cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CT_ar
Frequent Visitor

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:

Capture1.JPG
 
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:
Capture.JPG
 
Is this possible?
 
Thanks so much!
3 REPLIES 3
littlemojopuppy
Super User
Super User

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!  ‌‌🙂

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)

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.