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)

Exploiting relationships

Relationships connect tables together within a data model by defining an affiliation between a column in one table and a column in a second table. Creating a relationship between two columns in a table ties the two tables together such that it is expected that values from a column in the first table will be found in the other column in the second table. These table relationships can be exploited by DAX calculations as DAX intrinsically understands these relationships within the data model.

Getting ready

To prepare for this recipe, perform the following steps:

  1. Create a table using the following formula:
R07_TableA = GENERATESERIES(DATE(2020,1,1),DATE(2020,12,31))
  1. Create a column in this table using the following formula:
Month = [value].[Month]
  1. Create a second table called R07_TableB using an Enter Data query with the following data:

Month

Date

January

1/1/2020

February

2/1/2020

March

3/1/2020

April

4/1/2020

May

5/1/2020

June

6/1/2020

July

7/1/2020

August

8/1/2020

September

9/1/2020

October

10/1/2020

November

11/1/2020

December

12/1/2020

  1. Ensure that the Date column is formatted as Date/Time.
  2. Create a relationship between the Month column in the R07_TableB table and the Month column in the R07_TableA table. Make sure that the cross-filter direction of this relationship is set to Both.
  3. Create a measure using the following formula:
R07_CountOfDays = COUNTROWS('R07_TableA')

How to do it...

To demonstrate how relationships work, perform the following steps:

  1. On a Report page, create a Table visualization and place the Month column from the R07_TableB table as a field in the visual.
  2. While that visualization is still selected, place the R07_CountOfDays measure in the visual.
  3. Create a second Table visual and place the Month column from the R07_TableA table as a field in the visual.
  4. With this second visualization still selected, drag the Date column from the R07_TableA table into the visual and change its default aggregation to Count.

The first Table visualization lists the months in alphabetical order, while the R07_CountOfDays measure displays the number of days in each month. The second Table visualization also lists the months in alphabetical order and the second column displays 1 for all rows.

How it works...

For the first visualization, placing the Month column from R07_TableB in the visualization creates filter context on the R07_TableA table. Thus, when the R07_CountOfDays measure is calculated in this context, this filter context from the relationship applies to the calculation. Thus, we get the number of rows in R07_TableA that are related to the relevant context. In short, the number of rows in R07_TableA that have the same month as our month values from R07_TableB are displayed.

The second visualization functions in the same way, except that since we placed the Month column from R07_TableA in the visualization and are counting the values in R07_TableB, a 1 is displayed because there is only a single matching row in R07_TableB that matches each distinct Month value in R07_TableA.

There's more...

To explore relationships and how they affect filter context, perform the following steps:

  1. Create a second relationship between the Value column in R07_TableA and the Date column in R07_TableB.
  2. Ensure that the relationship direction is Both and note that the line is dotted. The dotted line indicates that this relationship is inactive. This is because data models can only have a single active filter pathway between tables.
  3. Create the following measure:
R07_CountOfDays2 = CALCULATE([R07_CountOfDays],USERELATIONSHIP(R07_TableA[Value],R07_TableB[Date]))
  1. Place this measure in the first Table visualization created.

Note that adding this measure, R07_CountOfDays2, to the first table visualization lists the value of 1 for each month. In addition, an extra row is added to the visualization that has no value for the Month column and lists a value of 354 for the R07_CountOfDays2 measure.

The way that the R07_CountOfDays2 measure works is that we have explicitly overridden the default filter context by the CALCULATE function to explicitly define our filter context and then used the USERELATIONSHIP DAX function to define that filter context. Essentially, we have explicitly told DAX to use the inactive relationship we created as its filter context between the two tables. Thus, only a single row in R07_TableA matches each date value in R07_TableB. However, we have 354 (366 days in 2020 minus 12 matching rows) rows in R07_TableA that do not match any value in R07_TableB, and so this shows up in our table visualization. This actually demonstrates a powerful feature of DAX in helping us find bad data.

See also