Is Advanced Filtering with logical operators across multiple columns possible in PBI Desktop?

[Anonymous]

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?

[Anonymous]

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.

[Fernanh98]

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?

[lbendlin]

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.

[Fernanh98]

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.

[lbendlin]

use CALENDAR or DATESBETWEEN to specify date range filters,  or - if you have it - put a filter on the Year column in your calendar table.

[Fernanh98]

The problem in my case is that I want to show a visualization of a table and I want the user to be able to change its filter dynamically, but the filter should works as follows:

(columnA > [slicerValue] && columnB > [slicerValue]) || (columnA = [slicerValue] && columnC = [slicerValue])

I want to be able to change slicerValue dynamically. Do you knwo how can I achive this?
Thanks in advance!

[lbendlin]

Use a disconnected table (or a parameter) for the slicer.  Then use a measure that implements your logic. Use that measure as a filter on the visual.

[lbendlin]

@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.