cancel
Showing results for
Did you mean:
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

3 REPLIES 3
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.

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

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!
Regular Visitor

@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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!