40,81 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Solve real-world business problems by learning how to create common industry key performance indicators and other calculations using DAX within Microsoft products such as Power BI, SQL Server, and Excel.
Key Features
- Learn to write sophisticated DAX queries to solve business intelligence and data analytics challenges
- Handle performance issues and optimization within the data model, DAX calculations and more
- Solve business issues with Microsoft Excel, Power BI, and SQL Server using DAX queries
Book Description
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.
What you will learn
- Understand how to create common calculations for dates, time, and duration
- Create key performance indicators (KPIs) and other business calculations
- Develop general DAX calculations that deal with text and numbers
- Discover new ideas and time-saving techniques for better calculations and models
- Perform advanced DAX calculations for solving statistical measures and other mathematical formulas
- Handle errors in DAX and learn how to debug DAX calculations
- Understand how to optimize your data models
Who this book is for
Business users, BI developers, data analysts, and SQL users who are looking for solutions to the challenges faced while solving analytical operations using DAX techniques and patterns will find this book useful. Basic knowledge of the DAX language and Microsoft services is mandatory.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 613
Veröffentlichungsjahr: 2020
Ähnliche
DAX Cookbook
Copyright © 2020 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
Commissioning Editor: Sunith ShettyAcquisition Editor:Reshma RamanContent Development Editor:Athikho Sapuni RishanaSenior Editor: David SugarmanTechnical Editor: Manikandan KurupCopy Editor: Safis EditingProject Coordinator:Aishwarya MohanProofreader: Safis EditingIndexer:Rekha NairProduction Designer:Aparna Bhagat
First published: March 2020
Production reference: 1180320
Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.
ISBN 978-1-83921-707-4
www.packt.com
Packt.com
Subscribe to our online digital library for full access to over 7,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Fully searchable for easy access to vital information
Copy and paste, print, and bookmark content
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.packt.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.packt.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.
Contributors
About the author
Greg Deckler is Vice President of the Microsoft Practice at Fusion Alliance and has been a professional technology systems consultant for over 25 years. Internationally recognized as an expert in Power BI, Greg Deckler is a Microsoft MVP for Data Platform and a superuser within the Power BI community with over 100,000 messages read, more than 11,000 replies, over 2,300 answers, and more than 75 entries in the Quick Measures Gallery. Greg founded the Columbus Azure ML and Power BI User Group (CAMLPUG) and presents at numerous conferences and events, including SQL Saturday, DogFood, and the Dynamic Communities User Group/Power Platform Summit.
About the reviewers
Bill Anton is an expert in Analysis Services and an experienced data warehouse practitioner. In 2013, he founded Opifex Solutions, a consulting firm with deep expertise in enterprise-scale architecture, design, and performance optimization of analysis services and Power BI solutions. He loves eating and spends most of his free time convincing his beloved wife to adopt more golden retrievers.
Denis Trunin has a master's degree in computer science from Moscow State Technical University, Russia. He has worked with Microsoft Dynamics 365 Finance and Operations (formerly known as Dynamics AX) as a technical consultant for more than 18 years. He is very passionate about Power BI as it has tight integration with the Dynamics 365 product line. He has also been involved in several Power BI implementation projects. Denis lives in Australia with his wife and two kids.
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
Table of Contents
Title Page
Copyright and Credits
DAX Cookbook
Dedication
About Packt
Why subscribe?
Contributors
About the author
About the reviewers
Packt is searching for authors like you
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Download the color images
Conventions used
Sections
Getting ready
How to do it…
How it works…
There's more…
See also
Get in touch
Reviews
Thinking in DAX
Technical requirements
Using DAX in Excel, Power BI, and SQL
Getting ready
How to do it...
Excel
Power BI
SQL Server
See also
Writing good DAX
Getting ready
How to do it...
How it works...
There's more...
See also
Using variables
Getting ready
How to do it...
How it works...
There's more...
See also
Confronting context
Getting ready
How to do it...
How it works...
There's more...
See also
Grouping and summarizing
Getting ready
How to do it...
How it works...
There's more...
See also
Filtering and unfiltering
Getting ready
How to do it...
How it works...
There's more...
See also
Exploiting relationships
Getting ready
How to do it...
How it works...
There's more...
See also
Implementing iterators
Getting ready
How to do it...
How it works...
There's more...
See also
Using conditional logic
Getting ready
How to do it...
How it works...
There's more...
See also
Dealing with Dates and Calendars
Technical requirements
Using time intelligence
Getting ready
How to do it...
How it works...
There's more...
See also
Creating quarters
Getting ready
How to do it...
How it works...
There's more...
See also
Calculating leap years
Getting ready
How to do it...
How it works...
There's more...
See also
Determining day and working day numbers in a year
Getting ready
How to do it...
How it works...
There's more...
See also
Determining date of the day number of a year
Getting ready
How to do it...
How it works...
There's more...
See also
Finding week start and end dates
Getting ready
How to do it...
How it works...
There's more...
See also
Finding working days for weeks, months, quarters, and years
Getting ready
How to do it...
How it works...
There's more...
See also
Constructing a sequential week number
Getting ready
How to do it...
How it works...
There's more...
See also
Computing rolling weeks
Getting ready
How to do it...
How it works...
There's more...
See also
Replacing Excel's NETWORKDAYS function
Getting ready
How to do it...
How it works...
There's more...
See also
Working with date intervals
Getting ready
How to do it...
How it works...
See also
Exploiting alternatives to DAX's time intelligence
Getting ready
How to do it...
How it works...
There's more...
See also
Tangling with Time and Duration
Technical requirements
Constructing timetables
Getting ready
How to do it...
How it works...
There's more...
See also
Computing an hour breakdown
Getting ready
How to do it...
How it works...
There's more...
See also
Converting Unix into UTC and back
Getting ready
How to do it...
How it works...
There's more...
See also
Adding and subtracting time
Getting ready
How to do it...
How it works...
There's more...
See also
Determining network duration
Getting ready
How to do it...
How it works...
See also
Calculating shifts
Getting ready
How to do it...
How it works...
See also
Aggregating duration
Getting ready
How to do it...
How it works...
See also
Transforming milliseconds into duration
Getting ready
How to do it...
How it works...
See also
Tinkering with time zones
Getting ready
How to do it...
How it works...
See also
Converting duration into seconds
Getting ready
How to do it...
How it works...
There's more...
See also
Creating a last-refreshed timestamp
Getting ready
How to do it...
How it works...
See also
Transforming Text and Numbers
Technical requirements
Crafting a dynamic slicer title
Getting ready
How to do it...
How it works...
There's more...
See also
Creating a greeting
Getting ready
How to do it...
How it works...
There's more...
See also
Counting a list of items
Getting ready
How to do it...
How it works...
There's more...
See also
Ranking columns and measures
Getting ready
How to do it...
How it works...
There's more...
See also
Totaling measures
Getting ready
How to do it...
How it works...
There's more...
See also
Converting from Cartesian to polar and back
Getting ready
How to do it...
How it works...
There's more...
See also
Computing percentages
Getting ready
How to do it...
How it works...
See also
Calculating mode for single and multiple columns
Getting ready
How to do it...
How it works...
There's more...
See also
Extracting text
Getting ready
How to do it...
How it works...
There's more...
See also
Detecting prime numbers
Getting ready
How to do it...
How it works...
There's more...
See also
Figuring Financial Rates and Revenues
Technical requirements
Computing gross margin, revenue, and cost
Getting ready
How to do it...
How it works...
See also
Building a revenue growth rate
Getting ready
How to do it...
How it works...
See also
Generating an accounts payable turnover ratio
Getting ready
How to do it...
How it works...
See also
Fashioning the market share and relative market share
Getting ready
How to do it...
How it works...
There's more...
See also
Determining compound interest
Getting ready
How to do it...
How it works...
There's more...
See also
Working with periodic revenue (reverse YTD)
Getting ready
How to do it...
How it works...
There's more...
See also
Comparing budgets and actuals
Getting ready
How to do it...
How it works...
There's more...
See also
Crafting currency exchange rates
Getting ready
How to do it...
How it works...
See also
Assessing days sales outstanding
Getting ready
How to do it...
How it works...
There's more...
See also
Computing Customer KPIs
Technical requirements
Crafting a funnel drop-off rate
Getting ready
How to do it...
How it works...
There's more...
See also
Finding new and returning customers
Getting ready
How to do it...
How it works...
There's more...
See also
Identifying lost and recovered customers
Getting ready
How to do it...
How it works...
See also
Creating a Net Promoter Score
Getting ready
How to do it...
How it works...
See also
Analyzing customer churn rate
Getting ready
How to do it...
How it works...
There's more...
See also
Calculating the customer lifetime value
Getting ready
How to do it...
How it works...
There's more...
See also
Computing the customer acquisition cost
Getting ready
How to do it...
How it works...
There's more...
See also
Evaluating Employment Measures
Technical requirements
Calculating employee turnover rate
Getting ready
How to do it...
How it works...
There's more...
See also
Computing absenteeism
Getting ready
How to do it...
How it works...
There's more...
See also
Evaluating employee engagement
Getting ready
How to do it...
How it works...
There's more...
See also
Determining human capital value added
Getting ready
How to do it...
How it works...
See also
Finding the full-time equivalent
Getting ready
How to do it...
How it works...
See also
Using Kaplan-Meier survival curves
Getting ready
How to do it...
How it works...
See also
Processing Project Performance
Technical requirements
Calculating utilization
Getting ready
How to do it...
How it works...
See also
Projecting planned value
Getting ready
How to do it...
How it works...
There's more...
See also
Estimating earned value
Getting ready
How to do it...
How it works...
There's more...
See also
Achieving actual cost
Getting Ready
How to do it...
How it works...
See also
Creating project schedule variance
Getting ready
How to do it...
How it works...
See also
Computing project cost variance
Getting Ready
How to do it...
How it works...
See also
Creating burndown charts
Getting Ready
How to do it...
How it works...
See also
Calculating Common Industry Metrics
Technical requirements
Calculating Days of Supply
Getting ready
How to do it...
How it works...
There's more...
See also
Computing the mean time between failures
Getting ready
How to do it...
How it works...
There's more...
See also
Constructing a patient cohort (AND slicer)
Getting ready
How to do it...
How it works...
See also
Determining overall equipment effectiveness
Getting ready
How to do it...
How it works...
See also
Optimizing On Time In Full
Getting ready
How to do it...
How it works...
There's more...
See also
Analyzing order cycle time
Getting ready
How to do it...
How it works...
There's more...
See also
Using Uncommon DAX Patterns
Technical requirements
Aggregating multiple columns
Getting ready
How to do it...
How it works...
There's more...
See also
Finding in-common and not-in-common things
Getting ready
How to do it...
How it works...
There's more...
See also
Crafting linear interpolation
Getting ready
How to do it...
How it works...
See also
Creating an inverse aggregator
Getting ready
How to do it...
How it works...
See also
Finding childless nodes
Getting ready
How to do it...
How it works...
There's more...
See also
Calculating transitive closure
Getting ready
How to do it...
How it works...
There's more...
See also
Computing advanced measure totals
Getting ready
How to do it...
How it works...
See also
Using measures where you are not allowed to
Getting ready
How to do it...
How it works...
There's more...
See also
Evaluating permutations and combinations
Getting ready
How to do it...
How it works...
There's more...
See also
Creating a dynamic temporal scale
Getting ready
How to do it...
How it works...
See also
Emulating loops
Getting ready
How to do it...
How it works...
There's more...
See also
Simulating recursion
Getting ready
How to do it...
How it works...
See also
Solving Statistical and Mathematical Formulas
Technical requirements
Calculating Shannon entropy
Getting ready
How to do it...
How it works...
There's more...
See also
Approximating the area under a curve
Getting ready
How to do it...
How it works...
There's more...
See also
Using Runge-Kutta
Getting ready
How to do it...
How it works...
See also
Measuring covariance
Getting ready
How to do it...
How it works...
See also
Utilizing the Mann-Kendall test
Getting ready
How to do it...
How it works...
See also
Finding Kendall's Tau
Getting ready
How to do it...
How it works...
See also
Analyzing kurtosis
Getting ready
How to do it...
How it works...
See also
Utilizing the Jarque-Bera test
Getting ready
How to do it...
How it works...
See also
Determining Pearson's coefficient of skewness
Getting ready
How to do it...
How it works...
There's more...
See also
Applying the hypergeometric distribution formula
Getting ready
How to do it...
How it works...
See also
Determining the required sample size
Getting ready
How to do it...
How it works...
See also
Applying Advanced DAX Patterns
Technical requirements
Using dynamic ABC classification
Getting ready
How to do it...
How it works...
See also
Creating an inverse slicer
Getting ready
How to do it...
How it works...
See also
Unpivoting columns in DAX
Getting ready
How to do it...
How it works...
See also
Transposing tables
Getting ready
How to do it...
How it works...
See also
Repeating counter with criteria
Getting ready
How to do it...
How it works...
There's more...
See also
Using across then down
Getting ready
How to do it...
How it works...
There's more...
See also
Using matrix multiplication
Getting ready
How to do it...
How it works...
See also
Forecasting with a de-seasonalized correlation coefficient
Getting ready
How to do it...
How it works...
There's more...
See also
Creating an SVG graphic
Getting ready
How to do it...
How it works...
See also
Creating an SVG animation
Getting ready
How to do it...
How it works...
There's more...
See also
Making things anonymous
Getting ready
How to do it...
How it works...
See also
Debugging and Optimizing DAX
Technical requirements
Handling errors with ERROR, ISERROR, and IFERROR
Getting ready
How to do it...
How it works...
See also
Handling errors with other DAX functions
Getting ready
How to do it...
How it works...
See also
Debugging with variables
Getting ready
How to do it...
How it works...
See also
Debugging with CONCATENATEX
Getting ready
How to do it...
How it works...
See also
Debugging with COUNTROWS
Getting ready
How to do it...
How it works...
See also
Debugging with FIRSTNONBLANK and LASTNONBLANK
Getting ready
How to do it...
How it works...
See also
Debugging with tables
Getting ready
How to do it...
How it works...
See also
Debugging context
Getting ready
How to do it...
How it works...
There's more...
See also
Dealing with circular dependencies
Getting ready
How to do it...
How it works...
There's more...
See also
Optimizing the data model
Getting ready
How to do it...
How it works...
There's more...
See also
Optimizing DAX calculations
Getting ready
How to do it...
How it works...
There's more...
See also
Other Books You May Enjoy
Leave a review - let other readers know what you think
Preface
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 (KPIs), this cookbook features solutions that you can use for your own business analysis needs.
You'll learn how to write various DAX expressions and functions in order to understand how DAX queries work. This book also contains 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 in order to create dynamic titles and ranks and deal with measure totals. Later, you'll explore common business metrics for finance, customers, employees, and projects. This book will also show you how to implement common industry metrics such as days of supply, mean time between failures, order cycle time, and overall equipment effectiveness. In the concluding chapters, you'll learn how 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.
Who this book is for
Business users, BI developers, data analysts, and SQL users who are looking for solutions to the challenges faced while solving analytical operations using DAX techniques and patterns will find this book useful. Basic knowledge of the DAX language and Microsoft services is mandatory.
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.
To get the most out of this book
You will need Power BI Desktop and some familiarity with basic operations such as how to create tables, measures, and columns. In addition, it is important that you have some basic data modeling abilities for creating relationships between tables. Finally, you should at least have a rudimentary understanding of DAX as well as some familiarity with the Desktop interface for things such as Sort By columns and formatting options for visuals. All code examples were tested on the latest version of the Power BI Desktop available at the time of writing (February 2020). Older versions of the Power BI Desktop may not have all of the DAX functions used in this book.
If you are using the digital version of this book, we advise you to type the code yourself or access the code via the GitHub repository (the link is in the next section). Doing so will help you avoid any potential errors related to copy/pasting of code.
Download the example code files
You can download the example code files for this book from your account at www.packt.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.
You can download the code files by following these steps:
Log in or register at
www.packt.com
.
Select the
Support
tab.
Click on
Code Downloads
.
Enter the name of the book in the
Search
box and follow the onscreen instructions.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR/7-Zip for Windows
Zipeg/iZip/UnRarX for Mac
7-Zip/PeaZip for Linux
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/DAX-Cookbook. In case there's an update to the code, it will be updated on the existing GitHub repository.
We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Download the color images
We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: https://static.packt-cdn.com/downloads/9781839217074_ColorImages.pdf.
Sections
In this book, you will find several headings that appear frequently (Getting ready, How to do it..., How it works..., There's more..., and See also).
To give clear instructions on how to complete a recipe, use these sections as follows:
Getting ready
This section tells you what to expect in the recipe and describes how to set up any software or any preliminary settings required for the recipe.
How to do it…
This section contains the steps required to follow the recipe.
How it works…
This section usually consists of a detailed explanation of what happened in the previous section.
There's more…
This section consists of additional information about the recipe in order to make you more knowledgeable about the recipe.
See also
This section provides helpful links to other useful information for the recipe.
Get in touch
Feedback from our readers is always welcome.
General feedback: If you have questions about any aspect of this book, mention the book title in the subject of your message and email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packt.com.
Thinking in DAX
Many moons ago, when learning to write Perl code, I read a blog article about hashes and Perl. While that blog article has been lost to time and the ever-morphing internet, the crux of the article was that if you weren't thinking in hashes, unordered key-value pairs, then you weren't truly thinking in Perl. The theory here was that hashes were the fundamental, native, internal data structure for Perl, and so it was critical that you understood how hashes worked in order to write fast, efficient Perl code.
While I have moved far beyond Perl code in my career, the lesson of that blog article stuck in my mind as I learned new technologies. I have found it incredibly useful to understand the inner workings of new languages and how those languages think. Thus, this chapter is all about teaching you how to think the way DAX thinks. In other words, teaching you how to understand the base inner workings of DAX so that you can write fast, efficient, reliable, and supportable DAX code.
The recipes included in this chapter are as follows:
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
Technical requirements
The following are required to complete all of the recipes in this chapter:
Microsoft Excel (first recipe)
Power BI Desktop (all recipes)
SQL Server Management Studio (first recipe)
Data files:
https://github.com/PacktPublishing/DAX-Cookbook/tree/master/Chapter01
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:
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.
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.
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:
Open Power BI Desktop.
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:
Open SQL Server Management Studio.
In SQL Server Management Studio, connect to a deployed tabular Analysis Server data model.
Right-click the database in the deployed tabular model and choose
New Query
and then
MDX.
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:
Data Analysis Expressions
(
DAX
) in Power Pivot:
https://support.office.com/en-us/article/Data-Analysis-Expressions-DAX-in-Power-Pivot-BAB3FBE3-2385-485A-980B-5F64D3B0F730
DAX basics in Power BI Desktop:
https://docs.microsoft.com/en-us/power-bi/desktop-quickstart-learn-dax-basics
Getting started with the DAX queries for SQL Server Analysis Services:
https://www.mssqltips.com/sqlservertip/4068/getting-started-with-the-dax-queries-for-sql-server-analysis-services
Writing good DAX
While the term good can be subjective, writing good DAX code is very much like writing code in other programming languages; the code should be readable and easy to understand. In this recipe, we will learn how to properly format DAX as well as how to use comments.
How it works...
The comment block at the top of the function provides useful information regarding the purpose of the measure as well as a description regarding its operation. In addition, this comment block includes information about the author, including contact information and when the calculation was created. This information assists someone else reviewing the code or the author if revisiting the code at a later date. A space and asterisk have been added to lines within the comment block to visually cue the reader that the entire comment section belongs together. In addition to the comment block, inline comments have been used to call out where functions end. This makes it much easier to read the code instead of hunting for beginning and end parentheses.
Each function has been placed on its own, separate line by using the Alt+Enter key combination. In addition, each argument for each function is also on its own line, except for the SUM function, since this only has a single argument. The Tab key has been used to indent the nested functions, clearly denoting the nesting hierarchy. In addition, the Tab key has been used to indent function arguments underneath each function, visually keeping coding elements together.
The full, canonical name of columns has been used in order to remove any ambiguity and improve readability. Someone looking at the code immediately understands what column and table is being referenced in the code. These table names have been prefixed and suffixed with single quotes. While not required for table names without spaces, for consistency, they should always be used.
Spaces inserted after beginning parentheses and before end parentheses, as well as before and after the equals sign, provide visual separation between elements and make things easier to read.
Finally, the creation of the column in the SUMMARIZE statement has been created with the name prefixed by two underscore characters, unlike the original formula, where this column is the same name as a column from the original table. While DAX can generally figure out which column is being referenced, having duplicate names is confusing for the reviewer and can create actual confusion and problems in complex DAX formulas.
There's more...
Inline or single-line comments can also be executed by using--instead of//at the beginning of the comment. You can also useCtrl+/ to automatically comment out or comment in a line using the//style of comment.
Instead of usingAlt+EnterandTab, you can useShift+Enterto move to a new line and indent all at once. In addition, you can useCtrl+]to indent andCtrl+[to outdent instead of usingTabandShift+Tab.
See also
For more details regarding this recipe, refer to the following links:
Rules for DAX formatting:
https://www.sqlbi.com/articles/rules-for-dax-code-formatting/
DAX Formatter:
https://www.daxformatter.com/
DAX formula bar keyboard shortcuts in Power BI Desktop:
https://xxlbi.com/blog/dax-formula-bar-keyboard-shortcuts-in-power-bi-desktop/
The
Using variables
recipe
Using variables
While DAX functions can be nearly infinitely nested, using variables can avoid doing the same calculation multiple times and also improves overall code readability. Using variables can help you break complex calculations down into smaller, more consumable pieces that can be individually verified step by step.
How it works...
The code creates three variables, __summarizedTable, __filteredTable, and __sum. The __summarizedTable variable creates a table in memory using the SUMMARIZE function to return a table summarized by [Column] and by summing the [Value] column. This variable takes on the value of the table as shown:
Column
__Value
1
66
2
187
3
212
The __filteredTable variable uses the FILTER function to filter the table represented by the __summarizedTable variable to just the values 2 and 3 in the [Column] column. This variable takes on the value of the table as shown:
Column
__Value
2
187
3
212
The __sum variable uses the SUMX function to sum the [__Value] column of the two remaining rows in the table, taking on a value of 187 + 212, or 399.
The RETURN statement uses an IF statement. This IF statement checks to see whether the __sum variable is less than 400. If the variable is less than 400, the value stored in the __sumvariable is returned. Otherwise, the value -1 is returned.
Variables can only be referenced within the DAX calculation in which they are created. As shown in both __filteredTable and __sum, variables can refer to previously created variables within the same DAX calculation.
See also
For more details regarding this recipe, refer to the following links:
VAR
:
https://docs.microsoft.com/en-us/dax/var-dax
Using variables in Power BI – a detailed example:
https://blog.enterprisedna.co/2019/09/09/using-variables-in-dax-a-detailed-example/
Using DAX variables in iterators:
https://www.kasperonbi.com/using-dax-variables-in-iterators/
Using variables within DAX:
https://powerbi.tips/2017/05/using-variables-within-dax/
Variables in DAX:
https://www.sqlbi.com/articles/variables-in-dax/
Using variables in DAX:
https://exceleratorbi.com.au/using-variables-dax/
Power BI Quick Measure, Runge Kutta:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Runge-Kutta/m-p/411280
Confronting context
There is perhaps no more important subject to understanding DAX than context. Context is essential to DAX and is also something that is relatively unique to the language. In fact, context is so fundamental to DAX that a DAX calculation cannot return a result without context. Thus, understanding context is crucial to understanding DAX as it is context that provides much of the unbridled power of the DAX language. Conversely, context also contributes significantly to the learning curve for the DAX language.
Official Microsoft documentation cites three types of context:
Row context
Query context
Filter context
Most other sources essentially ignore the concept of query context, and the Microsoft documentation is somewhat vague regarding this concept. The best analysis is that the combination of row and filter creates the final query context for DAX to retrieve the required data from the underlying data model for the requisite calculation. Users essentially only ever explicitly define row and filter context for DAX, and DAX itself implicitly creates query context from the row and filter context. Thus, we will focus on row and filter context in this recipe.
How it works...
With regard to row context, DAX automatically applies row context to any calculated column. Therefore, the three columns created, Year, Month, and Weekday, all have row context applied. This is why there is a single value returned despite the fact that we have no aggregation function applied. Thus, within row context, references to columns such as [Value], when not referenced from within an aggregation function, always return a single value, the value of the referenced column in that row. This is really as complex as row context gets, with the exception that it is possible to create row context outside of tables and calculated columns. To create row context within measures, we can use certain DAX functions such as ADDCOLUMN.
Filter context is somewhat trickier. Filter context is created by the combination of visuals and the fields within those visuals, as well as explicit filters created using the Filters pane in Power BI Desktop or directly within a DAX calculation when using a filters clause. In step 3, the matrix rows and columns define the context for the CountOfDays measure. Thus, for each cell, excluding the Total cells, we get the number of days in each month for each year. This is why the cell intersecting February and 2020 has 29, and 2020 is a leap year. The Total column removes the filter context for the individual columns but not the individual rows, and so we get the total number of days for all three years, 2020, 2021, and 2022, for each month. Conversely, the Total row removes the filter context for the individual rows but not for the individual columns, and so we get the total number of days in each year. Finally, the cell on the right in the bottom row removes the filter context for both the individual rows and individual columns, and so we get the total number of day in all three years. Therefore, the filter context for this cell is effectively no filters or all data referenced by the matrix visualization.
Adding the slicer and selecting an individual weekday adds additional filter context to the matrix since the default in Power BI Desktop is to cross-filter visualizations. Thus, in addition to the filter context of the individual rows and columns in the matrix, the cells also encapsulate the filter context of the slicer, and so we are presented with the number of Saturdays in each month of each year with their corresponding totals in the Totals row and column. Selecting a different weekday from the slicer, or a combination of weekdays, will present their corresponding counts in the matrix visualization.
See also
For more details regarding this recipe, refer to the following links:
DAX overview – context:
https://docs.microsoft.com/en-us/dax/dax-overview#context
The most important DAX concept:
https://powerdax.com/important-dax-concept/
Understanding evaluation contexts in DAX:
https://www.microsoftpressstore.com/articles/article.aspx?p=2449191
Grouping and summarizing
Grouping and summarizing information is a powerful feature of Excel pivot tables and Power BI table and matrix visualizations. However, it is often necessary to group and summarize information in DAX as well. Grouping and summarizing in DAX can be accomplished through the use of two functions, SUMMARIZE and GROUPBY. In this recipe, we will create new tables that summarize information by using the SUMMARIZE and GROUPBY functions.
How it works...
Looking at the SUMMARIZE formula, the first parameter is the table that we want to summarize, and the next two columns are the columns according to which we want to group our data. Note that you can group by one, two, three, or nearly any number of columns.
The next four parameters are name/expression pairs. DAX understands where these pairs start when you stop referring to column names in the table and enter a text value for a parameter denoted by double quotes. These name/expression pairs specify a column name as the first part of the pair and a DAX expression to evaluate as the second portion of the pair. We first create a column called # of Days, and the value to be returned in this column is the count of the rows from our groupings. In other words, we get the number of each weekday in each month as our value. For the second column called First Date, we return the minimum date from our groupings. In other words, we get the first date of each weekday within each month as our value.
Looking at our GROUPBY formula, the first parameter is the table that we want to group. The next two columns are the columns according to which we want to group our data. This works in the same way as the SUMMARIZE function in that you can group by one, two, three, or nearly any number of columns.
Again, with the next four parameters, these are similar to the SUMMARIZE function in that they are name/expression pairs. This works exactly like the SUMMARIZE function except that instead of referring to the original table, you must refer to a special DAX function that can only be used within a GROUPBY function – the CURRENTGROUP function. In other words, you are referring to the current row of the Cartesian product created by your grouping columns.
