Forum Discussion
Is Advanced Filtering with logical operators across multiple columns possible in PBI Desktop?
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 Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
When creating the measure as you´ve mentioned:
Measure = IF(AND(MAX('Table'[Date])>DATE(2019,10,01),OR(MAX('Table'[Category]) = "A",MAX('Table'[Location])="L2")),1,0)
Why do you calculate the max of "Date" column?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.
Ok thanks! But if I want to get those rows in which Date is equal to 2019 instead of greater than?
If I use MAX(), since there are dates of 2020 in the column, it won´t return those rows from 2019, though my condition is that the year has to be 2019.
