DAX Cookbook

5 (1 reviews total)
By Greg Deckler
  • Instant online access to over 8,000+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Thinking in DAX

About 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.

Publication date:
March 2020
Publisher
Packt
Pages
552
ISBN
9781839217074

 

Thinking in DAX

Many moons ago, when learning to write Perl code, I read a blog article about hashes and Perl. While that blog article has been lost to time and the ever-morphing internet, the crux of the article was that if you weren't thinking in hashes, unordered key-value pairs, then you weren't truly thinking in Perl. The theory here was that hashes were the fundamental, native, internal data structure for Perl, and so it was critical that you understood how hashes worked in order to write fast, efficient Perl code. 

While I have moved far beyond Perl code in my career, the lesson of that blog article stuck in my mind as I learned new technologies. I have found it incredibly useful to understand the inner workings of new languages and how those languages think. Thus, this chapter is all about teaching you how to think the way DAX thinks. In other words, teaching you how to understand the base inner workings of DAX so that you can write fast, efficient, reliable, and supportable DAX code.

The recipes included in this chapter are as follows:

  • Using DAX in Excel, Power BI, and SQL
  • Writing good DAX
  • Using variables
  • Confronting context
  • Grouping and summarizing
  • Filtering and unfiltering
  • Exploiting relationships
  • Implementing iterators
  • Using conditional logic
 

Technical requirements

 

Using DAX in Excel, Power BI, and SQL

DAX is the native formula and query language for Microsoft Power Pivot (Excel), Power BI Desktop, and SQL Server Analysis Services (SSAS) tabular models. Thus, DAX can be used in any of these programs.

Getting ready

Ensure that you have Power Pivot for Excel, Power BI Desktop, or an SSAS tabular cube.

How to do it...

Depending upon the program being used, where DAX is entered varies somewhat. Use the following instructions according to the program you are using.

Excel

Perform the following steps to enter DAX in Excel:

  1. Open Excel and create a data model using the Power Pivot tab in the ribbon and then click Add to Data Model. You must have a data model created before you can enter DAX.
  2. To create a measure in Excel, select the Power Pivot tab from the ribbon, and then choose Measures followed by New Measure. A new window called Measure will appear where you can enter a DAX formula in the Formula area.
  1. To create a new calculated column in Excel, select the Power Pivot tab from the ribbon and then Manage. In the Power Pivot window, select Design from the ribbon and then Add in the Columns section. A formula bar will appear just below the ribbon and your cursor will become active in the formula bar.

Power BI

Perform the following steps to enter DAX in Power BI Desktop:

  1. Open Power BI Desktop.
  2. In Power BI Desktop, select the Modeling tab from the ribbon and then choose New Measure | New Column or New Table.

The formula bar will appear just below the ribbon and your cursor will become active in the formula bar.

SQL Server

Perform the following steps to enter DAX in SQL Server:

  1. Open SQL Server Management Studio.
  2. In SQL Server Management Studio, connect to a deployed tabular Analysis Server data model.
  3. Right-click the database in the deployed tabular model and choose New Query and then MDX.
  4. When writing your DAX query, ensure that you begin your query with the EVALUATE keyword.

See also

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

 

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

 

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:

 

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

 

Grouping and summarizing

Grouping and summarizing information is a powerful feature of Excel pivot tables and Power BI table and matrix visualizations. However, it is often necessary to group and summarize information in DAX as well. Grouping and summarizing in DAX can be accomplished through the use of two functions, SUMMARIZE and GROUPBY. In this recipe, we will create new tables that summarize information by using the SUMMARIZE and GROUPBY functions.

Getting ready

To prepare for this recipe, perform the following steps:

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

How to do it...

The SUMMARIZE function has the following syntax:

SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)

To use the SUMMARIZE function to return the number of weekdays in each month as well as the first day when each weekday occurs in each month, create a new table with the following formula:

R05_summarizedTable = 
SUMMARIZE(
'R05_Table', // This is the table to summarize
[Month], // This is the column by which we want to group values
[Weekday], // This is a second column by which we want to group values
"# of Days", // Create a column called "# of Days"
COUNTROWS('R05_Table'), // Return the count of rows for "# of Days"
"First Date", // Create a second column called "First Date"
MINX('R05_Table','R05_Table'[Value]) // Return first date weekday occurs in the month in "First Date"
)

An excerpt of the table returned by this formula is as follows:

Month

# of Days

Weekday

First Date

January

5

Wednesday

1/1/2020 12:00:00 AM

February

4

Wednesday

2/5/2020 12:00:00 AM

March

4

Wednesday

3/4/2020 12:00:00 AM

April

5

Wednesday

4/1/2020 12:00:00 AM

May

4

Wednesday

