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)

Filtering and unfiltering

Filtering is a critical concept in DAX because filters provide the main context under which DAX calculations evaluate. In addition, unlike when working with Excel, you cannot specify exact cells or ranges within DAX. Instead, if you want to use particular rows and columns of information within a table, you must filter that table down to the particular rows and columns desired for your calculation. The primary DAX function that allows you to filter rows in a table is the FILTER function.

Conversely, DAX allows you to remove, ignore, and change filter context within calculations. This is powerful and useful in many situations, such as in Power BI, where slicers, page, or report filters may need to be overridden within certain calculations and visualizations. DAX functions that allow the removal or editing of filter behavior include the following:

  • ALL
  • ALLCROSSFILTERED
  • ALLEXCEPT
  • ALLNOBLANKROW
  • ALLSELECTED
  • KEEPFILTERS
  • REMOVEFILTERS

Getting ready

To prepare for this recipe, perform the following steps:

  1. Create a table using the following formula:
R06_Table = GENERATESERIES(DATE(2020,1,1),DATE(2022,12,31))
  1. Create a column in that table using the following formula:
Year = [Value].[Year]
  1. Create a second column in that table using the following formula:
Month = [Value].[Month]
  1. Create a third column in that table using the following formula:
Weekday = FORMAT([Value], "dddd")

How to do it...

To implement this recipe, perform the following steps:

  1. Create the following measures:
Days = COUNTROWS('R06_Table')

January Days = COUNTROWS(FILTER('R06_Table',[Month] = "January"))

January Wednesday Days = COUNTROWS(FILTER('R06_Table',[Month] = "January" && [Weekday] = "Wednesday"))

All Days = COUNTROWS(ALL('R06_Table'))

Weekday Days = CALCULATE(COUNTROWS('R06_Table'), ALLEXCEPT('R06_Table','R06_Table'[Weekday]))

All Years January Days = CALCULATE([January Days],REMOVEFILTERS('R06_Table'[Year]))

January Days? =
CALCULATE(
CALCULATE(
COUNTROWS('R06_Table'),
'R06_Table'[Month] = "January"
) ,
'R06_Table'[Month] = "February"
)

January Days! =
CALCULATE(
CALCULATE(
COUNTROWS('R06_Table'),
KEEPFILTERS(
'R06_Table'[Month] = "January"
|| 'R06_Table'[Month] = "February"
)
) ,
'R06_Table'[Month] = "January"
|| 'R06_Table'[Month] = "March"
)
  1. Place each of these measures in a Card visualization on a page.
  2. Add three slicers to this page for the Year, Month, and Weekday columns from the R06_Table table.

How it works...

With all of the slicers set to All, the values for each of these measures is as follows:

Measure

Value

Explanation

Days

1096

This measure counts all of the rows in the table (366 + 365 + 365).

January Days

93

This measure only counts days in the table with a month of January (31 + 31 + 31).

January Wednesday Days

13

This measure only counts days in January that are Wednesdays (5 + 4 + 4).

All Days

1096

This measure always counts all of the rows in the table (366 + 365 + 365).

Weekday Days

1096

This measure counts all of the rows in the table unless there is a weekday filter (366 + 365 + 365).

All Years January Days

93

This measure counts days in January, ignoring filters for Year, but not other filters.

January Days?

93

This measure only counts days in January.

January Days!

93

This measure only counts days in January.

For January Days?, it may appear odd that this measure calculates the number of days in January for the three years of dates listed in the table since the outer CALCULATE function clearly specifies a filter of February. However, the default behavior of CALCULATE is to use the innermost filter value when the same column is specified within nested CALCULATE statements.

Given the default behavior of the CALCULATE function, it may seem even more odd that the January Days! measure also returns the number of days in January. The reason for this is the KEEPFILTERS function. The KEEPFILTERS function changes the default behavior of the CALCULATE function by adding another filter. The calculation now becomes the intersection of the two filters instead of a complete override. Thus, since the only value that is in common between the two filters is January, only the count of the days in January is returned!

There's more...

Use the Year slicer to only choose 2020. The values for the measures become the following:

Measure

Value

Explanation

Days

366

