Forum Discussion

resullivan's avatar
resullivan
New Member
3 years ago

Adding an Or Slicer

I am new to PowerBI/DAX. That being said I have tried this multiple ways. To start, I have a list of cars that have a type and a trim. I am trying to filter data basd on type and trim or a different type. I have created two calculated tables model, and model2 with the following:

 

 

model = DISTINCT('test_data (2)'[Model_Type])

 

Same for trim:

 

 

trim = DISTINCT('test_data (2)'[Car_Type_Trim_Level])

 

 

This has given me a disconnected filter so to speak to potentially let me write some code vs using the native filter feature. I can also select more than one model this way. To show the entire thing I created a visual which shows from 0% to 100% that % of cars that are electric:

 

 

To do this I created a measure using the following:

 

 

Percentage = 
VAR _sumofpervalue = 
CALCULATE(
    COUNTA('test_data (2)'[Fuel_Type]), 
    ALLSELECTED('test_data (2)'[Fuel_Type]), 
    (('test_data (2)'[Fuel_Type]=SELECTEDVALUE('test_data (2)'[Fuel_Type])) && ('test_data (2)'[Model_Type] IN (ALLSELECTED('model'))) && ('test_data (2)'[Car_Type_Trim_Level] IN (ALLSELECTED('trim'[Car_Type_Trim_Level]))))
)
VAR _sumofallvalues = CALCULATE(COUNTA('test_data (2)'[Fuel_Type]), ALLSELECTED('test_data (2)'[Fuel_Type]), KEEPFILTERS('test_data (2)'[Model_Type] IN (ALLSELECTED('model'[Model_Type]))), KEEPFILTERS('test_data (2)'[Car_Type_Trim_Level] IN (ALLSELECTED('trim'[Car_Type_Trim_Level]))))
RETURN
    DIVIDE(_sumofpervalue, _sumofallvalues)

 

This code works for filtering based on the tables created above based on slicers for those tables. It does not include the OR for a second model. I have attempted to go about this a couple of different ways. The most promising was using a union for model1 and model2 allselected. The issue is that if nothing is selected for model2 then everything is. I went down a rabbit hole of making a slicer that defaults to non selected using a parameter, but kind of gave up on that. I tried using IF isfiltered, but eventually figured out IF cannot return a table. I have gone down a rabbit hole of attempting to utilize a measure based on the slicers, and probably just do not know enough about how measures aggregate to figure it out. I have started using DAX just for a silly competition, so I have two days of experience. Please go easy on my code lol. Any assistance to solve would be much appreciated.

1 Reply

  • 1. Don't use slicers. Use the filter pane. The Advanced filtering gives you more flexibility.

    2. For more exotic OR conditions create a measure that you can then use as a filter on your visual.