Book Image

DAX Cookbook

By : Greg Deckler
Book Image

DAX Cookbook

By: Greg 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.
Table of Contents (15 chapters)

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: