Forum Discussion
Multiple nested IF statements
Hi tomekm ,
Please try the following DAX for your calculated column and replace 'Table' with the name of your table:Status = IF ( 'Table'[Total] > 0, SWITCH ( TRUE(), 'Table'[A] > 0 && 'Table'[B] > 0 && 'Table'[C] > 1 && 'Table'[D] > 1, IF('Table'[Customer Type] = "OK", "Primary", "Secondary"), 'Table'[A] > 0 && 'Table'[C] > 1 && 'Table'[D] > 0, IF('Table'[Customer Type] = "Upcoming", "Tertiary", "Advanced"), 'Table'[Customer Type] = "Other", "Basic", "Next" ), "No Access" )
The SWITCH() function in DAX acts the same as nested IF() statements. However, it is more performant and easier to manage.If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,Samson
In DAX, a Switch statement is much better than an IF statements for situations like this where the syntax becomes difficult to disentangle.
It would be something like this:
Status =
SWITCH(
TRUE(),
<Logic A>, <Return value if Logic A is true>,
<Logic B>, <Return value if Logic B is true>,
<Default return value if Logic A and Logic B are both false>
)
Remember, while writing, to keep in mind that it will stop evaluating and return the return value on the first line whose logic evaluates to true. If Logic A and Logic B are both true for a certain row, they will both return the return value for Logic A.
So the precise order matters sometimes for getting the correct return values, but also you can thoughtfully put them in a specific order such that the whole thing is more efficient by ordering the situations in the anticipated frequency that each condition will be fulfilled. (If you think Logic B is the one that will be evaluated as true in 90% of cases, put it first, so that the code for Logic A does not have to evaluate every single time, unnecessarily)
///Mediocre Power BI Advice, but it's free///