DAX – Making the “Case” for SWITCH()

June 5, 2012

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.

Read the rest of this entry »