5/6/2020 12:00:00 AM

June

4

Wednesday

6/3/2020 12:00:00 AM

July

5

Wednesday

7/1/2020 12:00:00 AM

August

4

Wednesday

8/5/2020 12:00:00 AM

September

5

Wednesday

9/2/2020 12:00:00 AM

October

4

Wednesday

10/7/2020 12:00:00 AM

November

4

Wednesday

11/4/2020 12:00:00 AM

December

5

Wednesday

12/2/2020 12:00:00 AM

January

5

Thursday

1/2/2020 12:00:00 AM

February

4

Thursday

2/6/2020 12:00:00 AM

 

We can also use the GROUPBY function to return the same information in a table. The GROUPBY function has the following format:

GROUPBY (<table>, [<groupBy_columnName1>], [<name>, <expression>]… )

To do this, create a new table with the following formula:

R05_groupedTable = 
GROUPBY(
'R05_Table', // This is the table to group
[Month], // This is the column to group by
[Weekday], // This is a second column by which we want to group values
"# of Days", // Create a new column in this table called "# of Days"
COUNTX(CURRENTGROUP(),'R05_Table'[Value]), // Return the count of values for "# of Days"
"First Date", // Create a second column called "First Date"
MINX(CURRENTGROUP(),'R05_Table'[Value]) // Return first date weekday occurs in the month in "First Date"
)

This formula returns a table that is identical to our SUMMARIZE formula, except that the order of the rows differs slightly.

How it works...

Looking at the SUMMARIZE formula, the first parameter is the table that we want to summarize, and the next two columns are the columns according to which we want to group our data. Note that you can group by one, two, three, or nearly any number of columns.

The next four parameters are name/expression pairs. DAX understands where these pairs start when you stop referring to column names in the table and enter a text value for a parameter denoted by double quotes. These name/expression pairs specify a column name as the first part of the pair and a DAX expression to evaluate as the second portion of the pair. We first create a column called # of Days, and the value to be returned in this column is the count of the rows from our groupings. In other words, we get the number of each weekday in each month as our value. For the second column called First Date, we return the minimum date from our groupings. In other words, we get the first date of each weekday within each month as our value.

Looking at our GROUPBY formula, the first parameter is the table that we want to group. The next two columns are the columns according to which we want to group our data. This works in the same way as the SUMMARIZE function in that you can group by one, two, three, or nearly any number of columns.

Again, with the next four parameters, these are similar to the SUMMARIZE function in that they are name/expression pairs. This works exactly like the SUMMARIZE function except that instead of referring to the original table, you must refer to a special DAX function that can only be used within a GROUPBY function – the CURRENTGROUP function. In other words, you are referring to the current row of the Cartesian product created by your grouping columns.

There's more...

OK, so the most obvious question is likely something related to why there are two extremely similar functions that essentially do precisely the same thing. The answer is that while they are similar and do similar things, the way in which these functions go about what they do is fairly different. The order of the resulting table rows returned by these functions provides a hint that they are operating somewhat differently within the bowels of DAX.

Without going into excruciating detail regarding the internals of DAX calculations, the best way to describe the differences is to understand that within name/expression pairs of a SUMMARIZE function, the expression portion must always refer to an actual table name within the data model, while within the name/expression pairs of a GROUPBY function, the expression portion must always refer to CURRENTGROUP. This means that if you were to nest two SUMMARIZE functions, you could not refer to the table or columns created by the first SUMMARIZE function within the second (nested) SUMMARIZE function.

However, with GROUPBY, it would be possible to refer to a column created by the first GROUPBY function within the second (nested) GROUPBY function. This can be fairly powerful under the right circumstances. Conversely, because the SUMMARIZE expressions do not need to refer to CURRENTGROUP, this provides a certain flexibility that is not possible when using GROUPBY. For example, if you were to change the last parameter of each formula, within SUMMARIZE, as shown in the following line of code: 

DAY(MINX('R05_Table',[Value]))

This would work and you would return just the day of the first date of the weekday within each month instead of the full date. However, you would receive an error in the GROUPBY formula because the specified expression does not aggregate over the current group.

See also

 

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, Monthand 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

 

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

 

Implementing iterators

In DAX, iterators are functions that evaluate an expression for every row of a table and then aggregate the result. These functions are called iterators because the functions iterate over each row of a table. Within DAX, iterator functions end with an X character and include the following functions:

  • AVERAGEX
  • COUNTAX
  • COUNTX
  • GEOMEANX
  • MAXX
  • MEDIANX
  • MINX
  • PRODUCTX
  • STDEVX.P
  • STDEVX.S
  • SUMX
  • VARX.P
  • VARX.S

