Book Image

DAX Cookbook

By : Greg Deckler
Book Image

DAX Cookbook

By: Greg Deckler

Overview of this book

DAX provides an extra edge by extracting key information from the data that is already present in your model. Filled with examples of practical, real-world calculations geared toward business metrics and key performance indicators, this cookbook features solutions that you can apply for your own business analysis needs. You'll learn to write various DAX expressions and functions to understand how DAX queries work. The book also covers sections on dates, time, and duration to help you deal with working days, time zones, and shifts. You'll then discover how to manipulate text and numbers to create dynamic titles and ranks, and deal with measure totals. Later, you'll explore common business metrics for finance, customers, employees, and projects. The book will also show you how to implement common industry metrics such as days of supply, mean time between failure, order cycle time and overall equipment effectiveness. In the concluding chapters, you'll learn to apply statistical formulas for covariance, kurtosis, and skewness. Finally, you'll explore advanced DAX patterns for interpolation, inverse aggregators, inverse slicers, and even forecasting with a deseasonalized correlation coefficient. By the end of this book, you'll have the skills you need to use DAX's functionality and flexibility in business intelligence and data analytics.
Table of Contents (15 chapters)

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:

  1. Create a table using the following formula:
R09_Table = GENERATESERIES(DATE(2020,1,1),DATE(2020,12,31))
  1. 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
)

See also

For more details regarding this recipe, refer to the following links: