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)

Writing good DAX

While the term good can be subjective, writing good DAX code is very much like writing code in other programming languages; the code should be readable and easy to understand. In this recipe, we will learn how to properly format DAX as well as how to use comments.

Getting ready

To prepare for this recipe, perform the following steps:

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

How to do it...

Let's assume that you have a measure formula that looks something like the following:

Bad DAX = SUMX(FILTER(SUMMARIZE('R02_Table',[Column],"Value",SUM([Value])),[Column]=2||[Column]=3),[Value])

While the preceding formula is syntactically correct and will work, it is difficult to read and understand the intent of the calculation. There are a number of best practices that can be followed to make the code more readable, including the following:

  1. Use single-line comments (//) or comment blocks (/*...*/) to document the calculation being made.
  2. Use Alt+Enter and Tab to separate and indent nested functions and multiple arguments.
  1. Always use the full, canonical syntax when referring to a column, enclosing the table name portion in single quotes.
  2. Use spaces to provide visual separation between elements, including parentheses and operators.
  3. When creating data elements within a calculation, such as columns, clearly name these elements distinctly in order to avoid confusion.

Following these simple best practices, we can rewrite the measure as follows:

Good DAX = 
/* Creates a filtered sum for a certain business purpose
*
* Gregory J. Deckler
* [email protected]
* 10/7/2019
*/
SUMX(
FILTER(
SUMMARIZE(
'R02_Table',
'R02_Table'
[Column],
"__Value",
SUM( 'R02_Table'[Value] )
) , // End SUMMARIZE
'R02_Table'
[Column] = 2
||
'R02_Table'
[Column] = 3
) , // End FILTER
[__Value]
) // End SUMX
It is best practice to use single quotes for referencing table names. The issue is consistency. If you have spaces in your table names, then you need single quotes. If you do not, you can get away with not having single quotes, but it will burn you eventually. So, it is better to always use single quotes, and this has the added benefit of you always knowing that these are tables being referenced when you see single quotes.

How it works...

The comment block at the top of the function provides useful information regarding the purpose of the measure as well as a description regarding its operation. In addition, this comment block includes information about the author, including contact information and when the calculation was created. This information assists someone else reviewing the code or the author if revisiting the code at a later date. A space and asterisk have been added to lines within the comment block to visually cue the reader that the entire comment section belongs together. In addition to the comment block, inline comments have been used to call out where functions end. This makes it much easier to read the code instead of hunting for beginning and end parentheses.

Each function has been placed on its own, separate line by using the Alt+Enter key combination. In addition, each argument for each function is also on its own line, except for the SUM function, since this only has a single argument. The Tab key has been used to indent the nested functions, clearly denoting the nesting hierarchy. In addition, the Tab key has been used to indent function arguments underneath each function, visually keeping coding elements together.

The full, canonical name of columns has been used in order to remove any ambiguity and improve readability. Someone looking at the code immediately understands what column and table is being referenced in the code. These table names have been prefixed and suffixed with single quotes. While not required for table names without spaces, for consistency, they should always be used.

Spaces inserted after beginning parentheses and before end parentheses, as well as before and after the equals sign, provide visual separation between elements and make things easier to read.

Finally, the creation of the column in the SUMMARIZE statement has been created with the name prefixed by two underscore characters, unlike the original formula, where this column is the same name as a column from the original table. While DAX can generally figure out which column is being referenced, having duplicate names is confusing for the reviewer and can create actual confusion and problems in complex DAX formulas.

There's more...

Inline or single-line comments can also be executed by using -- instead of // at the beginning of the comment. You can also use Ctrl+/ to automatically comment out or comment in a line using the // style of comment.

Instead of using Alt+Enter and Tab, you can use Shift+Enter to move to a new line and indent all at once. In addition, you can use Ctrl+] to indent and Ctrl+[ to outdent instead of using Tab and Shift+Tab.

See also