Complex Filtering

[mjones24]

Hi all,

I’ve been using Power BI for a while now, on and off. My customer has a requirement regarding complex, compound filters, which I’d like to understand if Power BI supports and how. I’ve described my problem below, hope you can help and give me some advice, thanks in advance !

I have a well-defined, relational Star Schema as my data source, and I’ve bought this into Power BI Desktop. I’ve built a matrix-report featuring a few attributes on the rows and columns and a count of the FACT table rows as a measure. This works nicely.

I now want to add some filters to it. I’ve used the standard user interface to specify filter values on the row and column attributes, and I’ve dragged other attributes in the data model into the filters user interface panel and set those accordingly. All this works nicely.

My customer wants to apply more complex filtering. Specifically, he wants to set up a filter like this:

Filter = (attribute 1 is a AND b) OR (attribute 2 is c OR d) AND (attribute 3 is e AND f) OR (attribute 4 is g AND (h OR i)).

I don’t think this is possible using the standard user interface, since it appears that when you set a filter on attribute 1 and a filter on attribute 2, Power BI uses an AND condition between them e.g.

Filter = (attribute 1 is a AND b) AND (attribute 2 is c OR d)

instead of

Filter = (attribute 1 is a AND b) OR (attribute 2 is c OR d), which is what my customer wants.

I understand I can use DAX to add calculated columns to the model to implement this kind of filtering, but my customer sees that as a blocker. Is my understanding correct here ?

More generally, my customer wants to a user to be able to drag all attributes (in principle) from the data model into a “filter builder”, set filter conditions on the attributes individually, and combine them using AND and OR operations similar to my example above. I don’t think this is possible using the standard user interface; however, is this possible using DAX and has anybody built something like this ?

Thanks, mjones24.

[v-shex-msft · Accepted solution]

Hi @mjones24,

Default filter feature will use 'and logic to link all filters, so your customers goal is impossible to achieve through default filter.

I'd like to write a measure to check conditions and return the result tag, then you can filter the result tag to filter the records who suitable the complex filter conditions.

Sample:

Filter Tag =
VAR current_attr1 =
    LASTNONBLANK ( 'Table'[Attribute 1], 1 )
VAR current_attr2 =
    LASTNONBLANK ( 'Table'[Attribute 2], 1 )
VAR current_attr3 =
    LASTNONBLANK ( 'Table'[Attribute 3], 1 )
VAR current_attr4 =
    LASTNONBLANK ( 'Table'[Attribute 4], 1 )
VAR result =
    OR (
        AND ( current_attr1 IN { "a", "b" }, current_attr2 IN { "c", "d" } ),
        AND ( current_attr3 IN { "a", "b" }, current_attr4 IN { "c", "b" } )
    )
RETURN
    IF ( result, 1, 0 )

Notice:

1. You can modify the AND,OR functions to change the link logic.

2. If you want to add more conditions items, you can increase original items in variable list.(e.g.  current_attr1 IN { "a", "b" ,"c", "d"})

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you, that is very interesting, but can you explain please why you use the LASTNONBLANK function.  That seems to be something to make it work and not related to the requirements.

Thanks,
Ashley.

[mjones24]

Thank you, very helpful.