Each of these iterator functions performs exactly the same calculation as their non-X equivalent aggregation functions, except that the X functions perform their aggregation over a table specified as the first parameter of the function.

All of these iterator functions have the following general form:

<function>(<table>, <expression>)

Here, <function> is the name of the iterator function. Each iterator function takes a table as its first parameter as well as a DAX expression as its second parameter. The expression is evaluated for each row of the table and then the aggregation function aggregates the results of each of those evaluations.

Getting ready

To prepare for this recipe, perform the following steps:

  1. Create a table using the following formula:
R08_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:
MonthNo = FORMAT([Value].[MonthNo],"00")
  1. Create a third column in that table using the following formula:
Weeknum = FORMAT(WEEKNUM([Value],1),"00")

How to do it...

To implement this recipe, perform the following steps:

  1. Create the following three measures:
Count = COUNTX('R08_Table',[Value])
Max = MAXX(R08_Table,[Year] & [MonthNo] & [Weeknum])
Min = MINX(R08_Table,[Year] & [MonthNo] & [Weeknum])
  1. Place each of these measures in its own Card visualization and note the values returned by each:
  • Count: 1096
  • Max: 20221253
  • Min: 20200101

How it works...

For the Count measure, the first parameter is again our table, R08_Table, which has a row for every day of the years 2020, 2021, and 2022. For each row, the expression simply evaluates to the value of the date in our Value column. The iterator then simply counts how many values have been returned as its aggregation, in this case, 1,096 days. That is 366 values for 2020, a leap year, and then 365 for both 2021 and 2022.

For the Max measure, the first parameter is again our table, R08_Table, which has a row for every day of the years 2020, 2021, and 2022. For each row in the table, the expression concatenates the Year, MonthNo, and Weeknum columns using the ampersand (&) concatenation operator. Once all of the values are calculated, the MAXX function then performs the aggregation step to return the maximum value calculated, in this case, 20221253, the last week of December in the year 2022.

The Min measure works in an identical fashion to the Max measure, except that the MINX function returns the minimum value calculated, in this case, 20200101, the first week of January in the year 2020.

There's more...

The first parameter to iterate functions does not have to be simply a table reference; it can actually be any DAX expression that returns a table. To demonstrate this, create the following measure:

Product = PRODUCTX(DISTINCT('R08_Table'[MonthNo]), [MonthNo]+0)

Place this measure in a Card visualization and note that the value displayed is 479M, or 479 million.

In this measure, we use the DISTINCT function to return only the unique values from the MonthNo column of our table, R08_Table. This returns the numbers 1-12. As our expression, we add 0 to the MonthNo column for each row in order to convert the expression to a numeric value from text. The PRODUCTX function then multiplies each of these unique values together. If you calculate the factorial of 12 (12!) on a calculator, the value is indeed 479,001,600, or roughly 479 million.

See also

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

 

Using conditional logic

Conditional logic is an important concept for programming in general, and DAX is no different. There are many circumstances where we want to present different values for a measure or column based upon a series of conditions or circumstances; for example, if a particular day is a workday or weekend, or if a value is even or odd. In this recipe, we will learn how to use conditional logic in DAX to create a custom column that calculates the quarter for a nonstandard fiscal year that runs from June to May instead of from January to December using two different conditional logic DAX functions, IF and SWITCH.

Getting ready

To prepare for this recipe, perform the following steps:

  1. Create a table using the following formula:
R09_Table = GENERATESERIES(DATE(2020,1,1),DATE(2020,12,31))
  1. Create a column in that table using the following formula:
MonthNo = [Value].[MonthNo]

How to do it...

The first conditional logic function that most people use is the IF function. The IF function has the following format:

IF(<condition> , <value to return if true> , <value to return if false)

Complex conditional logic can be created by nesting IF functions. For example, in the true or false value to return, another IF statement can be inserted. To create our custom quarter calculation, we will actually need three nested IF functions. Create a new column in R09_Table with the following formula:

QuarterNo = 
IF('R09_Table'[MonthNo] < 4,
"Q3", // If the month is 1-3 then this is Q3
IF('R09_Table'[MonthNo] < 7,
"Q4", // If the month is 3-6 then this is Q4
IF('R09_Table'[MonthNo] < 10,
"Q1", // If the month is 7-9 then this is Q1
"Q2" // If no other condition is met, then the month is 10-12, Q2
) // End 3rd IF
) // End 2nd IF
) // End 1st IF

IF statements work perfectly fine for conditional logic, but, as you can see, nesting can become somewhat difficult to format and read. A preferred way to do conditional logic is to instead use the SWITCH statement for easier formatting and readability. The SWITCH statement has the following format:

SWITCH(<expression> , <value> , <result> [ , <value> , <result>]...[ , <else>])

