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.

Related Content you might be interested in

Current Title:

Small book image
DAX Cookbook
Gregory Deckler
Mar, 2020 | 552 pages
Small book image
Hands-On Business Intelligence with DAX
Ian Horne
Jan, 2020 | 402 pages
Small book image
Extreme DAX
Michiel Rozema | Henk Vlootman
Jan, 2022 | 470 pages
Small book image
Learn Power BI
Greg Deckler
Sep, 2019 | 362 pages
Table of Contents (15 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Sections
Get in touch
1
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
2
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
3
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
4
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
5
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
6
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
7
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
8
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
9
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
10
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
11
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
12
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
13
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
14
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

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:

Previous Section
End of Section 3
Next Section