DAX – Making the “Case” for SWITCH()

Guest Post by Colin Banfield [LinkedIn]

Way back during the period of the first CTP of Gemini (which later became PowerPivot), I was working with a data set that included a column of month numbers. I wanted to create a calculated column with month names, and the only solution that I could think of at the time was using the IF function in DAX:

=IF([MonthNum]=1, “January”,
IF([MonthNum]=2, “February”,
IF([MonthNum]=3, “March”,
IF([MonthNum]=4, “April”,
IF([MonthNum]=5, “May”,
IF([MonthNum]=6, “June”,
IF([MonthNum]=7, “July”,     
IF([MonthNum]=8, “August”,
IF([MonthNum]=9, “September”,
IF([MonthNum]=10, “October”,
IF([MonthNum]=11, “November”,
IF([MonthNum]=12, “December”,
)
)
)
)
)
)
)
)
)
)
)
)

This a horrendous formula, with eleven nested IF functions and a long tail of closing parentheses. I promptly made a suggestion in the Connect forum for the addition of a “Case” function in DAX, siting the preceding formula as justification for its inclusion. Sometime later, I received a message from Howie Dickerman indicating that some form of Case function was being considered for PowerPivot V2 (the current version). This “Case” function turned out to be the SWITCH() function. “Switch” is a function familiar to Access and VBA users, so it made sense to stick with that name for the DAX  implementation.

Anatomy of the SWITCH function

There are actually two usages of the SWITCH function. The first usage is equivalent to the SQL simple Case statement, and is the officially DAX documented usage. The second, and more powerful usage is equivalent to the SQL searched Case statement. The structure of the first usage is thus:

SWITCH(expression,
   value1, result1,
   value2, result2,
    :
    :
    else
   )

expression is any DAX expression that returns a single scalar value (number, string, or date), where the expression is to be evaluated multiple times (for each row/context).

value1 is a constant value to be matched with the evaluated result of expression.

result1 is any scalar expression (i.e. one that returns a scalar value) to be evaluated if the results of expression match the corresponding value1.

else is any scalar expression to be evaluated if the result of expression doesn’t match any of the value arguments

expression, value1, and result1 are the only mandatory parameters in SWITCH. If the formula contains multiple value/result pairs, then the data types of result1, result2…resultn must be the same. In addition, if you include an else expression, the expression must evaluate to the same data type as result.

Note: If the result of expression does not match any listed value, and the else condition is omitted, SWITCH returns an error. 

Using  SWITCH instead of IF in the formula provided at the top of this post, we get:

=SWITCH([MonthNum],
    1,”January”,
    2,”February”,
    3,”March”,
    4,”April”,
    5,”May”,
    6,”June”,
    7,”July”,
    8,”August”,
    9,”September”,
    10,”October”,
    11,”November”,
    12,”December”,
    “Invalid Month Number”
   )

The SWITCH formula is much cleaner than the IF formula – you don’t have to repeat [MonthNum] in every condition tested, and there is a single closing parenthesis. Incidentally, this particular example is used in the description of the SWITCH function in the official DAX documentation.

In reality though, when faced with a scenario where both value and result are constants, a table-based solution is most often the best approach. In Gemini, I ended up creating a table linked to a MonthNum/MonthName lookup table in Excel.

An example of using SWITCH where a table approach is not possible (because of the dynamic nature of the expressions and calculations involved) would be a formula like the following:

=IF(COUNTROWS(VALUES(DimPeriod[Period]))=1,
  IF(VALUES(DimPeriod[Period]) = “Current”, [Sales],
  IF(VALUES(DimPeriod[Period]) = “MTD”, [Sales](DATESMTD(DimDate[Datekey])),
  IF(VALUES(DimPeriod[Period]) = “QTD”, [Sales](DATESQTD(DimDate[Datekey])),
  IF(VALUES(DimPeriod[Period]) = “YTD”, [Sales](DATESYTD(DimDate[Datekey])),
  IF(VALUES(DimPeriod[Period]) = “LastYear”, [Sales](DATEADD(DimDate[Datekey],-1,YEAR)),
  IF(VALUES(DimPeriod[Period]) = “PriorYearMTD”, [Sales](DATEADD(DATESMTD(DimDate[Datekey]),-1,YEAR)),
  IF(VALUES(DimPeriod[Period]) = “PriorYearQTD”, [Sales](DATEADD(DATESQTD(DimDate[Datekey]),-1,YEAR)),
  IF(VALUES(DimPeriod[Period]) = “PriorYearYTD”, [Sales](DATEADD(DATESYTD(DimDate[Datekey]),-1,YEAR)),BLANK()
  )
  )
  )
  )
  )
  )
  )
  ),
  [Sales]
  )

