Table Visual OR with nested AND condition Filtering

[kylifeofpy]

Hi PowerBI Community,

I’m running into a problem which I fear it cannot easily or possibly be solved through PowerBI’s general visual/page filters or data transformation method since I’m using Direct Query mode for database connectivity. I’m hoping to perform table visual filtering using a multi-level filtering starting with an OR and a nested AND condition for two columns in a single table, see below sample dataset.

IdentificationBadge Table Dataset

ID Badge	ID	Title	Dept
123	1	Consultant I	Finance
234	2	Consultant I	IT
345	3	Consultant II	IT
NULL	4	Consultant II	IT
NULL	5	Consultant III	IT
678	6	Consultant III	IT

Desired Results

ID Badge	ID	Title	Dept
234	2	Consultant I	IT
345	3	Consultant II	IT
NULL	5	Consultant III	IT
678	6	Consultant III	IT

For the desired result:

• Page-level filtering is applied to the ‘Dept’ column displaying ‘IT’
• Visual-level (table) filter:
  • ‘Title’ column displaying ‘Consultants III'
  • OR
  • ‘Title’ column where the individual is not a ‘Consultant III’ AND ‘ID Badge’ column is not NULL

Basically, I’m looking for all staff in IT that’s either a Consultant III or you have an existing ID badge regardless of title. The visual-level filter with an OR statement that has a nested AND condition is the problem where I’m unsure if it can be applied at the filter pane-level. Unfortunately, the filter pane’s advanced filtering allows for OR conditions but only to values within the same column, not another column, so that won’t work. Basically, there isn’t a font-facing solution to apply multi-level filtering to the table visual.

Also, the reason I’m unsure whether using the filter pane can resolve my issue is using a DAX measure with IF(OR(conditions), 1, 0) then put that measure on the filter pane and set the flag to 1. I’ve seen some clever ways others are using this for a problem like mines, but not exactly and I may have interpreted it the wrong way. If this is possible, can someone shed some light on how this can be done?

I know this can be solved through data transformation where you can create another table/virtual table or add a custom column with DAX conditions, but like I mentioned, my data model is through direct query and not import mode, so I’m limited in how I can ETL through PowerBI’s PowerQuery. Although, I’m again unsure if this limitation prevents me from using DAX to apply my conditions against the direct query tables to return a column of data in a virtual table, which then I can use that column to filter in the filter pane. That would be the ideal solution.

So the question is: Is what I’m trying to achieve feasible though PowerBI’s tools or because I’m using Direct Query, it’ll have to be done from the db-level? If it can be done through BI tools, can you please share a sample solution? Thank you in advance

[kylifeofpy · Accepted solution]

A helping hand from the Stack Overflow PowerBI Community was able to provide a concept of the DAX measure and after tinkering with it, I was able to resolve this problem. Sharing this with the community here:

Measure =

SWITCH(

    TRUE(),

    SELECTEDVALUE('IdentificationBadge'[Title]) = "Consultant III" ||

    (

        NOT(SELECTEDVALUE('IdentificationBadge'[Title]) = "Consultant III") &&

        NOT(ISBLANK(SELECTEDVALUE('IdentificationBadge'[ID Badge])))

    ),

    1,

    SELECTEDVALUE('IdentificationBadge'[Title]) = "Consultant III",

    2

)

Then add this measure to your visual and filter to 1.