Forum Discussion
Is Advanced Filtering with logical operators across multiple columns possible in PBI Desktop?
I want to setup a filter that, for example, can filter rows where the date is greater than x and where the category is y or the location is z. In SQL it would be like:
SELECT *
FROM table as t
WHERE t.date > 'y' AND (t.category = 'y' OR t.location = 'z')
I know that a table can be created in DAX, but my requirement is for the end user to perform the filtering in Power BI desktop using the available Report Filter pane. However the filtering pane only allows multiple logical conditions on a single column so that all together they end up being joined behind the scene with "AND"
Is the functionality available in PBI Desktop and I am just not understanding how to implement it?
8 Replies
Anonymous Yes, that functionality is available. You can either add the same column to the visual/filters multiple times to allow for composite filters, or (much better) enable the "Personalize Visual" feature and teach your users how to use it.
Hi Anonymous
I think you can try to build a measure to filter your visual or just build a measure to calculate the result.
For example:
Here I want to get value where Date>2019/10/01 AND (Category = "A" OR Location = "L2").
Way1 is to create a measure to filter your visual.
Measure = IF(AND(MAX('Table'[Date])>DATE(2019,10,01),OR(MAX('Table'[Category]) = "A",MAX('Table'[Location])="L2")),1,0)Add this measure into visual level filter pane and set it to show items when value =1.
Way2 is to build a measure to calculate the result directly.
M_Value= CALCULATE(SUM('Table'[Value]),FILTER('Table',AND('Table'[Date]>DATE(2019,10,01),OR('Table'[Category]="A",'Table'[Location]="L2"))))Result:
Your end user can change parameters like date,category or location in measure to get result they want.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Measures live in a filter context. They need to calculate something. "MAX" is picked because it is convenient (especially for the Totals) but you can use any other aggregation too.
