Learning DAX: Things to consider as a Beginner
Writing DAX is hard. For a while I couldn’t figure out the DAX I needed to perform a calculation. Most times I just found a solution online, copy and pasted it into my .pbix file, but did not understand how the calculation works or why it works. Honestly, till now, I still don’t grasp it most times but thankfully, I have a better understanding now and can figure out most of my DAX problems with the help resources. In this article I want to share a few things to consider in your DAX learning journey. I don’t claim to be a DAX master, no. I only want to share the things I believe are overlooked by folks just starting out with DAX, based on my learning experience and having helped a few people solve DAX-related problems.
Introduction
You’ve started your data analytics journey and are now learning Power BI. Or you already work as a data analyst and Power BI and/or Power Pivot in Excel is a primary tool for your day-to-day. At some point you must have written some DAX yourself. But sometimes you get a new business requirement from your boss or want to perform a fairly complex calculation with DAX but you don’t know what to write. Maybe you’re brave and you attempted the calculation but for the some reason can’t figure out why the calculation isn’t returning the desired result. I know that feeling very well.
When I started to learn Power BI and began writing DAX, there were many instances where I either couldn’t get the DAX I needed to work or I just found a solution online, copied and pasted it into my .pbix file, and did not understand how or why the calculation. Thankfully, that is less now and I can figure out most of the DAX problems with the help resources online when needed. I don’t claim to be a DAX master, no, I’m quite far from that. I only want to share the things I believe are overlooked by many folks who write DAX, based on my learning experience and having helped a few people solve DAX-related problems.
Learning DAX
If you’re unfamiliar with what DAX is, it stands for Data Analysis Expressions. It is the functional language used to author calculations in Power Pivot for Excel, Power BI and Analysis Services. There are a few things that I consider instrumental while learning DAX. I’ve summarized these into 6 key but broad topics:
- Understanding DAX Theory
- The Behavior of DAX functions
- DAX Patterns
- Data Modeling
- Watching others and seeing good examples
- Practice and helping others
The Building Blocks
Understanding DAX Theory
DAX is built on a small set of foundational concepts that are very important in explaining how calculations are executed. The most important one is Evaluation Context. The evaluation context is simply “in what manner should this calculation be done”. Or “what subset of the data should be used for the calculation.” It includes the Filter Context, Row Context, and Context Transition.
Without going into details, the filter context is essentially the “data environment” in which the calculation is evaluated. I like to describe it as the subset of the data model that is used for a calculation. The filter context is present mostly when you are doing some form of aggregation. The Row Context, on the other hand, is present in columns or situations where a calculation must be iterated row by row to get its result. This can be in calculated columns or with iterator functions. And Context transition is when the existing Row Context is transformed into a Filter Context.
Understanding the evaluation context is fundamental in understanding how a calculation is computed and why it returned a certain result. If you don’t understand Evaluation Contexts, it’s hard to understand DAX.
The Behavior of DAX functions
The DAX language has over 250 functions and it is a really big task to try to learn every function. What I mean by the behavior of DAX functions is “what does the function do”, “how does the function interact with the evaluation context” and “what is the output of the function”.
First, what does the function do? A sum, average, ranking, time intelligence? This helps you identify what function is best suited for certain kinds of calculations. Next, interaction with the evaluation context. On its own, each function behaves differently depending on the context it is evaluated in. A SUM added as a calculated column doesn’t recognize the Row Context. Likewise, a multiplication of two columns with no Row Context returns an error except with an iterator function. Lastly, what is the output of the function? A table or scalar value? This might seem trivial but it’s important. Have you ever written a TOPN formula and expected DAX to return the top N value only to get an error that the function returns a table? That was one of my early frustrations. With no knowledge of how the DAX functions behave, DAX looks like magic.
If you want to know the mostly used functions to prioritize in your learning, then read this LinkedIn post by Brian Julius.
DAX Patterns
DAX Patterns are approaches to common data scenarios or business requirements. This particularly speeds up your time to get a DAX solution to the problem you have at hand. However, without a good grasp of the conceptual side, the DAX patterns can become useless to you. When you have a pattern very close to your business requirement but don’t understand how DAX works, how do you modify and adapt that solution? Personally, DAX patterns are a way for me to understand how DAX experts think about a solution. If I understand the fundamentals of DAX, I can pick a DAX pattern, attempt to understand the thought process behind it and adapt it with a good chance of success. This is only possible when you know the DAX theory.
Data Modeling
This is probably the most subtle problem especially if do not bother about the technical details of BI. Sometimes the complexity of your DAX solution is largely because of a wrong Data Model. A data model is basically what the structure of the data is and how the tables are organized via relationships. The Data Model directly impacts how your DAX measure is going to be written and what results to expect. Poor knowledge of data modeling best practices can sometimes make a mountain of a molehill of a DAX problem.
Quick story
Sometime in 2021 when I had just started to learn DAX properly, someone reached out on LinkedIn requesting help with some DAX code to calculate inventory balances. We had a call where he shared the data and described the problem. There, we began to think of how to solve it and we were approaching a solution, though a slightly complex and long measure for a calculation that seemed to be simple. After the call, I began to research and found a simple DAX Pattern for calculating Balances and tried to adapt it to the data we had. It didn’t work because the model we had was wrong. With some research, I learned about types of fact tables and found out the reason why the code we had come up with was unnecessarily complex was that we had put two types of fact tables (Transaction & Snapshot) into one table, and that made the formula complex. Data Modeling is foundational to a lot in Power BI and Business Intelligence. It’s a good investment to spend some time learning it.
Watching others and seeing good examples
It is really hard to think about how to write a DAX formula, especially when you are just starting to get the hang of things. Trust me, I know. I’ve had sleepless nights because of it. Watching other people solve DAX problems or write DAX code was really helpful to me in solidifying those theoretical concepts and becoming more familiar with them. There are tons of YouTube videos where Power BI professionals share a solution to a problem and how they approached it. A personal favorite is the ‘Unplugged’ series by SQLBI. These videos feel like I am in a class with Alberto & Marco, and I get to tap into their minds on how they write DAX. As often as you can find good examples of using DAX to solve problems and learn from how they are developed.
Practice and helping others
As with learning any skill, practice is super important and it is no exception with learning DAX. You really have to practice the fundamental concepts till it sticks or at least till you are a bit more comfortable. One of the best ways to practice is to help others solve their DAX problems. This can be through forums, user groups, communities, or peers with whom you are learning together. Any way you can dive into formulas that would keep your mind running for days, do them. These help you build an arsenal of approaches to thinking through and solving problems with DAX, and translating business requirements in English to code in DAX. Do this frequently, and you would almost begin to create the algorithm of a solution in your head before writing code.
Final Thoughts
As the popular saying goes, DAX is simple but not easy. It’s simple because it is built on a small set of unique concepts but it is difficult to learn because it takes time before it all makes some sense. I remember the first time it all started to make sense to me. I had been reading the first few chapters of The Definitive Guide to DAX and was finding it hard to understand how the filter context is modified with CALCULATE. One morning, I woke up and continued reading the book and soon that click happened; “Filters are tables, everything is a table”. That was my missing piece to the puzzle of DAX foundations. It took weeks of learning and practice to get it but I was happy when it did.
If you are looking to level up your DAX knowledge, here are some resources I would recommend:
1. Introducing DAX Video Course
2. Free Advanced Data Analysis & BI Course at YouTube (MSPTDA)
3. Ultimate Beginners Guide to DAX (YouTube)
4. DAX for Humans
5. The Definitive Guide to DAX
6. Atomic DAX Challenge
Happy Learning.
Let’s stay connected.
Don’t forget to subscribe to
and join our Power BI community
