Improve our PBI GA4 Connector Using Filters With Logic Operators

HomeGA4Improve our PBI GA4 Connector Using Filters With Logic Operators
Img 20230322 Wa0003 1240x600

In the last article in this series we saw how to apply some basic metric & dimension filters.

If you have spent any serious amount of time working with the GA4 interface you will almost certainly have wanted to stack those fillers up. Cool as the filters in the last article were, they lack one important capability, filter logic.

This article is part of a series where we built a GA4 custom data connector for Power BI (PBI). If you haven’t seen that yet you might want read that first. It also follows on from the last post about setting up filters using the connector. You definitely should read that first then come back here.

Filters are cool as they let us cut down our data to the important bits, and even filtering on columns that we aren’t even returning. That’s important as we may want to get rid of data such as internal users but without having to bring that data down the wire and into Power BI taking up space.

Filter logic lets us combine our filters using familiar logic patterns AND, OR, NOT. Using these in combination with each other and utilising the operations from the last article, you should be able to solve most problems. To use the filters you will create an approved filter record and apply it in the same way as we did before. I’ll give you some basics to get you started and if you need more just ask in the comments, drop me a message in the contact form, or even better catch me on LinkedIn.

Tip. If you are writing your filters by hand start with the basics from the last post and get them working in isolation. Once they work individually then start to stack them together.

Examples

Lets start with an AND filter

AND Filter
let
    dimensionFilter = [
        andGroup = [
            expressions = {[
                    filter = [
                        fieldName = “eventName”,
                        stringFilter = [
                            matchType = “CONTAINS”,
                            value = “page_view”,
                            caseSensitive = false
                        ]
                    ]],
                    [filter = [
                        fieldName = “pageTitle”,
                        stringFilter = [
                            matchType = “CONTAINS”,
                            value = “GA4”,
                            caseSensitive = false
                        ]
                    ]],
                    [filter = [
                        fieldName = “hostName”,
                        stringFilter = [
                            matchType = “CONTAINS”,
                            value = “cooriedug.com”,
                            caseSensitive = false
                        ]
                    ]
                ]
            }
        ]
    ]
in
    dimensionFilter

Not so bad right!

Straight on to an OR filter

OR Filter
let
    dimensionFilter = [
        orGroup = [
            expressions = {[
                    filter = [
                        fieldName = “pageTitle”,
                        stringFilter = [
                            matchType = “CONTAINS”,
                            value = “GA4”,
                            caseSensitive = false
                        ]
                    ]],
                    [filter = [
                        fieldName = “pageTitle”,
                        stringFilter = [
                            matchType = “CONTAINS”,
                            value = “PBI”,
                            caseSensitive = false
                        ]
                    ]
                ]
            }
        ]
    ]
in
    dimensionFilter

The last type is the notExpression. I’ll leave you to work that out yourself. I’m sure you got it from here. You can nest all those guys above and do some pretty neat filtering.

I would recommend having a look at the API documentation to get a better feel. Remember JSON and Power Query M swap [ and { brackets up so that’s a fun thing.

Google Data API FilterExpression 

photo credit Matthew Robbins 

Comments ( 2 )

  • Pablo
    Pablo March 22, 2023

    Hi, I tried all of your filters, and only OrderBy and OrderByMetrics are working. All of the other ones return “The field ‘rowCount’ of the record wasn’t found”
    when I’m adding them as a filters to the Invoked Function.
    Could you help me with that error?
    I’ve already overwritten a quotation marks with the fresh ones.

    Reply
    • Gavin Brogan
      Gavin Brogan March 22, 2023

      I would need a closer a look. Ping me on Linkedin and we can go over it. They have all been tested and should work.

      Reply

Leave a Comment