The above is a formula you can find in the excellent whitepaper titled DAX in the BI Tabular Model, written by Howie Dickerman et al. The formula is based on version 1 of the whitepaper (before SWITCH existed), and wasn’t updated to reflect new alternative functions in PowerPivot V2. This is an awkward and error-prone formula to write, because you have to be careful about including the correct number of closing parentheses before the [Sales] measure (the false condition of IF(COUNTROWS…). 

Now consider how this formula would be rewritten using SWITCH:

=IF(HASONEVALUE(DimPeriod[Period]),
   SWITCH(VALUES(DimPeriod[Period]),
     “Current”, [Sales],
     “MTD”, [Sales](DATESMTD(DimDate[Datekey])),
     “QTD”, [Sales](DATESQTD(DimDate[Datekey])),
     “YTD”, [Sales](DATESYTD(DimDate[Datekey])),
     “LastYear”, [Sales](DATEADD(DimDate[Datekey],-1,YEAR)),
     “PriorYearMTD”, [Sales](DATEADD(DATESMTD(DimDate[Datekey]),-1,YEAR)),
     “PriorYearQTD”, [Sales](DATEADD(DATESQTD(DimDate[Datekey]),-1,YEAR)),
     “PriorYearYTD”, [Sales](DATEADD(DATESYTD(DimDate[Datekey]),-1,YEAR)),
     BLANK()
    ),
   [Sales]
  )

The SWITCH version of the formula is easier to write and less error prone. SWITCH also replaces eight IF function calls with a single function call. Note that I’ve substituted COUNTROWS(VALUES(DimPeriod[Period]))=1 with HASONEVALUE(DimPeriod[Period]). HASONEVALUE is new in PowerPivot V2, and eliminates the extra function call in the IF(COUNTROWS(VALUES…) test.

The second usage of the SWITCH function has the following structure:

SWITCH(TRUE(),
    booleanexpression1, result1,
    booleanexpression2, result2,
    :
    :
    else
   )

where TRUE() is a DAX function, and booleanexpression1,booleanexpression2,…are any valid Boolean expressions (i.e. returns True or False). The first booleanexpression that evaluates to True will return the corresponding result as the formula result.

Consider the next example, which uses the IF function:

=IF(ISFILTERED(DimAccount[Level6]),6,
  IF(ISFILTERED(DimAccount[Level5]),5,
  IF(ISFILTERED(DimAccount[Level4]),4,
  IF(ISFILTERED(DimAccount[Level3]),3,
  IF(ISFILTERED(DimAccount[Level2]),2,
  IF(ISFILTERED(DimAccount[Level1]),1
  )
  )
  )
  )
  )
  )

In the above formula, we are evaluating six different Boolean expressions, using ISFILTERED. The formula was inspired by a couple of breathtaking articles written by an Italian BI guru with the same name as a famous Italian carmaker (hint…its not Lamborghini or Alfa Romeo).

If at this point you’ve given up wondering who I’m referring to, please see Parent/Child Hierarchies in Tabular with Denali and Clever Hierarchy Handling in DAX.

We can rewrite the formula using SWITCH as follows:

=SWITCH(TRUE(),
  ISFILTERED(DimAccount[Level6]),6,
  ISFILTERED(DimAccount[Level5]),5,
  ISFILTERED(DimAccount[Level4]),4,
  ISFILTERED(DimAccount[Level3]),3,
  ISFILTERED(DimAccount[Level2]),2,
  ISFILTERED(DimAccount[Level1]),1
)

In the above formula, we’ve eliminated five closing parentheses and replaced six IF functions with a single SWITCH function.

We can use SWITCH in cases where we need to evaluate inequalities. For example, consider a community foundation that accepts monetary gifts from donors and invests the proceeds in a series of endowment funds. For a given month, the foundation wants a report that provides the quantity and fund balances for funds of given sizes. In the database table that provides the fund name (and/or ID) and balances, we can create a calculated column named Fund Size, e.g.:

Fund Size:=SWITCH(TRUE(),
             AND([Fund Balance]>=0, [Fund Balance]<=10000), “Up to $10,000”,
             AND([Fund Balance]>=10001, [Fund Balance]<=50000), “$10,001 to 50,000”,
             AND([Fund Balance]>=50001, [Fund Balance]<=100000), “$50,001 to 100,000”,
             AND([Fund Balance]>=100001, [Fund Balance]<=500000), “$100,001 to 500,000”,
             AND([Fund Balance]>=1500001, [Fund Balance]<=1000000), “$500,001 to 1,000,000”,
             “greater than $1,000,000”
           )

We can then put Fund Size in the Row area of a PivotTable, and use Fund Balance in measures that calculate the total quantity and total balances of the various fund sizes. Come to think of it, the preceding formula is nothing more than another example of banding. In this instance, I would opt for the table solution, but there could be instances where the complexity of the Boolean or result expressions might resist a table approach.

Summary

Advantages of SWITCH() over IF():

• Formulas are easier to write, and subsequently read
• Formulas are less error-prone, and thus easier to debug
• A large number of IF calls may, or may not have a negative performance impact in large datasets (I haven’t done any testing to prove or disprove this statement)

Advantages of IF() over SWITCH():

• None