DAX Cookbook

By : Gregory Deckler

DAX Cookbook

By: Gregory Deckler

Overview of this book

DAX provides an extra edge by extracting key information from the data that is already present in your model. Filled with examples of practical, real-world calculations geared toward business metrics and key performance indicators, this cookbook features solutions that you can apply for your own business analysis needs. You'll learn to write various DAX expressions and functions to understand how DAX queries work. The book also covers sections on dates, time, and duration to help you deal with working days, time zones, and shifts. You'll then discover how to manipulate text and numbers to create dynamic titles and ranks, and deal with measure totals. Later, you'll explore common business metrics for finance, customers, employees, and projects. The book will also show you how to implement common industry metrics such as days of supply, mean time between failure, order cycle time and overall equipment effectiveness. In the concluding chapters, you'll learn to apply statistical formulas for covariance, kurtosis, and skewness. Finally, you'll explore advanced DAX patterns for interpolation, inverse aggregators, inverse slicers, and even forecasting with a deseasonalized correlation coefficient. By the end of this book, you'll have the skills you need to use DAX's functionality and flexibility in business intelligence and data analytics.

Preface

Who this book is for

What this book covers

To get the most out of this book

Sections

Get in touch

Thinking in DAX

Technical requirements

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

Free Chapter

Dealing with Dates and Calendars

Technical requirements

Using time intelligence

Creating quarters

Calculating leap years

Determining day and working day numbers in a year

Determining date of the day number of a year

Finding week start and end dates

Finding working days for weeks, months, quarters, and years

Constructing a sequential week number

Computing rolling weeks

Replacing Excel's NETWORKDAYS function

Working with date intervals

Exploiting alternatives to DAX's time intelligence

Tangling with Time and Duration

Technical requirements

Constructing timetables

Computing an hour breakdown

Converting Unix into UTC and back

Adding and subtracting time

Determining network duration

Calculating shifts

Aggregating duration

Transforming milliseconds into duration

Tinkering with time zones

Converting duration into seconds

Creating a last-refreshed timestamp

Transforming Text and Numbers

Technical requirements

Crafting a dynamic slicer title

Creating a greeting

Counting a list of items

Ranking columns and measures

Totaling measures

Converting from Cartesian to polar and back

Computing percentages

Calculating mode for single and multiple columns

Extracting text

Detecting prime numbers

Figuring Financial Rates and Revenues

Technical requirements

Computing gross margin, revenue, and cost

Building a revenue growth rate

Generating an accounts payable turnover ratio

Fashioning the market share and relative market share

Determining compound interest

Working with periodic revenue (reverse YTD)

Comparing budgets and actuals

Crafting currency exchange rates

Assessing days sales outstanding

Computing Customer KPIs

Technical requirements

Crafting a funnel drop-off rate

Finding new and returning customers

Identifying lost and recovered customers

Creating a Net Promoter Score

Analyzing customer churn rate

Calculating the customer lifetime value

Computing the customer acquisition cost

Evaluating Employment Measures

Technical requirements

Calculating employee turnover rate

Computing absenteeism

Evaluating employee engagement

Determining human capital value added

Finding the full-time equivalent

Using Kaplan-Meier survival curves

Processing Project Performance

Technical requirements

Calculating utilization

Projecting planned value

Estimating earned value

Achieving actual cost

Creating project schedule variance

Computing project cost variance

Creating burndown charts

Calculating Common Industry Metrics

Technical requirements

Calculating Days of Supply

Computing the mean time between failures

Constructing a patient cohort (AND slicer)

Determining overall equipment effectiveness

Optimizing On Time In Full

Analyzing order cycle time

Using Uncommon DAX Patterns

Technical requirements

Aggregating multiple columns

Finding in-common and not-in-common things

Crafting linear interpolation

Creating an inverse aggregator

Finding childless nodes

Calculating transitive closure

Computing advanced measure totals

Using measures where you are not allowed to

Evaluating permutations and combinations

Creating a dynamic temporal scale

Emulating loops

Simulating recursion

Solving Statistical and Mathematical Formulas

Technical requirements

Calculating Shannon entropy

Approximating the area under a curve

Using Runge-Kutta

Measuring covariance

Utilizing the Mann-Kendall test

Finding Kendall's Tau

Analyzing kurtosis

Utilizing the Jarque-Bera test

Determining Pearson's coefficient of skewness

Applying the hypergeometric distribution formula

Determining the required sample size

Applying Advanced DAX Patterns

Technical requirements

Using dynamic ABC classification

Creating an inverse slicer

Unpivoting columns in DAX

Transposing tables

Repeating counter with criteria

Using across then down

Using matrix multiplication

Forecasting with a de-seasonalized correlation coefficient

Creating an SVG graphic

Creating an SVG animation

Making things anonymous

Debugging and Optimizing DAX

Technical requirements

Handling errors with ERROR, ISERROR, and IFERROR

Handling errors with other DAX functions

Debugging with variables

Debugging with CONCATENATEX

Debugging with COUNTROWS

Debugging with FIRSTNONBLANK and LASTNONBLANK

Debugging with tables

Debugging context

Dealing with circular dependencies

Optimizing the data model

Optimizing DAX calculations

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

5 star

4 star

3 star

2 star

1 star

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:

Create a table using the following formula:

R06_Table = GENERATESERIES(DATE(2020,1,1),DATE(2022,12,31))

Create a column in that table using the following formula:

