Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

OR Condition (OR logic) across multiple slicers

Hi,

 

I have a page with multiple calculated measures that I would like to filter using both regular slicers (working well) and a set of yes/no slicers that need the OR condition rather than AND condition.

 

Slicers typically work with AND conditions but for one particular set of slicers I need the condition to be OR

 

I've tried a bunch of things - the closest I got was this:

 
But it doesn't seem to work when I choose just ONE of the options - for example if I chose 1 occupation it would give me the wrong count in my measure.  The same happens if I choose ONE Brand, it gives me the same wrong count.  However when I select BOTH a brand and an occupation it gives me the right number.

I'm not sure what I"m doing wrong?

My slicer (Q3) is actually a dozen columns with yes/no/blanks (each column represents a brand).  I would love if I could just have 1 slicer with all the Q3 brands as the slicer items.  The user then would be able to select a combination of OR'd brands from the slicer to view all calculated results on the page.
 
Example: 
If the user selects Brand 1 and Brand 2 in the slicer then my measures would be calculated just on the rows where Q3_1=Yes OR Q3_2=Yes
If I did a measure that was a count of Q2 below, for example, and the user selected Q3_1=Yes and Q3_2=Yes, my count should be 5:
 
Is this possible?
 
Thanks so much!
Need Help

3 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi!  Slicers always work with "and" unless you override the filter context within your measures.

     

    Depending on the number of slicers you have, this could become quite ugly because you would have to check for which slicers are filtered and filter appropriately.  But there might be an easier way...try something like this:

    1. Set variables to check if a slicer is being filtered using the ISFILTERED function.  If so, apply that filter exactly like you are using VALUES(Table[attribute]).  If not, filter for all possible values of that attribute - ALL(Products[Brand]), ALL(Customer[Occupation]), etc.
    2. Change your measure to the following (after including the appropriate variables):

     

    CALCULATE(
	[Units],
	Brands,
	Occupations, 
	{Any other slicers one at a time separated by commas}
)​

     


    Hope this helps!  ‌‌🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi littlemojopuppy 

       

      Thank you for your help!

       

      I tried the following but it says "The True/False expression does not specify a column"

       

      VAR Brand1=ISFILTERED(DATA[Q3_1])
      VAR Brand2=ISFILTERED(DATA[Q3_2])
      RETURN
      CALCULATE(COUNT(DATA[Q1]),Brand1,Brand2)

      • littlemojopuppy's avatar
        littlemojopuppy
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Hi Anonymous .  Each variable should be IF ( ISFILTERED(), true condition, false condition)