Book Image

DAX Cookbook

By : Gregory Deckler

Book Image

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

Thinking in DAX

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

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

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

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

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

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

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

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

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

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

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

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

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

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

Creating quarters

You may consider it somewhat silly to have a recipe for determining the quarter of a date since quarter is included in the default hierarchy for a date. However, many organizations do not adhere to a standard calendar quarter. For example, Microsoft's fiscal calendar runs from June to May. Other organizations start and stop their quarters on specific dates of the year. This quarter calculation can be used with standard quarterly calendars as well as non-standard quarterly calendars. It is easily modified to fit just about any custom quarterly schedule you can imagine. The quarter table that we will create in this recipe has the following quarters:

Q1: August 15 – November 14
Q2: November 15 – February 14
Q3: February 15 – May 14
Q4: May 15 – August 14

...