DAX for Humans E-Book

Greg Deckler

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 705

Veröffentlichungsjahr: 2025

Beschreibung

Although DAX has been around for over a decade, many struggle to master the language primarily because DAX is often taught through the CALCULATE function, which is the most complex and unintuitive function in all of DAX. But what if DAX could be taught without CALCULATE? The result would be an incredibly intuitive and easy way to learn DAX.
DAX for Humans stands the traditional approach to learning DAX on its head, foregoing the traditional, legacy methods of learning DAX for a more modern approach that focuses on core DAX concepts and not any specific function. Even if you know nothing about DAX, from the very first chapter you will learn the essentials of the DAX language, as well as a single pattern to solve the majority of DAX problems. From that point forward, you’ll explore how to work with the basic building blocks of the DAX language and apply what you learn to real-world business scenarios across customers, human resources, projects, finance, operations, and more.
By the end of this book, you’ll be able to apply your DAX skills to simple, complex, and advanced scenarios; understand how to optimize and debug your DAX code; and even know how to efficiently apply artificial intelligence to help you write and debug your DAX code.

Leseprobe

DAX For Humans

The No CALCULATE Guide that Makes DAX Easy

Greg Deckler

DAX For Humans

© 2025 Gregory J. Deckler

All Rights Reserved: No part of this publication may be reproduced, distributed, or transmitted in any form or by any means, including photocopying, recording, or other electronic or mechanical methods, without the prior written permission of the publisher or author, except in the case of brief quotations embodied in critical reviews and certain other noncommercial uses permitted by copyright law. For permission requests, write to the publisher or author at the address below. This work may not be used for commercial artificial intelligence training. NO AI TRAINING.

Limitation of Liability: The information in this book is provided “as is,” with no guarantees of completeness, accuracy, timeliness, or of the results obtained from the use of this information. The author and publisher disclaim any liability, loss, or risk incurred as a consequence, directly or indirectly, of the use and application of any content in this work. The reader is solely responsible for verifying the applicability of the content to their specific situation.

Cover Art: Alexandre P, https://www.fiverr.com/alerrandre

Interior Art Editor: Rocket “Alex” Deckler

Interior Art Credits. These works are edited:

Chapter 1: Frank R. Paul, Amazing Stories, April 1929

Chapter 2: Frank R. Paul, Amazing Stories, June 1926

Chapter 3: Frank R. Paul, Amazing Stories, July 1926

Chapter 4: Frank R. Paul, Amazing Stories, August 1926

Chapter 5: Frank R. Paul, Amazing Stories, August 1927

Chapter 6: Frank R. Paul, Amazing Stories, July 1929

Chapter 7: Frank R. Paul, Amazing Stories, January 1927

Chapter 8: Frank R. Paul, Amazing Stories, May 1926

Chapter 9: Frank R. Paul, Amazing Stories, July 1927

Chapter 10: Frank R. Paul, Amazing Stories, August 1928

Chapter 11: Frank R. Paul, Amazing Stories, March 1929

Chapter 12: Frank R. Paul, Amazing Stories, April 1928

Chapter 13: Frank R. Paul, Amazing Stories, November 1928

Chapter 14: Unknown/Uncredited, Amazing Stories Quarterly, Summer 1929. Interior artwork for “Venus Liberated” credited to Hugh MacKay.

Chapter 15: Frank R. Paul, Amazing Stories, June 1929

Chapter 16: Frank R. Paul, Amazing Stories, March 1927

First published: July 2025

ISBN: 9798290175508

[email protected]

About the Author

Greg Deckler is a prolific writer and respected authority in the field of business intelligence, particularly known for his expertise in Microsoft Power BI and DAX. With over 30 years of experience in technology consulting, Greg currently serves as Vice President at a global technology consulting services firm.

Greg’s literary contributions include several influential books aimed at both beginners and advanced users of Power BI including Learn Power BI 1st, 2nd and 3rd editions, DAX Cookbook, Power BI Cookbook 2nd and 3rd editions, Mastering Power BI 2nd edition, and The Definitive Guide to Power Query (M).

Greg is also an active Power BI community member, having authored more than 7,500 solutions on the Power BI community forums and nearly 200 entries to the community’s Quick Measure Gallery. In addition, Greg often blogs, has founded Power BI user groups, presents at various conferences, and is a seven-time Microsoft MVP for Data Platform.

