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
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.
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.
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 😊
Have fun reading!