DAX Series — Learn the core of Power BI
20-part blog series about figuring out the DAX language
(You can also find the article on my website together with other articles and more interesting things)
(As I am switching towards more focus on Tableau and Alteryx in my job life at the moment, I will put this series on halt for a while. I will take it up later when Power BI will be more of my daily work again.)
What is this series about and who is it for?
During the next-coming months I will post at least two articles every week for a new DAX-series that I will make. One article will come out around the middle of the week and the second one by the end of the week
The purpose of this series is two folded:
The first purpose is to introduce the DAX language to people that are relative or completely new to DAX, but have a big urge to learn the language. It will therefore cover things that would have been a great help for me when I first began my DAX journey. It will also include a lot of different resources to make it possible for you to learn by your own.
The second purpose is to also cover more advanced DAX topics for people that have already worked with DAX. These chapters might be a high level of DAX for beginners, but one of the best ways to learn is to aim a bit higher than your current level. I hope this also will give inspirations about topics to research more by your own.
If the posts are well received will I continue to make different series about Power Query, Data Modelling and Report Design later in the year as well.
How will the posts be structured?
The series will both be posted as articles on my webpage, https://eliasnordlinder.com/ and as free articles on my Medium page.
In the end of each part of the series I will post two downloadable Power BI Models. The first one will be an “Empty Model” before I have created the measures/calculated columns/dynamic tables etc. The second one will be with everything included, as well as all the resources that I took help from to make the blog post.
I prefer to use this concept as it will give the possibility for you to go through the blog post yourself and try out the concepts from a blank perspective. It will be especially useful when the concepts are becoming more difficult.
Short summary of concepts
Here are some information of the concepts that I will write about in the DAX series. The chapters can change name or shift place after time, but I will try to post them in chronological order.
The skill-level of the concepts will partly follow the S-shaped learning curve that I will describe in the first chapter. Part 1–4 will be mostly about the parts until the steep incline. Part 5–18 will be a mix, but mostly about parts from the incline and afterwards.
1. What is the DAX Language?
In the first part of the series there will be a discussion of what DAX really is. This will be more of a high level discussion to get an introduction to the language.
It will touch on different concepts such as Measures/Calculated Columns/Calculated Tables, learning curve for learning DAX, very brief overview of Table Functions, DAX Formulas vs DAX Querys and similar.
2. Resources/Learning techniques while learning DAX
The second post will first discuss a number of different resources that for me have been invaluable while learning DAX. Besides this, different techniques and tips for learning DAX will also be included. This will be both from my subjective opinion and from other prominent DAX teachers.
I think this blog post is important to have this post already as chapter two, so that you can start to learn the theory behind DAX as early as possible.
3. Measures vs Calculated Columns in DAX
This is a very important concept to understand. I will touch on it briefly in the first article as well,“What is the DAX language?”, but will discuss it to a deeper extent in this article.
If you are coming from DAX from Excel, like me and many others have done, this will be an especially important post to read through and understand.
Usually people leverages calculated columns much more than measures when they begin to work with DAX, as it is similar to Excel functions. In this part I will describe both the pitfalls of working with Calculated columns and the performance issues that can occur.
4. Iterators and Aggregators
A logical next step after describing Measures and Calculated Columns is to describe what iterators and aggregators are and the difference between the two of them.
As the last part, this is something that people coming from Excel have some background in as well, but there are some quite big differences.
I will also explain with example how common iterators such as SUMX, AVERAGEX, MINX and MAXX works.
5. Table Functions
Table functions are a very important part of DAX. I am leveraging them more and more the more that I use DAX. As the name describes, the result of a table function is a table, instead of a scalar value.
I will mostly describe three main table functions in this post; ALL + ALLSELCTED, FILTER and VALUES.
6. Variables
This will be a good point to explain variables in a more detailed manner. Variables are an instrumental part of DAX when you begin to write more advanced formulas.
I will explain both how they can help with the readability and writability of your code partly by leveraging the DRY principle, as well as increase the performance
7. Evaluation Context (Filter and Row Contexts)
Now it will be the dive to deep into the really advanced concepts of DAX, evaluation context. I decided to explain this in this post first, before the CALCULATE posts. But in the following posts, this will be referenced back to many times.
An evaluation context is the context under which a DAX expression is evaluated.
In short, there are filter context which filters a model and row context which iterates tables. These two concepts will be described in detail in this post.
8.a CALCULATE function part 1
In this part the CALCULATE function will be described in more detail. It is a very complex function and therefore need two posts. Common patterns with CALCULATE will be described here.
After this post, you will also understand why CALCULATE will be one of your absolutely most used function.
8.b CALCULATE function part 2
In this part of CALCULATE most of the text will be focused regarding context transition. CALCULATE is the only function that can modify the filter context. This is a very important ability and it needs a whole post to fully understand.
9. Ranking functions (RANKX and TOPN)
RANKX and TOPN are two functions that you will use a lot when working with DAX. They can be used both for creating simple ranking measures that you can use as filters for your visualizations and for more advanced topics.
I will give a short introduction to the two functions here and describe when and where each of them can be used.
10. Time Intelligence functions
Time Intelligence functions are the functions that I use the most in my daily work as a Business Intelligence Consultant. There are a lot of different Time Intelligence functions and many different ways to write them.
This post about Time Intelligence functions will describe common Time Intelligence functions and a part of the theory behind them.
11. Working with Multiple Date Columns
An important part of working with Power BI is when you have to work with multiple Date Columns. In this post I will describe different variants you can apply when you have for example one date column for Order Date, one column for Ship Date and one for Procured Date.
12. Measure Branching
Measure Branching is an important concept to leverage when working with DAX, which is why it will have its own post here. Measure Branching is regarding how you can create new measures while leveraging old measures, which makes it much quicker to make new measures.
13. Dynamic Tables
Dynamic Tables is not really DAX per see, if you do not make them with Calculated Tables, but you use them with the help of DAX formulas which is why they will have an own post here.
With Dynamic tables you can do a lot of cohort analysis, as well as different dynamic filters, which can be made in Calculation groups as well, which will be described in a later post.
14. Working with DAX Studio
When you are getting in to more advanced DAX, DAX Studio will be your friend in a lot of different aspects. In DAX Studio you can do things such as evaluating the performance of your DAX code, evaluating the result of part of your measures, or write and format measures.
In this Blog post I will describe the different parts that I use DAX studio for and some basic introduction to the Tool.
15. Summarize and Addcolumns
These two concepts will also be more and more used the more advanced you go into DAX. Both of these concepts are used to create temporary tables, which is similar to CTE’s in SQL.
They have some different functionalities which I will go into in this chapter, complemented with usual patterns and how you can leverage DAX studio to work with them.
16. Advanced DAX Formulas
In this post some advanced DAX Formulas will be analyzed and explained that can be of great use in your work.
There are formulas such as calculating the most common weekday that your customers are buying products at, or the day each customer bought its first product.
17. Working with Tabular Editor
Another important External Tool when working with DAX is Tabular Editor. In this blog post I will explain different ways you can use Tabular Editor for, as well as giving some resources to how.
Tabular Editor 2 will be explained here, but the new Tabular Editor 3 will be discussed briefly as well.
18. Calculation Groups
If you have a lot of different measures, Calculation Groups will help you both create these and organize them in an optimal way. Besides this can Calculation Groups also be used as slicers instead of creating Dynamic Tables.
19. Parent-Child Hierarchies
Parent-Child Hierarchies is a concept to for example analyze how employees are related to managers, and also an important part of Row-Level Security (RLS).
I will also show some nice visualizations that you can do with the help of Parent-Child Hierarchies.
20. Row-Level Security (RLS)
Row-Level Security is the final concept that will be to be described in this Series. It is not completely in DAX either, but DAX plays a very important part to implement RLS, therefore I will explain it in this post as a final post.
Link to Articles
Below is links to all the articles, the links will be updated all after the articles are posted:
- What is the DAX language?
- Resources/Learning techniques while learning DAX.
- Measures vs Calculated Columns in DAX
- Iterators and Aggregators
- Table Functions
- Variables
- Evaluation Context (Filter and Row Contexts)
8. a) CALCULATE function part 1
8. b) CALCULATE function part 2
9. Ranking Functions
10. Time Intelligence functions
11. Working with multiple date columns
12. Measure Branching
13. Dynamic Tables
14. Working with DAX Studio
15. Summarize and Addcolumns
16. Advanced DAX Concepts
17. Working with Tabular Editor
18. Calculation Groups
19. Parent-Child hierarchies
20. Row-Level Security (RLS)
Power BI Models available on GitHub
You can find the Power BI files for all of the Blog posts below:
https://github.com/EliasNo/DAX-Series
I added an example for this blog post in the folder:
0. DAX Series — Learn the core of Power BI
In the future it will be similar with one “Clean version” and one “Answer Key” like I described before.
Thank you for reading
Thank you very much for reading my blog!
Drop a comment below about what you thought about the content, or if you want to mention how you use DAX in your daily work.
I would be interested to hear more about how other people see these things.
If you enjoy reading my stories, and would like to support me as a writer, consider to sign up to become a Medium member using this link, and I can earn a small commission, with no extra cost for you.
Questions or inquiries
If you have any questions or input please contact me on
LinkedIn: www.linkedin.com/in/elias-nordlinder
Email: Elias.Nordlinder@gmail.com
Webpage: eliasnordlinder.com
Have a great week everyone
//Elias
