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
