cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Amri214
Regular Visitor

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 @Amri214 ,

 

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

@Amri214 , 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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@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
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.