Forum Discussion
CASE Statement replacement for DAX
I don't know when/if the CASE syntax will ever be a part of DAX but I was just wondering if there is a better approach than nested, upon nested, upon nested inline IF syntax? Sure, for very simple things like replacing numeric codes with lables, SWITCH works nicely, but when there are calculations involved, the IF's get very hard to read. Is there some other syntax I'm not aware of? I've seen some examples of making a new column in PQ using an If Then Else construct. Is that the better way to go? If so, what would be the PQ syntax to replace SQL Server's
CASE WHEN x THEN y ELSE z END ?
Hi domtrump ,
I think Switch is what a lot of people use. If the calculations are cluttering up the formula, you might consider using variables to separate those from the switch statement- something like below:
Measure =
VAR ComplexCondition1 = longcomplexformula1
VAR ComplexResult2 = longcomplexformula2
RETURN
SWITCH ( TRUE (), [ComplexCondition1], [ComplexResult1] )etc...
2 Replies
Hi domtrump ,
I think Switch is what a lot of people use. If the calculations are cluttering up the formula, you might consider using variables to separate those from the switch statement- something like below:
Measure =
VAR ComplexCondition1 = longcomplexformula1
VAR ComplexResult2 = longcomplexformula2
RETURN
SWITCH ( TRUE (), [ComplexCondition1], [ComplexResult1] )etc...
I did NOT know you could use FORMULAS in SWITCH! Awesome. Thanks so much. -Dom
