Forum Discussion
DAX SWITCH STATEMENT not working properly
Hello, I am just trying to make a mesure by using SWITCH statement. but it is not working properly. I don't know why.Here is my code. I feel like there is something wrong with SWITCH statement because when I tried the code below in which I excluded SWITCH statement
it works. I got a right number which is -10.64%. With SWITCH, I got -100% which is not correct. I will also attach the table below that I created myself.
There is another table called "FI OperationsStatementData", but it being directquery from SQL server, so I can't attach here. Can anyone answer my question? I will appreciate any helps.
Hi Anonymous ,
Based on my test and reasearch, I have added a filter in your [Construction Income (Budget)] measure and now it can show the correct result:
Construction Income (Budget) = CALCULATE(SUM('Budget For Meters'[Budget]), FILTER(ALL('Budget For Meters'),'Budget For Meters'[Description] = "1.Construction Income"))Attached the modifed dummy file, please check it: Dummy.pbix
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
use daxformatter.com to put your formula into a readable format, and maybe find out where your logic breaks down.
here's how SWITCH() is supposed to work for your scenario (there are other options too)
SWITCH( TRUE(),<first test>,"first result",<second test>,"second result",...,"alternative result")
Hi Anonymous ,
Since not certain what did your whole dataset look like, one reason that could cause this issue is that the data type of these two measures are not the same, please check whether the data type of the switch statement and the previous measure are different.
Because your calculation logic does not change, the result should not be different due to a switch statement.
Or you can consider sharing a dummy .pbix file for further discussion to determine the specific reason if the data type is same.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hello, I think data type is same, so I attach here my dummy pbix file. Basically, I am trying to make a measure that calculates (Actual % / Budget %) - 1. "Payroll & Benefits" and "Indirect Expense" got right percentage, but "Fuels" not getting a right percentage which is -10/64%. I also attach a screenshot to make you understand better what I am saying.
https://drive.google.com/file/d/1hSHiM-QZ9g4arOpc7wV0a3Iu7nGCeWT5/view?usp=sharing
p.s. Construction income is negative number because it is condisered as credit, all others debit.
Hi Anonymous ,
Based on my test and reasearch, I have added a filter in your [Construction Income (Budget)] measure and now it can show the correct result:
Construction Income (Budget) = CALCULATE(SUM('Budget For Meters'[Budget]), FILTER(ALL('Budget For Meters'),'Budget For Meters'[Description] = "1.Construction Income"))Attached the modifed dummy file, please check it: Dummy.pbix
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
