Most users usually are not big fans of writing of complex logical functions, neither in excel formulas nor in DAX.

In both situations we can use the IF function when choosing from two options. In Excel formulas, nowadays, is the IFS function. It enables us to simply write condition - result - condition - result - condition - result etc., with no necessity to nest the IF functions.

Unfortunately, there is no IFS in DAX. However, we can simply use SWITCH to do something identical.

How to do it?

For example here we are looking for red Alfa Romeo and silver expensive Opel.

For multiple criterias in DAX you can use the AND or OR functions (that only handle up to 2 conditions), or operators like && or ||.

You can write formula like this and it works:

  • Description =
          Sheet1[Brand] = "Alfa Romeo"&& Sheet1[Color] = "Red",
          "Red Alfa",
             Sheet1[Brand] = "Opel"&& Sheet1[Color] = "Silver"&& Sheet1[Price] > 4000,
             "Expensive silver Opel",
             BLANK ()

but it becomes too complex and unclear if there are more IFs.

Instead of multiple IFS functions, we can use SWITCH:

  • Description =
       SWITCH (
          TRUE (),
          Sheet1[Brand] = "Alfa Romeo" && Sheet1[Color] = "Red", "Red Alfa",
          Sheet1[Brand] = "Opel" && Sheet1[Color] = "Silver" && Sheet1[Price] > 4000, "Expensive silver Opel"

With SWITCH we can simply keep the logic condition - result - condition - result etc.

Leave a Reply

Your email address will not be published.


clear formPost comment