Table Of ContentDAX Cookbook
Over 120 recipes to enhance your business with analytics,
reporting, and business intelligence
Greg Deckler
BIRMINGHAM - MUMBAI
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 Shetty
Acquisition Editor: Reshma Raman
Content Development Editor: Athikho Sapuni Rishana
Senior Editor: David Sugarman
Technical Editor: Manikandan Kurup
Copy Editor: Safis Editing
Project Coordinator: Aishwarya Mohan
Proofreader: Safis Editing
Indexer: Rekha Nair
Production 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
To my son, Rocket, who thinks it's a big deal that I have my own side panel
when he searches my name.
To Charles Sterling, for getting me started with being a Microsoft MVP.
To my friends and family that I ignored because this book took up all my free time.
– Greg Deckler
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
Preface 1
Chapter 1: Thinking in DAX 8
Technical requirements 9
Using DAX in Excel, Power BI, and SQL 9
Getting ready 9
How to do it... 9
Excel 9
Power BI 10
SQL Server 10
See also 10
Writing good DAX 11
Getting ready 11
How to do it... 11
How it works... 13
There's more... 13
See also 14
Using variables 14
Getting ready 14
How to do it... 15
How it works... 16
There's more... 17
See also 18
Confronting context 18
Getting ready 19
How to do it... 19
How it works... 21
There's more... 21
See also 22
Grouping and summarizing 22
Getting ready 22
How to do it... 23
How it works... 24
There's more... 25
See also 26
Filtering and unfiltering 26
Getting ready 27
How to do it... 27
How it works... 28
There's more... 29
Table of Contents
See also 32
Exploiting relationships 32
Getting ready 32
How to do it... 33
How it works... 34
There's more... 34
See also 35
Implementing iterators 35
Getting ready 36
How to do it... 37
How it works... 37
There's more... 38
See also 38
Using conditional logic 39
Getting ready 39
How to do it... 39
How it works... 40
There's more... 41
See also 42
Chapter 2: Dealing with Dates and Calendars 43
Technical requirements 44
Using time intelligence 44
Getting ready 44
How to do it... 45
How it works... 46
There's more... 47
See also 47
Creating quarters 47
Getting ready 48
How to do it... 48
How it works... 50
There's more... 51
See also 51
Calculating leap years 51
Getting ready 52
How to do it... 52
How it works... 52
There's more... 52
See also 53
Determining day and working day numbers in a year 53
Getting ready 54
How to do it... 54
How it works... 55
There's more... 56
[ ii ]
Table of Contents
See also 57
Determining date of the day number of a year 57
Getting ready 57
How to do it... 58
How it works... 58
There's more... 59
See also 59
Finding week start and end dates 59
Getting ready 59
How to do it... 60
How it works... 62
There's more... 63
See also 63
Finding working days for weeks, months, quarters, and years 63
Getting ready 64
How to do it... 64
How it works... 66
There's more... 66
See also 67
Constructing a sequential week number 68
Getting ready 68
How to do it... 68
How it works... 69
There's more... 70
See also 71
Computing rolling weeks 71
Getting ready 72
How to do it... 72
How it works... 73
There's more... 73
See also 74
Replacing Excel's NETWORKDAYS function 75
Getting ready 75
How to do it... 75
How it works... 76
There's more... 76
See also 77
Working with date intervals 78
Getting ready 78
How to do it... 79
How it works... 80
See also 81
Exploiting alternatives to DAX's time intelligence 81
Getting ready 82
How to do it... 82
[ iii ]