Beyond writing and community involvement, Greg also runs the YouTube channels Microsoft Hates Greg and DAX For Humans. In addition, Greg is the author of numerous external tools for Power BI including Microsoft Hates Greg’s Quick Measures (MSHGQM).

Through his extensive work, Greg Deckler continues to be a pivotal figure in advancing the use and understanding of Power BI in the business intelligence landscape.

Dedication

This book is dedicated to my wonderful patrons who have supported the creation of this book and to whom I am forever grateful. Listed in order of joining my Patreon page.

Brian Julius

Bryon Smedley

Deron Huskey

John Dages

Aske Laustsen

Tim Osborn

Rafiullah Shaheedullah

Henk-Jan van Well

Henrik Vestergaard

Hamish Maxwell

Jasmin Simader

Diana Ackermann

Aaron McVay

John Howard

Jacco

Alexis Olson

Phillip Lind

Winston

Tamer (Juma)

Richard Burnett

Garvin 5.0

Catherine Palmer

Mark Cunningham

Bip

Jonathan Vandervort

Paul Wyatt

Micah Harner

Sadiq

Jan

Christopher Aragao

Mateusz Mossakowski

Pawel Wrona

Jason Richter

Nelson Mwangi

Henry Partner

Simon

Rafael Ayres

Rich McMullen

Greg Hamper

Chris

Nazmul Islam Jobair

Alexandru Badiu

Fernando Lopez

Julliette Carignan

Matthew Floyd

D-Lloyd Agencies

Kirill Perian

Zachi Shefer

Rasmus Ludvigsen

Darran

Paul

Carlo Romanelli

Abubakar Alvi

Lukasz Wrobel

Mark Brookes

TO

Adam

Antti Rask

Kev

Dean Kuhn

Tony

Susan Bayes

JAJ Snyman

Dennis Priester

DURAND

Inan Bhulyan

Qadim A

Additional Thanks

Special thanks to these additional Patreon followers:

Aleksander

D-Lloyed Agencies

Nazmul Islam Jobair

Nauris Ozols

Lisa Sheehy

kalyan kumar

Maria Tynan

Kamal

mikhail stotskiy

Manuel Barahona Gandia

James Whitehead

Peter

Jedless

Jim Titus

Wale

Achmad Farizky

K

Eric Laforce

Morten Lillevik

gianivo

Stephanie

Eliezer Kanevski

philkhana aparna

Jacobus Snyman

Osama

Khurram Sheikh

David Johnson

John Griffiths

Abraham Karmel

Thomas Garvin

Mohammed

Bartłomiej Jaskólski

Rin Mai

BC

Gaurav Guliani

valapo

kjstvr

Joanna Hryniewicz

Douglas Cory

Patrick JACQUART

MSkovronsky

amitmend

Phillip Poole

Jørgen

Sathish

Burkhard Bräkling

Sarah Krusleski

AJAY KUMAR

Yann

Gaetan Mourmant

Lionel Sheikboudhou

Important Links

GitHub Repository

https://github.com/gdeckler/DAX-For-Humans/tree/main/book

DAX For Humans YouTube Channel

https://www.youtube.com/@daxforhumans

Microsoft Hates Greg YouTube Channel

https://www.youtube.com/@microsofthatesgreg

Table of Contents

DAX For Humans

About the Author

Dedication

Additional Thanks

Foreword

Introducing DAX

Getting Power BI Desktop

Touring Power BI Desktop

Header

Ribbon

Views

Canvas

Fly-out panes

Pages

Footer

Entering and Importing Data

Enter data queries

Import queries

Viewing and Interacting with the Data

Editing Queries

Creating a Visual

Creating a DAX Column

Creating a DAX Measure

Let’s Talk About Context

Thinking in DAX

Filtering

Using X Aggregators

Leveraging Variables

A DAX Pattern to Solve Most Problems

Summary

More Core Concepts

No CALCULATE vs. CALCULATE

Visualizing Your DAX

Looking up Values

All Functions

Grouping Rows

Adding Columns

Logical Functions

Information Functions

Selecting Columns

The IN Operator

Multiple Table Functions

Measure Totals

Summary

