Is it possible to use an OR with nested AND condition for filtering a table visual?

Hi Stack Overflow PowerBI/DAX 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

Solution

You could create a measure and then filter on that in your visual filter.
Example:

Consultant III or w/Badge = 
  SWITCH(TRUE(),
    SELECTEDVALUE('IdentificationBadge'[Title]) = "Consultant III", 1,
    NOT(ISBLANK(SELECTEDVALUE('IdentificationBadge'[ID Badge]))), 1
  )

In your visual filter, add this new measure and set it to is not blank

Based on your condition, wouldn't ID 3 & 4 (ConIII) appear in your desired results?

Here is a similar version but this one returns the count - you can use this one as well in your visual filter and if needed, for other visuals.

Consultant III or w/Badge count = 
  CALCULATE(
    COUNTROWS('IdentificationBadge'), (
      'IdentificationBadge'[Title] = "Consultant III"
    ) || (
      NOT( ISBLANK('IdentificationBadge'[ID Badge]) )
    )
  )

Kylifeofpy's modified DAX query from Sam as final solution:

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 apply the measure as a visual level filter and filter to 1