Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 WH...
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
lbendlin
Super User
1 year ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      lbendlin
      Super User
      1 year ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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!