DAX Foundation

Much has been said about DAX, and even more will be told, as DAX still evolves and the adoption of tools that are using DAX like Power BI and MSFT Analysis Services Tabular is growing. This series of blog posts are inspired by questions asked on https://community.powerbi.com. These questions are touching the most foundational concepts and are reminding me of my journey into the realm of evaluation context, filter propagation, data lineage, the scope of variables, and the mysterious world of table iterators, just to name some concepts.

Sometimes it seems that these foundational concepts are obscured by copy-pasting supposed to be matching DAX statements and tweaking these statements as long as they will obey and return the expected result. Of course, this is a valid approach, and I’m using copy-pasting by myself for a lot of things, e.g., Python, PowerShell, T-SQL, and also DAX. But (as always), doing just this prevents a deeper understanding of these foundational concepts and their intricate workings.

 

Bruce Lee once said

“I fear not the man who has practiced 10.000 kicks once, but I fear the man who has practiced one kick 10.000 times”

 

I firmly believe that understanding the essence of a technique or concept is essential if we have to walk through uncharted worlds, meaning no tweakable DAX statement can be found. From my personal experience, this will happen, and this will happen rather sooner than later.

I started years ago using DAX, first with Power Pivot in Excel, using  Power Pivot as a rapid prototyping tool developing migration strategies for moving multidimensional Analysis Services models (SSAS MD) to tabular models (SSAS Tabular). Since that time, I have learned a lot from various people and various sources, but there are two outstanding blog posts that I’m still reading whenever I need more than three tries to create the expected result with acceptable performance. Here are the links

I group these blog posts into five categories

  1. Universal truths
  2. Data modeling
  3. Filtering
  4. Variables
  5. Table iterators

I will write my blog posts in no particular order (considering a chronological order, or an order given by the above categories).

I believe that it’s almost impossible to separate certain DAX statements into “basic” and “advanced” as these DAX statements are bound to foundational concepts practically every time. For this reason, I call this series of blog posts, “DAX – Fundamental Musings.” From my personal experience, I think it takes time to fully understand the intricate workings that are in place. For this, I enclose a pbix file that shows why a specific statement returns this result (maybe the unexpected result) and not another one.

universal truths

This blog post sets the stage for all the following posts and focuses on some DAX mechanics that I consider foundational. The accompanying Power BI file is here

 

It’s important to note that foundational must not be confused with easy. Even now, after quite some time writing DAX statements, it happens that I need more than one approach to receive the expected result. This is because I do not pay enough attention to some details and neglect the interaction of the moving parts.

  • Tables and
  • Columns

Measures or calculated columns and, of course, virtual tables used inside measures or calculated columns are interacting with the above entities. Before I try to provide another explanation of the essential evaluation context, I want to make you aware of a couple of things.

 

There is something I call for myself “Universal DAX truths,” remembering these truths helps me a lot 😊

 

This blog provides an introduction into what I call Universal truths, and provides additional explanation to the accompanying Power BI file, that of course is available for download.

 

Have fun reading!