Forum Discussion
SWITCH statement not working as intended
i have a switch statment using DAX:
DisplaySEAData =
SWITCH(
TRUE(),
[SEA Selected],CALCULATE(SUM('EVR 2014-2024'[EVR]),'EVR 2014-2024'[office_name] = "SEA"),
"Test"
)
SEA Selected =
VAR SelectedCountries = VALUES('EVR 2014-2024'[office_name])
VAR CountSelectedCountries = COUNTROWS(SelectedCountries)
RETURN
IF (
CountSelectedCountries = 4 &&
COUNTROWS(FILTER(SelectedCountries, 'EVR 2014-2024'[office_name] IN {"Vietnam", "Philippines", "Indonesia","Thailand"})) = 4,
TRUE(),
FALSE()
)In this scenario nothing is selected, so "test" is shown which is correct
However when the SEA countries are selected in the slicer, it returns blank but [SEA Selected] is already returning True as seen on the card on the left
I have also checked that CALCULATE(SUM('EVR 2014-2024'[EVR]),'EVR 2014-2024'[office_name] = "SEA") returns a value and not a blank
Can someone help me figure out what is wrong with my logic as to why its returning blank when it clearly is not?
Appreciate the help in advance
i found the issue, as i am selecting the slicer I.e. Vietnam,Indonesia, Thailand. i want to display the sum from SEA region instead but as it is filtered for Vietnam,Indonesia, Thailand it shows as blank() instead. i am now going to try and see if can do it if i change my data source to another table
4 Replies
Hi rgoo, I'd suggest to debug in the following way:
- "DisplaySEAData" measure: replace "CALCULATE(SUM('EVR 2014-2024'[EVR]),'EVR 2014-2024'[office_name] = "SEA")" with a static value, for instance 1 (or text "1" in your case as your else parameter "test" is a string value). In this way you can verify whether switch works as expected.
- If switch works and you get value 1, then the problem is with Calcualte(). Make sure that you apply exactly same filters when testing it alone as when "DisplaySEAData".
- If not, so with "1" switch still provides you Blank(), then rewrite your formula with variable (I'm not sure if switch is evaluating a measure when used as a "value" argument
DisplaySEAData = VAR _SEA_Selected = [SEA Selected] RETURN SWITCH( TRUE(), _SEA_Selected,CALCULATE(SUM('EVR 2014-2024'[EVR]),'EVR 2014-2024'[office_name] = "SEA"), "Test" )
I hope something from it will help you! Good luck 🙂i found the issue, as i am selecting the slicer I.e. Vietnam,Indonesia, Thailand. i want to display the sum from SEA region instead but as it is filtered for Vietnam,Indonesia, Thailand it shows as blank() instead. i am now going to try and see if can do it if i change my data source to another table
Great! Remember to acceprt the relevant asnwer as a solution when you finish, so others can find an answer to similar problem 🙂
rgoo did you check the interaction between them?
