Forum Discussion
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
- lbendlin
Super User
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.