DAX SWITCH STATEMENT not working properly

[Anonymous]

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.

[v-yingjl]

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 Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

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. 

[v-yingjl · Accepted solution]

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 Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yingjl, I tried your code and it works! Really appreciate your help.

[lbendlin]

use daxformatter.com to put your formula into a readable format, and maybe find out where your logic breaks down.

[Anonymous]

Here you go.

[lbendlin]

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")

[Anonymous]

Actually, I gave you wrong code. This is right one.

[lbendlin]

You are using CALCULATE() without any filter context modifiers.  That means the calculation will only work on the current context of your visual which is very likely not what you want to do (and which doesn't really need Calculate).  What are you actually trying to calculate?

[Anonymous]

Sorry for late response. What do you mean by I am using CALCULATE () without any filter context modifiers? (Sorry again I am a beginner of Power BI.) Basically, I created this measure compare budget and actual like how much actual is different from budget and representing as percentage. The thing is, without SWITCH statement, my code is working. I got a number I want. However, when I add SWITCH statement, it is not working. I am not sure why this is happening.