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

What this book covers

Chapter 1, Thinking in DAX, introduces the basic concepts of DAX as a language, including how to think in DAX, where DAX can be used and basic elements such as data model elements, data types, and operators. Also covered are best practices for writing DAX and demonstrating key concepts such as context. Finally, some common DAX calculation patterns are presented with a focus on specific groups of functions.

Chapter 2, Dealing with Dates and Calendars, is all about working with calendars and dates. Many useful DAX calculations will be presented that allow the reader to manipulate date and calendar information to derive data that's useful in a business context. This includes dealing with the built-in DAX time intelligence functions but also how to deal with dates when the standard DAX time intelligence functions do not apply. Finally, special attention is paid to dealing with working days and week calculations as they are not covered in standard time intelligence.

Chapter 3, Tangling with Time and Duration, is all about working with time and duration. Time and duration are challenging subjects in DAX because duration data types are not supported. While there are numerous date functions, there are few, if any, time functions. In addition, DAX time intelligence really does not deal with time but rather with dates. The chapter provides recipes for many different variations in the concept of time that are useful for most if not all businesses.

Chapter 4, Transforming Text and Numbers, focuses on creating general DAX calculations that deal with text and numbers. This includes common patterns such as running and rolling totals for numbers and the concatenation of text. In addition, more uncommon patterns are presented that deal with measure totals, formatting text, and counting the number of items in a list.

Chapter 5, Figuring Financial Rates and Revenues, focuses on business financial metrics and how to use DAX to calculate important financial KPIs. All businesses, even non-profits, have financials that must be tracked and measured. This chapter also presents common KPIs that are important to most if not all businesses. Standard AP/AR KPIs are presented along with more uncommon patterns, such as dealing with currency exchange rates.

Chapter 6, Computing Customer KPIs, focuses on business customer metrics and how to use DAX to calculate important customer KPIs. All businesses have customers, and there are common customer metrics that most businesses find important. This chapter provides patterns for calculating the customer metrics important to most if not all businesses.

Chapter 7, Evaluating Employment Measures, focuses on business employee metrics and how to use DAX to calculate important employee KPIs. All businesses and organizations have employees, and it is important for any organization to have happy, satisfied employees. This chapter provides calculations and patterns for measuring employee health and happiness.

Chapter 8, Processing Project Performance, focuses on business project metrics and how to use DAX to calculate important project KPIs. Almost all businesses undertake projects. It is important to track key project metrics to ensure that expected outcomes are being met. This chapter presents numerous KPIs that help ensure that everyone understands the status of projects within the business.

Chapter 9, Calculating Common Industry Metrics, focuses on business metrics used in industries such as manufacturing, healthcare, and other industry sectors. The chapter also covers how to use DAX to calculate important industry KPIs.

Chapter 10, Using Uncommon DAX Patterns, presents a number of more uncommon DAX patterns that are useful in a wide array of scenarios. These patterns provide the reader with a more in-depth understanding of how to create complex DAX calculations that can solve many different problems. This includes overcoming DAX's inherent limitations regarding looping and recursion, dealing with hierarchies, and even using measures when you are not normally allowed to.

Chapter 11, Solving Statistical and Mathematical Formulas, presents a number of advanced DAX calculations for statistical measures and other mathematical formulas. Many of these calculations are very complex and push the limits of DAX coding, but can be incredibly useful in a wide variety of businesses and situations.

Chapter 12, Applying Advanced DAX Patterns, presents a number of advanced DAX calculations that demonstrate how to create complex and truly stunning DAX calculations. These DAX calculations push the limits of DAX coding to solve vexing problems as well as complex code for displaying graphics.

Chapter 13, Debugging and Optimizing DAX, presents provides guidance around how to handle errors in DAX as well as how to debug DAX calculations and optimize DAX formulas. There are various techniques for debugging DAX calculations. In addition, there are a number of useful techniques for optimizing DAX calculations.