Dates and Calendars

Measure Tables

Date Basics

Creating a Calendar

Offsets

Period-to-Date

Year-to-Date

Quarter-to-Date

Month-to-Date

Week-to-Date

Previous Period

Previous Year

Previous Quarter

Previous Month

Previous Week

Previous Period to Date

Previous Year-to-Date

Previous Quarter-to-Date

Previous Month-to-Date

Previous Week-to-Date

Rolling Periods

Leap Years

Julian Days

Previous Row or Occurrence

Summary

Text

Core Text Functions

Extracting Text

Counting Occurrences

Replace From Right

Dynamic Text

Simple Greeting

Conditional Formatting

Text to Table

Preserving Case

Anonymous

Phone Number Verifier

Extracting Text Revisited

Summary

Numbers

Core Number Functions

Rounding

A Better MOD

A Better MEDIAN

Computing the Mode

Ranking

Aggregating Measures

Weighted Averages

Linear Interpolation

Simple Linear Regression

Formatting Numbers

Summary

Time and Duration

Time Basics

Creating Time Tables

Time Addition and Subtraction

Decimal Duration

Duration To Seconds

Text Duration Conversion

Net Work Duration

Hours Breakdown

Shifts

Time Zones

Timestamps

Handling Unix Times

Milliseconds Duration

Summary

Customers

New, Lost, and Returning Customers

Net Promoter Score

Churn Rate

Lifetime Value

Acquisition Cost

Open Tickets

Funnel Drop-off Rate

Better Together

Annual Contract Value

Sales After Event

Summary

Human Resources

Employee Turnover Rate

Absenteeism

Bradford Factor

Employee Satisfaction

Human Capital Value Added

Utilization

Kaplan-Meier estimator

Pay Equality (Gini coefficient)

Summary

Projects

Burndown Chart

Planned and Earned Value

Schedule Variance

Actual Cost

Cost Variance

Overlap

Overworked

Summary

Finance

Gross Margin, Revenue, and Cost

Currency Exchange Rates

Periodic Billing

Reverse Year-To-Date

Comparing Budgets and Actuals

Accounts Payable Turnover Ratio

Modified Dietz Return

Compound Interest

Summary

Operations

On Time In Full

Order Cycle Time

Delivery Dates

Mean Time Between Failure

Overall Equipment Effectiveness

Days of Supply

Order Fulfillment

Summary

Distance and Space

ATAN2

Polar

Distance

Bearing

Eastings and Northings

Near

Transitive Closure

Box Sizes

Summary

Advanced Scenarios

Disconnected Tables

NOT Slicer

Complex Selector

AND Slicer

Custom Matrix Hierarchy

Scalable Vector Graphics (SVG)

Dynamic Granularity Scale

Summary

Complex Patterns

GAMMA

TRIMMEAN

Fuzzy Matching

DAX Index

Streaks

Multi-Column Aggregations

Summary

Optimizing Performance

Why is Optimizing DAX a Difficult Subject?

DAX Optimization Tools

General Example

CALCULATE vs No CALCULATE Act 1

CALCULATE vs No CALCULATE Act 2

Summary

AI, Debugging, and CALCULATE

Using Artificial Intelligence (AI) to Write DAX

Debugging DAX

Debugging with AI

Handling Errors

Debugging Context

Circular Dependencies

Using DAX Query View

EVALUATEANDLOG

Let’s Talk About CALCULATE

What is CALCULATE?

The Trouble with CALCULATE

When to Use CALCULATE?

Summary

Index

Landmarks

Title Page

Cover

Foreword

The fact that you are even holding this book in your hand or reading it on your screen is something of a miracle.

To understand why, let's set the Wayback Machine to July 23, 2020.

I am certain that if you’d conducted a survey on that day asking every single DAX user to name the most important DAX function, the unanimous answer would have been “CALCULATE”.

Well, actually unanimous minus one, but I'm getting a little ahead of myself…

At that time, here’s what the two top DAX books said about CALCULATE:

“CALCULATE( ) is the most important and powerful function in the DAX language. It is important because it is the only function that has the ability to modify the natural filtering behavior of visuals.”

“CALCULATE is the most important, useful, and complex function in DAX, so it deserves a full chapter.”

