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)

Confronting context

There is perhaps no more important subject to understanding DAX than context. Context is essential to DAX and is also something that is relatively unique to the language. In fact, context is so fundamental to DAX that a DAX calculation cannot return a result without context. Thus, understanding context is crucial to understanding DAX as it is context that provides much of the unbridled power of the DAX language. Conversely, context also contributes significantly to the learning curve for the DAX language.

Official Microsoft documentation cites three types of context:

  • Row context
  • Query context
  • Filter context

Most other sources essentially ignore the concept of query context, and the Microsoft documentation is somewhat vague regarding this concept. The best analysis is that the combination of row and filter creates the final query context for DAX to retrieve the required data from the underlying data model for the requisite calculation. Users essentially only ever explicitly define row and filter context for DAX, and DAX itself implicitly creates query context from the row and filter context. Thus, we will focus on row and filter context in this recipe.

Getting ready

To prepare for this recipe, perform the following steps:

  1. Create a table using the following formula:
R04_Table = GENERATESERIES(DATE(2020,1,1),DATE(2022,12,31))
  1. Create a measure in that table using the following formula:
CountOfDays = COUNT([Value])

How to do it...

To demonstrate row context at work, create the following three columns in the R04_Table table:

Year = [value].[Year]
Month = [value].[Month]
Weekday = FORMAT([Value],"dddd")

To demonstrate filter context, perform the following steps:

  1. Create a Report page and place a Matrix visualization on the page.
  2. Within the Matrix visualization selected, place the following columns and measures from the R04_Table table in the indicated fields for the matrix visualization:

Column/Measure

Field

Month

Rows

Year

Columns

CountOfDays

Values

  1. Note that the matrix displays the following information:

Month

2020

2021

2022

Total

April

30

30

30

90

August

31

31

31

93

December

31

31

31

93

February

29

28

28

85

January

31

31

31

93

July

31

31

31

93

June

30

30

30

90

March

31

31

31

93

May

31

31

31

93

November

30

30

30

90

October

31

31

31

93

September

30

30

30

90

Total

366

365

365

1096

  1. Place a Slicer visualization on the same page and place the Weekday column from the R04_Table table in the Field for the slicer.
  2. Select Saturday from the slicer. The Matrix visualization now displays the following:

Month

2020

2021

2022

Total

April

4

4

5

13

August

5

4

4

13

December

4

4

5

13

February

5

4

4

13

January

4

5

5

14

July

4

5

5

14

June

4

4

4

12

March

4

4

4

12

May

5

5

4

14

November

4

4

4

12

October

5

5

5

15

September

4

4

4

12

Total

52

52

53

157

How it works...

With regard to row context, DAX automatically applies row context to any calculated column. Therefore, the three columns created, Year, Month, and Weekday, all have row context applied. This is why there is a single value returned despite the fact that we have no aggregation function applied. Thus, within row context, references to columns such as [Value], when not referenced from within an aggregation function, always return a single value, the value of the referenced column in that row. This is really as complex as row context gets, with the exception that it is possible to create row context outside of tables and calculated columns. To create row context within measures, we can use certain DAX functions such as ADDCOLUMN.

Filter context is somewhat trickier. Filter context is created by the combination of visuals and the fields within those visuals, as well as explicit filters created using the Filters pane in Power BI Desktop or directly within a DAX calculation when using a filters clause. In step 3, the matrix rows and columns define the context for the CountOfDays measure. Thus, for each cell, excluding the Total cells, we get the number of days in each month for each year. This is why the cell intersecting February and 2020 has 29, and 2020 is a leap year. The Total column removes the filter context for the individual columns but not the individual rows, and so we get the total number of days for all three years, 2020, 2021, and 2022, for each month. Conversely, the Total row removes the filter context for the individual rows but not for the individual columns, and so we get the total number of days in each year. Finally, the cell on the right in the bottom row removes the filter context for both the individual rows and individual columns, and so we get the total number of day in all three years. Therefore, the filter context for this cell is effectively no filters or all data referenced by the matrix visualization.

Adding the slicer and selecting an individual weekday adds additional filter context to the matrix since the default in Power BI Desktop is to cross-filter visualizations. Thus, in addition to the filter context of the individual rows and columns in the matrix, the cells also encapsulate the filter context of the slicer, and so we are presented with the number of Saturdays in each month of each year with their corresponding totals in the Totals row and column. Selecting a different weekday from the slicer, or a combination of weekdays, will present their corresponding counts in the matrix visualization.

There's more...

Create a new column in the R04_Table table with the following formula:

Days = COUNT([Value])

You may be surprised to see the number 1096 in this column for every row of the table. This is the count of days in all three years of the table. You may have expected to see 1 for each row in this column. This result is driven by the exception mentioned earlier when dealing with column references in row context. The aggregation function effectively switches the calculation from row context to filter context and, since there is no filter context, the final query context is all rows within the table.

See also