Search code examples
powerbidaxpowerbi-desktopslicers

Using OR condition between >2 slicers, and slicing even if not all slicers are active

I want to be able to filter my visuals based on whether any of my multiple slicers are active.

I have been able to display data fitting any of the values in my slicers (rather than all of them) using this guide: https://apexinsights.net/blog/or-xor-slicing -- I just rewrote the OR filter to include multiple conditions:

OR Filter = 
IF (
    SELECTEDVALUE ( 'Table1'[Col1] )   IN ALLSELECTED ( xCol1[Col1] ) ||
    SELECTEDVALUE ( 'Table2'[Col2] )   IN ALLSELECTED ( xCol2[Col2] ) ||
    SELECTEDVALUE ( 'Table1'[Col3] )   IN ALLSELECTED ( xCol3[Col3] ),
    1,
    0
)

My problem is that I want to be able to have fewer than three slicers active and for the data to still be filtered. At the moment, if only two of the slicers are active, it still brings in everything. The data is only filtered if all three slicers are active.

Can anyone help?

Solution

  • You will need to add ISFILTERED to check if it is indeed filtered.
    Try:

    OR Filter = 
  var a = ISFILTERED(xCol1[Col1]) && SELECTEDVALUE('Table1'[Col1]) IN DISTINCT(xCol1[Col1])
  var b = ISFILTERED(xCol2[Col2]) && SELECTEDVALUE('Table2'[Col2]) IN DISTINCT(xCol2[Col2])
  var c = ISFILTERED(xCol3[Col3]) && SELECTEDVALUE('Table1'[Col3]) IN DISTINCT(xCol3[Col3])

  return
    IF( a || b || c, 1, 0)