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 !! YouTube Channel !! Connect on Linkedin

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!