Nested IF are good but in certain cases you might find yourself struggling with multiple IF statements within a single DAX due to the complexity or length of your requirements. This becomes very tiring and is very difficult to maintain in the longer run. BUT what if there was a better DAX function to solve this issue.
Let me give you a scenario, I have a table which has a list of services sold in a month with a column for Industry. Expected inputs in that Industry column are Banking, Retail, Chemical & Insurance. Now suppose you want to create a new calculated column by the name Ind_Short in Power Pivot with these conditions:
Banking = BKG
Retail = RTL
Chemical = CML
Insurance = INS
You might be tempted to use a nested IF function to get this done, something like below
=IF(Service_Industry[Industry]="Banking","BKG",IF(Service_Industry[Industry]="Retail","RTL",IF(Service_Industry[Industry]="Chemical","CML","INS")))But there is a better way to do this when we have to evaluate multiple conditions without ever writing any IF statements. We can use the SWITCH function in these scenarios.
In DAX, the SWITCH function is used to evaluate a list of expressions and return the result of the first expression that evaluates to TRUE.
It is also used in combination with other functions to create more complex calculations, but we will cover that in the future.
The syntax for SWITCH function is:
SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, …[, default_result])Where:
expression is the value or expression that will be evaluated
value1, value2, etc. are the values to be compared against the expression
result1, result2, etc. are the results to be returned if the corresponding value matches the expression
default is the optional result to be returned if none of the values match the expression
So in this specific scenarios we can use the below DAX code to work the magic with SWITCH function
=SWITCH(Service_Industry[Industry],"Banking","BKG","Retail","RTL","Chemical","CML","Insurance","INS","OTH")Where “OTH” is a default value. This default result can be useful for handling cases where the expression being evaluated has values that are not included in the list of specified values. It can also be used to provide a more meaningful result for values that do not match any of the specified values.
So as you see this formula is much more easy to construct and involves less number of parenthesis, which makes it easy to maintain in the long run.
The SWITCH function is a very useful tool that allows us to create conditional statements in our data models. It is especially useful when working with large datasets that have multiple conditions to evaluate.
Comments