We all accepted this as if it was handed down to us on stone tablets, and CALCULATE was the cornerstone of every book, video, and course you could use to learn DAX.

Then on July 24, 2020 at 11:13am, Greg Deckler took the slingshot out of his back pocket and hurled a rock straight at this cornerstone, in the form of an article he posted on the Microsoft Community forum entitled “CALCUHATE - Why I Don't Use DAX's CALCULATE Function”. In this article, Greg laid out a very detailed argument why CALCULATE is not only inessential to DAX, but in fact makes it more difficult to write, debug, and maintain.

I wish I could tell you that upon reading that article, the scales immediately fell from my eyes and I recognized it as a better and easier way to write DAX. However, I did not. I’m not sure whether it was an insufficient understanding of the nuances of DAX necessary to fully grasp Greg's arguments, or intellectual laziness, or most likely some combination of both. Regardless, I kept writing DAX as I always had - with CALCULATE in the lead role.

However, that CALCUHATE article continued to nag at me, and two years later I found myself responsible for assembling the speakers for a large conference on DAX and Power BI. In that intervening period, I had gotten to know Greg a bit, bonding over our shared hatred of the DAX time intelligence functions.

He enthusiastically accepted my invitation to speak at the conference, delivering a masterful presentation entitled “DAX Counterculture: Alternative Perspectives on DAX” in which he clearly and systematically walked through his arguments against the use of CALCULATE and DAX time intelligence functions.

Unlike two years prior, I now found myself unable to ignore the incompatibility of the views that “CALCULATE is the most important function in DAX” and “CALCULATE is wholly unnecessary, and actually makes DAX harder”.

As the son of a biochemist who, from the time I was eight years old used to sneak me into the lab on weekends to conduct scientific experiments together, I publicly committed to performing an experiment of my own – for the entire month of September I would attempt not to use a single CALCULATE in any of the DAX I was writing, and would publish my results at the end of the month.

At that time, I was building Power BI reports on a daily basis and honestly expected that somewhere within those 30 days, I would encounter a situation that could not be resolved without the use of CALCULATE.

Much to my surprise, that day never came, and at the end of the 30-days, I found that DAX without CALCULATE was far easier to write, understand, and debug.

However, when I posted these results, many of the comments I received confidently stated that even if that were true, measures constructed without CALCULATE would perform much worse than those written in the accepted style.

So, back to the “lab” where I took reports from highly accomplished Power BI developers, reconstructed their measures in the “No CALCULATE” style, tested the performance of each pair of measures, and found no evidence of a statistical difference in performance between the two approaches.

I also ran these tests on increasingly large data sets, up to 20 million records, to counter the argument that the No CALCULATE approach would break down at large scale, and again found no evidence to support that contention.

Recognizing that CALCULATE is miles from being the most important function in DAX has profound implications for the way we should be teaching DAX. This revelation allows us to put CALCULATE in its proper context – a very advanced function (along the lines of CROSSFILTER or TREATAS) that should be one of the last ones taught, useful for “fine tuning” in very limited and specific use cases.

Now, the idea of teaching DAX beginners by building upon a foundation of CALCULATE makes no more sense to me than teaching a toddler to walk by taking them out to the track and shouting at them to run the 400m hurdles, or teaching a teenager to drive by strapping them into a racecar at the 24 Hours of Le Mans.

However, coming to that revelation presented me with an entirely new problem. By then I had become a very loud public proponent of Greg’s approach to learning and writing DAX. DAX unquestionably remains the largest hurdle to learning Power BI, and many users struggling with it asked me the best way to learn the No CALCULATE approach.

Until this moment, my answer always felt largely unsatisfactory, pointing them to a scattered collection of articles (including CALCUHATE, of course…) videos, and presentations explaining it.

However, with the publication of this book, there now is an integrated and comprehensive resource for learning DAX in an easier and better way. Far from being a dry, theoretical tome it is a plain language treasure trove - packed with highly practical uses across a wide range of domains and applications. I sincerely hope you find it as enlightening and enjoyable as I have.

To me, it also stands as a valuable reminder that the truth of a statement is not always determined by the number of people repeating it. Occasionally, that lone dissenting voice with the insight and courage to tell the rest of us “you’re wrong” ends up being right on target…

Brian Julius,

Power BI expert and instructor