How to handle the SQL CASE logic in DAX


IF-THEN-ELSE-END_flowchart.svg

 

IF..THEN..ELSE conditional programming logic is most common in all programming languages and it is one of the powerful weapon to handle the complex calculations. Those familiar with SQL, CASE Statements are the one of the way to achieve the end results. In Power BI, DAX has its own syntax and it is using SWITCH/Nested IF function to mimic that logic. In this post, I will take you through the SWITCH patterns in DAX and comparing it with CASE Statements in SQL.


Simple CASE expression in SQL & SWITCH Values Pattern in DAX:

The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

 

 

Example:

USE AdventureWorks2012; 

GO 

SELECT   ProductNumber, Category = 

      CASE ProductLine 

          WHEN 'R' THEN 'Road' 

          WHEN 'M' THEN 'Mountain' 

          WHEN 'T' THEN 'Touring' 

          WHEN 'S' THEN 'Other sale items' 

          ELSE 'Not for sale' 

      END, 

   Name 

FROM Production.Product 

ORDER BY ProductNumber; 

GO

 

In DAX, SWITCH() is the function of the choice to get this desired output.

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

 

Let’s dismantle the syntax of SWITCH() in DAX:

 

expression – It should be 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

 

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

 

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

 

else is any scalar expression to be evaluated if the result of expression doesn’t match any of the value arguments (You do not need to mention the else keyword)

 

Return Value is a scalar value coming from one of the result expressions, if there was a match with value, or from the else expression, if there was no match with any value.

 

The only required parameters in SWITCH are expression, value1, and result1 while the other arguments are optional.

switch

Points to Consider

  1. If the formula contains multiple values (value1, value2..) or results (result1,result2..), then the data types of result1, result2…resultn must be the same.
  2. If you have an else condition, the expression must evaluate to the same data type as result.

Advantages over Nested IF Calls

Using  SWITCH() instead of IF in the formula, we get:

  1. You don’t have to repeat expression in every condition tested, and there is a single closing parenthesis.
  2. The SWITCH version of the formula is easier to write and less error prone than using nested IF version.
  3. SWITCH also replaces multiple IF function calls with a single function call.
  4. The SWITCH formula is much cleaner & maintainable than using the nested IF calls.

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.

Consider the following eight calculations. Each of these formulas calculates a TotalSales amount (in the Contoso database) for a distinct time period.

Time Period
Formula
CurrentYear
=[TotalSales]   or   SUM(FactSales[SalesAmount])
MonthToDate
=CALCULATE([TotalSales],DATESMTD(DimDate[Datekey]))
QuarterToDate
=CALCULATE([TotalSales],DATESQTD(DimDate[Datekey]))
YearToDate
=CALCULATE([TotalSales],DATESYTD(DimDate[Datekey]))
LastYear
=CALCULATE([TotalSales],DATEADD(DimDate[Datekey],-1,YEAR))
PreviousYearMTD
=CALCULATE([TotalSales],DATEADD(DATESMTD(DimDate[Datekey]),-1,YEAR))
PreviousYearQTD
=CALCULATE([TotalSales],DATEADD(DATESQTD(DimDate[Datekey]),-1,YEAR))
PreviousYearYTD
=CALCULATE([TotalSales],DATEADD(DATESYTD(DimDate[Datekey]),-1,YEAR))

 

Let’s create a new table named DimPeriod in Power BI that has a column named Period & other column Sort By to sort the first column, by using the Enter Data option on the Home Ribbon:

enter data

Dimperiod

 

Now let’s build a single measure formula that does the following:

Use an IF function, to see if the DimPeriod[Period] column has been placed into the Filter Context by placing this column onto the column labels, or row labels of the Matrix Visual in Power BI Desktop. The best way is to count the number of values that are applicable from this column. If this isn’t in the filter context, all 8 values will be applicable. But if this is on columns or rows, then only one of the values will be applicable for each cell in the values area (except for the Total row/column).

 IF (HASONEVALUE(DimPeriod[Period]),)

If only one of these values is applicable, then use a SWITCH statement nested in an IF statement to see which one it is, and apply the appropriate formula.

