You're reading from DAX Cookbook Over 120 recipes to enhance your business with analytics, reporting, and business intelligence

Product type Paperback

Published in Mar 2020

Publisher Packt

ISBN-13 9781839217074

Length 552 pages

Edition 1st Edition

Languages

DAX

Tools

Excel

Concepts

Business Intelligence

Author (1):

Greg Deckler

View More author details

Using conditional logic

Conditional logic is an important concept for programming in general, and DAX is no different. There are many circumstances where we want to present different values for a measure or column based upon a series of conditions or circumstances; for example, if a particular day is a workday or weekend, or if a value is even or odd. In this recipe, we will learn how to use conditional logic in DAX to create a custom column that calculates the quarter for a nonstandard fiscal year that runs from June to May instead of from January to December using two different conditional logic DAX functions, IF and SWITCH.

Getting ready

To prepare for this recipe, perform the following steps:

Create a table using the following formula:

R09_Table = GENERATESERIES(DATE(2020,1,1),DATE(2020,12,31))

Create a column in that table using the following formula:

MonthNo = [Value].[MonthNo]

How to do it...

The first conditional logic function that most people use is the IF function. The IF function has the following format:

IF(<condition> , <value to return if true> , <value to return if false)

Complex conditional logic can be created by nesting IF functions. For example, in the true or false value to return, another IF statement can be inserted. To create our custom quarter calculation, we will actually need three nested IF functions. Create a new column in R09_Table with the following formula:

QuarterNo = 
    IF('R09_Table'[MonthNo] < 4, 
        "Q3", // If the month is 1-3 then this is Q3
        IF('R09_Table'[MonthNo] < 7, 
            "Q4", // If the month is 3-6 then this is Q4
            IF('R09_Table'[MonthNo] < 10,
                "Q1", // If the month is 7-9 then this is Q1
                "Q2" // If no other condition is met, then the month is 10-12, Q2
            ) // End 3rd IF
        ) // End 2nd IF
    ) // End 1st IF

IF statements work perfectly fine for conditional logic, but, as you can see, nesting can become somewhat difficult to format and read. A preferred way to do conditional logic is to instead use the SWITCH statement for easier formatting and readability. The SWITCH statement has the following format:

SWITCH(<expression> , <value> , <result> [ , <value> , <result>]...[ , <else>])

An equivalent SWITCH statement can be written by creating a new column with the following formula:

QuarterNo1 =
 SWITCH(
 'R09_Table'[MonthNo], // Check the [MonthNo] column
 1,"Q3", // If the [MonthNo] is 1, then return Q3
 2,"Q3", // If the [MonthNo] is 2, then return Q3
 3,"Q3", // If the [MonthNo] is 3, then return Q3
 4,"Q4", // If the [MonthNo] is 4, then return Q4
 5,"Q4", // If the [MonthNo] is 5, then return Q4
 6,"Q4", // If the [MonthNo] is 6, then return Q4
 7,"Q1", // If the [MonthNo] is 7, then return Q1
 8,"Q1", // If the [MonthNo] is 8, then return Q1
 9,"Q1", // If the [MonthNo] is 9, then return Q1
 "Q2" // Else if none of the other conditions are met, Q2
 )

How it works...

Looking at the IF formula, the formula starts with a condition, [MonthNo] < 4, for the first parameter. If this condition is true, then the next parameter is returned, in this case Q3. However, if the statement is not true, then another IF statement is evaluated and the pattern continues. It is important to understand that IF conditions are evaluated in order. In other words, the first IF statement is evaluated first and must be evaluated as either true or false before either returning a value or executing the next IF statement. This is why we do not need to worry about month numbers 1, 2, and 3 when evaluating [MonthNo] < 7 in the second IF statement. We can be certain that if this expression evaluates to true, then [MonthNo] must be 4, 5, or 6, and not 1, 2, or 3, because these latter numbers have already been excluded.

For the SWITCH statement, the first parameter is the value to evaluate, in this case [MonthNo]. The subsequent parameters come in pairs, the first value of this pair being the potential value of the expression in the first parameter, and the second being the value to return if the expression in the first parameter equals the paired value. At the end is a catch-all else value, the value to return if no other conditions are met. As you can see, the SWITCH statement, while more verbose, is much easier to read and understand.

There's more...

There is a trick for the SWITCH statement that makes this function even more valuable and also less verbose to write. This trick is the TRUE expression trick, where the first parameter for the SWITCH statement is the TRUE function. When using the SWITCH statement in this way, the first parameter of the value/result parameter pairs is actually a conditional statement that, if true, returns the corresponding result parameter. To demonstrate how this works, create a column with the following formula:

QuarterNo2 =
 SWITCH(
 TRUE(), // First parameter is the function TRUE, which always returns TRUE
 'R09_Table'[MonthNo] < 4,"Q3", // If the [MonthNo] is 1-3, then Q3
 'R09_Table'[MonthNo] < 7,"Q4", // If the [MonthNo] is 4-6, then Q4
 'R09_Table'[MonthNo] < 10,"Q1", // If the [MonthNo] is 7-9, then Q1
 "Q2" // Else, Q2
 )

This version of the SWITCH statement looks very much like our IF statement, but is far easier to read and understand. The first parameter is the TRUE function, which turns on the special mode of the SWITCH statement where the first parameter of the value/result parameter pairs is a conditional statement. If the statement is true, for example [MonthNo] < 4, then the corresponding result is returned, Q3.

Finally, it is worth noting that any conditional logic statement can use AND and OR logic. In DAX, && is used for AND and || is used for OR. For example, our preceding SWITCH statement could alternatively be written as follows:

QuarterNo3 =
 SWITCH(
 TRUE(),
 [MonthNo] = 1 || [MonthNo] = 2 || [MonthNo] = 3,"Q3", // If the [MonthNo] is 1, 2 or 3, Q3
 [MonthNo] = 4 || [MonthNo] = 5 || [MonthNo] = 6,"Q4", // If the [MonthNo] is 4, 5 or 6, Q4
 [MonthNo] = 7 || [MonthNo] = 8 || [MonthNo] = 9,"Q1", // If the [MonthNo] is 7, 8 or 9, Q1
 "Q2" // Else, Q2
 )