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 variables

While DAX functions can be nearly infinitely nested, using variables can avoid doing the same calculation multiple times and also improves overall code readability. Using variables can help you break complex calculations down into smaller, more consumable pieces that can be individually verified step by step.

Getting ready

To prepare for this recipe, perform the following steps:

  1. Create a table using the following formula:
R3_Table = GENERATESERIES(1,30,1)
  1. Create a column in that table using the following formula:
Column = ROUNDUP('R3_Table'[Value]/11,0)

How to do it...

Variables are created by using the VAR statement with the following syntax:

VAR <name> = <expression>

<name> is the name of the variable. Variable names must begin with the letters a-z or A-Z. The only other supported characters within variable names are the characters 0-9. Existing table names and certain keywords are not permitted as variable names.

DAX calculations that use a VAR statement must also use a RETURN statement.

By following these principles, we can write a DAX measure as follows:

Variable DAX = 
/*
* This measure summarizes the table, Table, grouping by [Column] and summing [Column1]. This
* summarized table is then filtered to values 2 and 3 in [Column] and then sums up [Column1]
*
* Gregory J. Deckler
* [email protected]
* 10/7/2019
*/
VAR __summarizedTable = // Summarize table by [Column], summing [Value]
SUMMARIZE(
'R03_Table',
'R03_Table'[Column],
"__Value",
SUM( 'R03_Table'[Value] )
)
VAR __filteredTable = // Filter summarized table for 2 and 3
FILTER(
__summarizedTable, // Here we use our __summarizedTable variable
[Column] = 2
||
[Column] = 3
)
VAR __sum = // Sum [__Value]
SUMX(
__filteredTable, // Here we use our __filteredTable variable
[__Value]
)
RETURN // If result is < 400, return the sum, otherwise -1
IF(
__sum < 400, // We avoid having to do the same calculation twice
__sum,
-1
)

How it works...

The code creates three variables, __summarizedTable, __filteredTable, and __sum. The __summarizedTable variable creates a table in memory using the SUMMARIZE function to return a table summarized by [Column] and by summing the [Value] column. This variable takes on the value of the table as shown:

Column

__Value

1

66

2

187

3

212

The __filteredTable variable uses the FILTER function to filter the table represented by the __summarizedTable variable to just the values 2 and 3 in the [Column] column. This variable takes on the value of the table as shown:

Column

__Value

2

187

3

212

The __sum variable uses the SUMX function to sum the [__Value] column of the two remaining rows in the table, taking on a value of 187 + 212, or 399.

The RETURN statement uses an IF statement. This IF statement checks to see whether the __sum variable is less than 400. If the variable is less than 400, the value stored in the __sum variable is returned. Otherwise, the value -1 is returned.

Variables can only be referenced within the DAX calculation in which they are created. As shown in both __filteredTable and __sum, variables can refer to previously created variables within the same DAX calculation.

There's more...

You can nest variables. For example, this recipe could be written as follows:

Nested Variable DAX = 
/*
* This measure summarizes the table, Table, grouping by [Column] and summing [Column1]. This
* summarized table is then filtered to values 2 and 3 in [Column] and then sums up [Column1]
*
* Gregory J. Deckler
* [email protected]
* 10/7/2019
*/
VAR __sum = // Sum [__Value]
SUMX(
VAR __filteredTable =
FILTER( // Filter summarized table for 2 and 3
VAR __summarizedTable = // Summarize table by [Column], summing [Value]
SUMMARIZE(
'R03_Table',
'R03_Table'[Column],
"__Value",
SUM( 'R03_Table'[Value] )
)
RETURN __summarizedTable // Return the summarized table
,
[Column] = 2
||
[Column] = 3
)
RETURN __filteredTable // Return the filtered table
,
[__Value]
)
RETURN // If result is < 400, return the sum, otherwise -1
IF(
__sum < 400,
__sum,
-1
)

See also

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