SWITCH(VALUES(DimPeriod[Period]

If this column is not on rows or columns (or selected to a single value in a slicer) then assume the user wants Current sales figure instead of one of the more elaborate calculations.


Such a formula would look like this:

=IF(HASONEVALUE(DimPeriod[Period]),

             SWITCH(VALUES(DimPeriod[Period],

   "Current", [TotalSales],

 "MonthToDate", CALCULATE([TotalSales],DATESMTD(DimDate[Datekey])),

 "QuarterToDate", CALCULATE([TotalSales],DATESQTD(DimDate[Datekey])),

 "YearToDate", CALCULATE([TotalSales],DATESYTD(DimDate[Datekey])),

 "LastYear",CALCULATE([TotalSales],DATEADD(DimDate[Datekey],-1,YEAR)),

"PreviousYearMTD",CALCULATE([TotalSales],DATEADD(DATESMTD(DimDate[Datekey]),-1,YEAR)),

"PreviousYearQTD",CALCULATE([TotalSales],DATEADD(DATESQTD(DimDate[Datekey]),-1,YEAR)),

"PreviousYearYTD",CALCULATE([TotalSales],DATEADD(DATESYTD(DimDate[Datekey]),-1,YEAR)),

BLANK()),[TotalSales])

 

Now if we build a Matrix Visual with DimDate[CalenderYear] on row labels, DimPeriod[Period] on column labels, and my new formula as the only measure, we will get a output that looks like this:

switch pattern1.png

switch values.png

 

Searched CASE expression in SQL & SWITCH Pattern in DAX:

 

Within a SELECT statement, the searched CASE expression allows for values to be replaced in the result set based on comparison values. The following example displays the list price as a text comment based on the price range for a product.

 

USE AdventureWorks2012;

GO

SELECT   ProductNumber, Name, "Price Range" =

CASE

WHEN ListPrice =  0 THEN 'Mfg item - not for resale'

WHEN ListPrice < 50 THEN 'Under $50'

WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'

WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'

ELSE 'Over $1000'

END

FROM Production.Product

ORDER BY ProductNumber ;

GO


In Power BI, This can be achieved using the SWITCH TRUE Pattern. There are many use cases for this pattern (to evaluate inequalities) and one of the common use is classifying the values in buckets/groups. Power BI has inbuilt capability introduced to group values using grouping feature in recent releases. However, SWITCH TRUE pattern is easy to adopt, valuable & can simplify syntax managing and complexities of using multiple Nested IF calls.

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.

For example, Consider a Contoso data model with the DimProduct as a Dimension and FactSales as a FactTable. Products in the DimProduct has different unit costs and we would like to categorize/groups them in 3 different categories. For a given month, the Manager wants a report that has Products grouped into High, Medium and Low Unit Costs with their respective Sales. In the DimProduct table that provides the Product name (and/or ID) and Unit Costs for each of them, we can create a calculated column named   Unit Cost Categories, e.g.:

UnitCostCategories:=SWITCH(TRUE(),
 AND([UnitCost]>=0, [UnitCost]<=100), "Low",
 AND([UnitCost]>=100, [UnitCost]<=200), "Medium",
"High"
 )

 

unitcost.png

We can then put UnitCostCategories in the Row area of a Matrix Visual, and use TotalSales in measures.

unitcost1.png

Similar to this pattern, we can use the CALCULATE VALUES FILTER combination to achieve the similar banding results with creating a new table in the data model that is not related to any other tables but we can use DAX to get the exact same output as SWITCH TRUE Pattern:

Let’s create a CostBands Table that contains the cost ranges. For each range, you should define the boundaries of the range itself as shown below:

costbands

In out contoso data model, there is no way you can create a direct relationship between the FactSales and CostBands Table. This is because the key in the CostBands table depends on a range relationship and range relationships are not supported by DAX. To overcome this limitation, we would need to denormalize the cost range directly into the FactSales using the calculated column.

With the combination of CALCULATE, VALUES & FILTER Pattern, Create a Calculated column in FactSales Table in the Contoso Data model , we will get the expected results as shown in the below screenshot:

UNITCOST2.png

It is interesting to note the use of VALUES function to retrieve the single value. VALUES is an interesting function. It is primarily used as a table function but returns a single scalar value whenever a table contains a single row and a single column. It means the function converts itself into the scalar value if the expression demands for it. So smart…

In the above calculation, FILTER function will always return a single row from the CostBands Table. Thus, VALUES is guaranteed to always return a single row, and the result of CALCULATE is the description of CostBands containing the UnitCosts.

Hope this article added a new magical weapon in your DAX expertise.

Next time, we will discuss more about the various use cases of SWITCH() in DAX. Till then, Enjoy!

 

 

Previous Golden Rules of working with Time Intelligence functions in Power BI
Next Handle different granularities without creating relationships in Power BI (Logical Relationships Approach)

1 Comment

  1. […] can also use DATEADD function. (Check it out here). This function will allow you to specify custom last parameter(QUARTER, […]

Leave a Reply