Year = [Value].[Year]

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

Month = [Value].[Month]

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:

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

Place each of these measures in a Card visualization on a page.
Add three slicers to this page for the Year, Month, and Weekday columns from the R06_Table table.

How it works...

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

Measure	Value	Explanation
`Days`	1096	This measure counts all of the rows in the table (366 + 365 + 365).
`January Days`	93	This measure only counts days in the table with a month of January (31 + 31 + 31).
`January Wednesday Days`	13	This measure only counts days in January that are Wednesdays (5 + 4 + 4).
`All Days`	1096	This measure always counts all of the rows in the table (366 + 365 + 365).
`Weekday Days`	1096	This measure counts all of the rows in the table unless there is a weekday filter (366 + 365 + 365).
`All Years January Days`	93	This measure counts days in January, ignoring filters for `Year`, but not other filters.
`January Days?`	93	This measure only counts days in January.
`January Days!`	93	This measure only counts days in January.

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

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

There's more...

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

Measure	Value	Explanation
`Days`	366	This measure is being filtered by the Year slicer and 2020 is a leap year.
`January Days`	31	This measure is being filtered by the Year slicer and January has 31 days.
`January Wednesday Days`	5	This measure is being filtered by the Year slicer and January 2020 has five Wednesdays.
`All Days`	1096	The `ALL` function overrides the filter from the Year slicer, so this is a count of all rows in the table.
`Weekday Days`	1096	The `ALLEXCEPT` function overrides the filter from the Year slicer, so this is a count of all rows in the table.
`All Years January Days`	93	The `REMOVEFILTERS` function removes the Year slicer filter from the calculation, so this is the number of days in January for the three years.
`January Days?`	93	This measure only counts days in January and is filtered by the Year slicer.
`January Days!`	93	This measure only counts days in January and is filtered by the Year slicer.

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

Measure	Value	Explanation
Days	29	This measure is being filtered by the Year slicer and the Month slicer and 2020 is a leap year.
January Days	(Blank)	This measure is being filtered by the Year slicer and the Month slicer, but also has a filter within the calculation of January. Since February and January have no intersecting days, the ultimate value is blank (null).
January Wednesday Days	5	This measure is being filtered by the Year slicer and the Month slicer, but also has a filter within the calculation of January and Wednesday. Since February and January have no intersecting days, the ultimate value is blank (null).
All Days	1096	The `ALL` function overrides the filter from the Year and Month slicers, so this is a count of all rows in the table.
Weekday Days	1096	The `ALLEXCEPT` function overrides the filter from the Year and Month slicers, so this is a count of all rows in the table.
All Years January Days	(Blank)	The `REMOVEFILTERS` function removes the Year slicer filter from the calculation, but not the Month slicer filter. Since February and January have no intersecting days, the ultimate value is blank (null).
January Days?	31	Since the Month slicer and internal filters all refer to the same column, the default behavior for the `CALCULATE` function is to override all filters on that column with the innermost filter, which is `January Days`. However, the Year slicer refers to a different column, so the filter on the `Year` column is also enforced.
January Days!	31	`KEEPFILTERS` modifies the behavior of the `CALCULATE` function, such that this is the intersection of all of the filters on the `Month` column, which still only leaves January. However, the Year slicer refers to a different column, so the filter on the `Year` column is also enforced. The Month slicer is effectively ignored. You can test this by switching the Month slicer to November, December, or another value.

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

Measure	Value	Explanation
Days	29	This measure is being filtered by the Year slicer, Month slicer, and Weekday slicer, and there are only four Fridays in January 2020.
January Days	(Blank)	This measure is being filtered by the Year slicer and the Month slicer, but also has a filter within the calculation of January. Since February and January have no intersecting days, the ultimate value is blank (null).
January Wednesday Days	5	This measure is being filtered by the Year slicer, Month slicer, and Weekday slicer, but also has a filter within the calculation of January and Wednesday. Since February and January have no intersecting days, the ultimate value is blank (null). This measure would also be blank if you selected January in the Month slicer instead of February since there are no Fridays that are also Wednesdays!
All Days	1096	The `ALL` function overrides the filter from the Year, Month, and Weekday slicers, so this is a count of all rows in the table.
Weekday Days	1096	The `ALLEXCEPT` function overrides the filter from the Year and Month slicers, but not the Weekday slicer, so this is a count of all Fridays for all years and months in the table.
All Years January Days	(Blank)	The `REMOVEFILTERS` function removes the Year slicer filter from the calculation, but not the Month slicer filter. Since February and January have no intersecting days, the ultimate value is blank (null).
January Days?	5	Since the Month slicer and internal filters all refer to the same column, the default behavior for the `CALCULATE` function is to override all filters on that column with the innermost filter. However, the Year and Weekday slicers refer to different columns, so the filters on the `Year` and `Weekday` columns are also enforced.
January Days!	5	`KEEPFILTERS` modifies the behavior of the `CALCULATE` function such that this is the intersection of all of the filters on the `Month` column, which still only leaves January. However, the Year and Weekday slicers refer to different columns, so the filters on the `Year` and `Weekday` columns are also enforced.

DAX Cookbook

By : Gregory Deckler

DAX Cookbook

By: Gregory Deckler

Overview of this book

Related Content you might be interested in

Current Title:

DAX Cookbook

Hands-On Business Intelligence with DAX

Extreme DAX

Learn Power BI