Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Using OR logic for multiple slicers (more than 2)

Hi everyone. I have been stuck with a problem for the past couple of days that I cannot seem to solve. To provide dummy data:

There are 4 columns in the sheet 'Vehicles' namely, Gasoline, Diesel, Electric and Kerosene. They are all Yes or No values to indicate the fuel type. For the sake of discussion, let us assume that a vehicle can have more than 1 value, i.e., a single row of data can say 'YES' for Gasoline and 'Yes' for Electric as well. (not going too much into the automotive technicalities) 
So I have 4 slicers:


Slicer 1: Gasoline [ Yes, No]
Slicer 2: Diesel [Yes, No]

Slicer 3: Electric [Yes, No]

Slicer 4 Kerosene [Yes, No]

By default, if I choose Gasoline YES and Kerosene YES, I will see the value of rows that have BOTH Gasoline and Kerosene. Whereas, I need the number of rows that have EITHER Gasoline OR Kerosene. I have seen multiple videos to get the OR functionality for 2 slicers using the measure :

OR #1 =
CALCULATE (
counta(ID),
UNION (
CROSSJOIN ( VALUES ( Table1[column1] ), ALL (Table1[column2])),
CROSSJOIN ( ALL ( Table1[Column1] ), VALUES(Table1[Column2]))))

But I don't understand how to use it for 4 slicers. So I need the OR value:  EITHER Gasoline OR Diesel OR Electric.

The actual data set contains 5 slicers, so having 4 ORS can also be a possibility but I hope the above-mentioned fictitious example serves the purpose.
Also, I need to filter a map as well using these slicers. Using the above-mentioned logic, I was able to obtain the OR value of 2 columns but not use that in the MAP. 

Any help or suggestion will be really helpful. The problem needs to be resolved as soon as possible

Thank you in advance
3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

Hi @Anonymous ,

 

According to your description, you can create extra tables which are unrelated to source data table. Then meet your needs by creating a formula. 

 

The following provides a solution similar to the independent slicer, whether it can provide you with ideas:                   2 independent slicers, one dataset


If the problem is still not resolved, can you provide test data (delete sensitive information) and screenshots of the expected results, I will answer you as soon as possible.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , for or you will need an independent slicer, with values yes and no. means four tables with yes and no

 

 

Example measure

Measure =
var _1 = selectedvalues(slicer1[slicer])
var _2 = selectedvalues(slicer2[slicer])
var _3 = selectedvalues(slicer3[slicer])
var _4 = selectedvalues(slicer4[slicer])
return
CALCULATE (
counta(ID),filter(Table, [Gasoline] =_1 || [Kerosene] = _2 || [Electric] = _3 || [Diesel] =_4))

 

 

Try

Power BI Abstract Thesis: Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

 

Anonymous
Not applicable

@amitchandak Thank you for your response.

 

I used the measure you provided and it works as AND logic. The reason being that I did not understand the concept of 'Independent Slicers' and 'Independent Tables'. In the video on your channel, it was clear how the 'Include' and 'Exclude' functions were used to get all values except the one chosen in the slicer or vice versa. I was not able to connect the dots and use the same logic for my dataset to obtain an OR functionality.

Since all my data is stored in 1 table, how can I get the independent slicers?

Thank you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.