This measure is being filtered by the Year slicer and 2020 is a leap year.

January Days

31

This measure is being filtered by the Year slicer and January has 31 days.

January Wednesday Days

5

This measure is being filtered by the Year slicer and January 2020 has five Wednesdays.

All Days

1096

The ALL function overrides the filter from the Year slicer, so this is a count of all rows in the table.

Weekday Days

1096

The ALLEXCEPT function overrides the filter from the Year slicer, so this is a count of all rows in the table.

All Years January Days

93

The REMOVEFILTERS function removes the Year slicer filter from the calculation, so this is the number of days in January for the three years.

January Days?

93

This measure only counts days in January and is filtered by the Year slicer.

January Days!

93

This measure only counts days in January and is filtered by the Year slicer.

Leave the Year slicer set to 2020 and now use the Month slicer to only choose February. The values for the measures become the following:

Measure

Value

Explanation

Days

29

This measure is being filtered by the Year slicer and the Month slicer and 2020 is a leap year.

January Days

(Blank)

This measure is being filtered by the Year slicer and the Month slicer, but also has a filter within the calculation of January. Since February and January have no intersecting days, the ultimate value is blank (null).

January Wednesday Days

5

This measure is being filtered by the Year slicer and the Month slicer, but also has a filter within the calculation of January and Wednesday. Since February and January have no intersecting days, the ultimate value is blank (null).

All Days

1096

The ALL function overrides the filter from the Year and Month slicers, so this is a count of all rows in the table.

Weekday Days

1096

The ALLEXCEPT function overrides the filter from the Year and Month slicers, so this is a count of all rows in the table.

All Years January Days

(Blank)

The REMOVEFILTERS function removes the Year slicer filter from the calculation, but not the Month slicer filter. Since February and January have no intersecting days, the ultimate value is blank (null).

January Days?

31

Since the Month slicer and internal filters all refer to the same column, the default behavior for the CALCULATE function is to override all filters on that column with the innermost filter, which is January Days. However, the Year slicer refers to a different column, so the filter on the Year column is also enforced.

January Days!

31

KEEPFILTERS modifies the behavior of the CALCULATE function, such that this is the intersection of all of the filters on the Month column, which still only leaves January. However, the Year slicer refers to a different column, so the filter on the Year column is also enforced. The Month slicer is effectively ignored. You can test this by switching the Month slicer to November, December, or another value.

Leave the Year slicer set to 2020 and the Month slicer set to February. Now, change the Weekday slicer to only choose Friday. The values for the measures become the following:

Measure

Value

Explanation

Days

29

This measure is being filtered by the Year slicer, Month slicer, and Weekday slicer, and there are only four Fridays in January 2020.

January Days

(Blank)

This measure is being filtered by the Year slicer and the Month slicer, but also has a filter within the calculation of January. Since February and January have no intersecting days, the ultimate value is blank (null).

January Wednesday Days

5

This measure is being filtered by the Year slicer, Month slicer, and Weekday slicer, but also has a filter within the calculation of January and Wednesday. Since February and January have no intersecting days, the ultimate value is blank (null). This measure would also be blank if you selected January in the Month slicer instead of February since there are no Fridays that are also Wednesdays!

All Days

1096

The ALL function overrides the filter from the Year, Month, and Weekday slicers, so this is a count of all rows in the table.

Weekday Days

1096

The ALLEXCEPT function overrides the filter from the Year and Month slicers, but not the Weekday slicer, so this is a count of all Fridays for all years and months in the table.

All Years January Days

(Blank)

The REMOVEFILTERS function removes the Year slicer filter from the calculation, but not the Month slicer filter. Since February and January have no intersecting days, the ultimate value is blank (null).

January Days?

5

Since the Month slicer and internal filters all refer to the same column, the default behavior for the CALCULATE function is to override all filters on that column with the innermost filter. However, the Year and Weekday slicers refer to different columns, so the filters on the Year and Weekday columns are also enforced.

January Days!

5

KEEPFILTERS modifies the behavior of the CALCULATE function such that this is the intersection of all of the filters on the Month column, which still only leaves January. However, the Year and Weekday slicers refer to different columns, so the filters on the Year and Weekday columns are also enforced.

See also