The following are required to complete all of the recipes in this chapter:

• Microsoft Excel (first recipe)
• Power BI Desktop (all recipes)
• SQL Server Management Studio (first recipe)
• Data files: https://github.com/PacktPublishing/DAX-Cookbook/tree/master/Chapter01

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.

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

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

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.

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

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.

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.

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

• Data Analysis Expressions (DAX) in Power Pivot: https://support.office.com/en-us/article/Data-Analysis-Expressions-DAX-in-Power-Pivot-BAB3FBE3-2385-485A-980B-5F64D3B0F730
• DAX basics in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/desktop-quickstart-learn-dax-basics
• Getting started with the DAX queries for SQL Server Analysis Services: https://www.mssqltips.com/sqlservertip/4068/getting-started-with-the-dax-queries-for-sql-server-analysis-services

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.

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)

3. Create a column in that table using the following formula:

Column = ROUNDUP('R02_Table'[Value]/11,0)

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.

3. Always use the full, canonical syntax when referring to a column, enclosing the table name portion in single quotes.
4. Use spaces to provide visual separation between elements, including parentheses and operators.
5. 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
 * gdeckler@fusionalliance.com
 * 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

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.

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.

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

• Rules for DAX formatting: https://www.sqlbi.com/articles/rules-for-dax-code-formatting/
• DAX Formatter: https://www.daxformatter.com/
• DAX formula bar keyboard shortcuts in Power BI Desktop: https://xxlbi.com/blog/dax-formula-bar-keyboard-shortcuts-in-power-bi-desktop/
• The Using variables recipe

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.

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R3_Table = GENERATESERIES(1,30,1)

2. Create a column in that table using the following formula:

Column = ROUNDUP('R3_Table'[Value]/11,0)

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
 * gdeckler@fusionalliance.com
 * 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
    )

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:

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:

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.

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
 * gdeckler@fusionalliance.com
 * 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
    )

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

• VAR: https://docs.microsoft.com/en-us/dax/var-dax
• Using variables in Power BI – a detailed example: https://blog.enterprisedna.co/2019/09/09/using-variables-in-dax-a-detailed-example/
• Using DAX variables in iterators: https://www.kasperonbi.com/using-dax-variables-in-iterators/
• Using variables within DAX: https://powerbi.tips/2017/05/using-variables-within-dax/
• Variables in DAX: https://www.sqlbi.com/articles/variables-in-dax/
• Using variables in DAX: https://exceleratorbi.com.au/using-variables-dax/
• Power BI Quick Measure, Runge Kutta: https://community.powerbi.com/t5/Quick-Measures-Gallery/Runge-Kutta/m-p/411280

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.

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))

2. Create a measure in that table using the following formula:

CountOfDays = COUNT([Value])

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:

3. Note that the matrix displays the following information:

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

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.

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

Days = COUNT([Value])

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

• DAX overview – context: https://docs.microsoft.com/en-us/dax/dax-overview#context
• The most important DAX concept: https://powerdax.com/important-dax-concept/
• Understanding evaluation contexts in DAX: https://www.microsoftpressstore.com/articles/article.aspx?p=2449191

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.

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))

2. Create a column in that table using the following formula:

Month = [Value].[Month]

Weekday = FORMAT([Value], "dddd")

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:

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.

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.

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.

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

• SUMMARIZE: https://docs.microsoft.com/en-us/dax/summarize-function-dax
• GROUPBY: https://docs.microsoft.com/en-us/dax/groupby-function-dax
• Nested grouping using GROUPBY versus SUMMARIZE: https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/

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

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))

2. Create a column in that table using the following formula:

Year = [Value].[Year]

3. Create a second column in that table using the following formula:

Month = [Value].[Month]

Weekday = FORMAT([Value], "dddd")

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"
)

2. Place each of these measures in a Card visualization on a page.

3. Add three slicers to this page for the Year, Month, and Weekday columns from the R06_Table table.

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

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!

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

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:

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:

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

• Filter functions: https://docs.microsoft.com/en-us/dax/filter-functions-dax

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.

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))

2. Create a column in this table using the following formula:

Month = [value].[Month]

3. Create a second table called R07_TableB using an Enter Data query with the following data:

4. Ensure that the Date column is formatted as Date/Time.
5. 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.
6. Create a measure using the following formula:

R07_CountOfDays = COUNTROWS('R07_TableA')

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.

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.

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]))

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

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

• COUNTROWS: https://docs.microsoft.com/en-us/dax/countrows-function-dax
• CALCULATE: https://docs.microsoft.com/en-us/dax/calculate-function-dax
• USERELATIONSHIP: https://docs.microsoft.com/en-us/dax/userelationship-function-dax

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.

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))

2. Create a column in that table using the following formula:

Year = [Value].[Year]

3. Create a second column in that table using the following formula:

MonthNo = FORMAT([Value].[MonthNo],"00")

4. Create a third column in that table using the following formula:

Weeknum = FORMAT(WEEKNUM([Value],1),"00")

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])

2. Place each of these measures in its own Card visualization and note the values returned by each:

• Count: 1096
• Max: 20221253
• Min: 20200101

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.

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.

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

• AVERAGEX: https://docs.microsoft.com/en-us/dax/averagex-function-dax
• COUNTAX: https://docs.microsoft.com/en-us/dax/countax-function-dax
• COUNTX: https://docs.microsoft.com/en-us/dax/countx-function-dax
• GEOMEANX: https://docs.microsoft.com/en-us/dax/geomeanx-function-dax
• MAXX: https://docs.microsoft.com/en-us/dax/maxx-function-dax
• MEDIANX: https://docs.microsoft.com/en-us/dax/meadianx-function-dax
• MINX: https://docs.microsoft.com/en-us/dax/minx-function-dax
• PRODUCTX: https://docs.microsoft.com/en-us/dax/productx-function-dax
• STDEVX.P: https://docs.microsoft.com/en-us/dax/stdevx-p-function-dax
• STDEVX.S: https://docs.microsoft.com/en-us/dax/stdevx-s-function-dax
• SUMX: https://docs.microsoft.com/en-us/dax/sumx-function-dax
• VAR.P: https://docs.microsoft.com/en-us/dax/varx-p-function-dax
• VAR.S: https://docs.microsoft.com/en-us/dax/varx-s-function-dax
• Optimizing nested iterators in DAX: https://www.sqlbi.com/articles/optimizing-nested-iterators-in-dax/

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.

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))

2. Create a column in that table using the following formula:

MonthNo = [Value].[MonthNo]

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
 )

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 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
 )

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

• IF: https://docs.microsoft.com/en-us/dax/if-function-dax

• SWITCH: https://docs.microsoft.com/en-us/dax/switch-function-dax

• Making the "Case" for SWITCH(): https://powerpivotpro.com/2012/06/dax-making-the-case-for-switch/

• DAX – the diabolical genius of "SWITCH TRUE": https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

• DAX IF & Switch Statement Performance – Does Order Matter?: https://joyfulcraftsmen.com/blog/dax-if-switch-statement-performance-does-order-matter/