An equivalent SWITCH statement can be written by creating a new column with the following formula:

QuarterNo1 =
SWITCH(
'R09_Table'[MonthNo], // Check the [MonthNo] column
1,"Q3", // If the [MonthNo] is 1, then return Q3
2,"Q3", // If the [MonthNo] is 2, then return Q3
3,"Q3", // If the [MonthNo] is 3, then return Q3
4,"Q4", // If the [MonthNo] is 4, then return Q4
5,"Q4", // If the [MonthNo] is 5, then return Q4
6,"Q4", // If the [MonthNo] is 6, then return Q4
7,"Q1", // If the [MonthNo] is 7, then return Q1
8,"Q1", // If the [MonthNo] is 8, then return Q1
9,"Q1", // If the [MonthNo] is 9, then return Q1
"Q2" // Else if none of the other conditions are met, Q2
)

How it works...

Looking at the IF formula, the formula starts with a condition, [MonthNo] < 4, for the first parameter. If this condition is true, then the next parameter is returned, in this case Q3. However, if the statement is not true, then another IF statement is evaluated and the pattern continues. It is important to understand that IF conditions are evaluated in order. In other words, the first IF statement is evaluated first and must be evaluated as either true or false before either returning a value or executing the next IF statement. This is why we do not need to worry about month numbers 1, 2, and 3 when evaluating [MonthNo] < 7 in the second IF statement. We can be certain that if this expression evaluates to true, then [MonthNo] must be 4, 5, or 6, and not 1, 2, or 3, because these latter numbers have already been excluded.

For the SWITCH statement, the first parameter is the value to evaluate, in this case [MonthNo]. The subsequent parameters come in pairs, the first value of this pair being the potential value of the expression in the first parameter, and the second being the value to return if the expression in the first parameter equals the paired value. At the end is a catch-all else value, the value to return if no other conditions are met. As you can see, the SWITCH statement, while more verbose, is much easier to read and understand. 

There's more...

There is a trick for the SWITCH statement that makes this function even more valuable and also less verbose to write. This trick is the TRUE expression trick, where the first parameter for the SWITCH statement is the TRUE function. When using the SWITCH statement in this way, the first parameter of the value/result parameter pairs is actually a conditional statement that, if true, returns the corresponding result parameter. To demonstrate how this works, create a column with the following formula:

QuarterNo2 =
SWITCH(
TRUE(), // First parameter is the function TRUE, which always returns TRUE
'R09_Table'[MonthNo] < 4,"Q3", // If the [MonthNo] is 1-3, then Q3
'R09_Table'[MonthNo] < 7,"Q4", // If the [MonthNo] is 4-6, then Q4
'R09_Table'[MonthNo] < 10,"Q1", // If the [MonthNo] is 7-9, then Q1
"Q2" // Else, Q2
)

This version of the SWITCH statement looks very much like our IF statement, but is far easier to read and understand. The first parameter is the TRUE function, which turns on the special mode of the SWITCH statement where the first parameter of the value/result parameter pairs is a conditional statement. If the statement is true, for example [MonthNo] < 4, then the corresponding result is returned, Q3.

Finally, it is worth noting that any conditional logic statement can use AND and OR logic. In DAX, && is used for AND and || is used for OR. For example, our preceding SWITCH statement could alternatively be written as follows:

QuarterNo3 =
SWITCH(
TRUE(),
[MonthNo] = 1 || [MonthNo] = 2 || [MonthNo] = 3,"Q3", // If the [MonthNo] is 1, 2 or 3, Q3
[MonthNo] = 4 || [MonthNo] = 5 || [MonthNo] = 6,"Q4", // If the [MonthNo] is 4, 5 or 6, Q4
[MonthNo] = 7 || [MonthNo] = 8 || [MonthNo] = 9,"Q1", // If the [MonthNo] is 7, 8 or 9, Q1
"Q2" // Else, Q2
)

See also

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

About the Author

  • Greg Deckler

    Greg Deckler is Vice President of the Microsoft Practice at Fusion Alliance and has been a professional technology systems consultant for over 25 years. Internationally recognized as an expert in Power BI, Greg Deckler is a Microsoft MVP for Data Platform and a superuser within the Power BI community with over 100,000 messages read, more than 11,000 replies, over 2,300 answers, and more than 75 entries in the Quick Measures Gallery. Greg founded the Columbus Azure ML and Power BI User Group (CAMLPUG) and presents at numerous conferences and events, including SQL Saturday, DogFood, and the Dynamic Communities User Group/Power Platform Summit.

    Browse publications by this author

Latest Reviews

(1 reviews total)
Dużo użytecznej wiedzy. Ok

Recommended For You

Book Title
Access this book, plus 8,000 other titles for FREE
Access now