My DAX (re)Learning Path
“The illiterate of the future are not those who can’t read or write but those who cannot learn, unlearn, and relearn” — Alvin Toffler
Have you ever walked a “known” road, and all of a sudden, you noticed something extraordinary you didn’t see before? Well, that is what re-learning means for me: walking a known path and noticing things that probably were there before, but you didn’t see, or new things that were not there on your last walk.
Today I will start my DAX re-learning journey, and I’ve chosen SQLBI as my guide through it simply because these guys ROCK! I consider there is a consensus among the Microsoft Business Intelligence community that Alberto and Marco are truly THE DAX guys.
“Incredible analytical power awaits you when you complete your journey learning DAX, so hurry and start learning it today.” — Ferrari, Alberto; Russo, Marco.
I am already a “seasoned” Power BI professional, and I consider that I have a pretty good understanding of DAX. Still, I am very excited about the new things I expect to learn and the “Damn, I should have done that thing differently” moments.
I will follow all the recommended steps from the creators and will share my insights about it. So, as Pink says, let’s get this party started.
In the SQLBI page, there are three recommended learning paths: Power BI, Excel, and Analysis services; I am going for the PBI one, this contains 14 steps that will take me from a Rookie to a “Guru.”
Early on, I realized is that this is not going to be cheap, as all of it, without applying any bulk discount, adds up to over 1 KUSD; it’s ok. Something that I’ve learned in my life is that good education’s ROI is almost unbeatable, unless, of course, you bought bitcoin ten years ago.
Step 1 — Introducing Microsoft Power BI
Don’t be fooled by the age; this five-year-old book that develops the story of David, the budgeting manager of Contoso, and his journey through Power BI might feel a bit outdated for the experienced Power BI professionals, but this book is not aimed at those “old wolves;” this is an introductory lecture to Power BI.
The fact that some of the references, menus, buttons, functions, etc., have changed (skip chapter 4 as content packs were deprecated) does not demise the book’s value; in fact, I consider 90% of the concepts, flows, procedures are still standard to Power BI and very handy to any newbie.
Mindblower 1: feature snapshots
My first surprise came on page 65; while comparing how much the PBI interface for sharing (and everything else) has evolved in time, I clicked on an option that I do not often visit: the content settings; in there I saw this option called “snapshot” which allows you to add an image; after some more clicking I found out that when the content is marked as featured, this image is the one that appears in the homepage. Damn! How many missed opportunities for publicizing content!
Reflexion 1: It’s never that easy
On page 123, David requests access to the SQL DB, and Karin, the DB admin, “gladly grants him access.” Well, let me tell you something from my experience, it’s never that easy.
Getting access to DB can be delicate stuff, and DB Admins are there to manage this. Depending on the complexity of the organization, the data’s sensibility, the DB architecture, and the willingness to support, getting direct access to a data source is easier said than done.
My suggestion is to be friendly and empathetic with the DB Admin and his job responsibilities. Work on your soft skills; they will be handier in getting DB access than any technical knowledge you may have.
Connecting the dots 1: Nested Calculations.
A couple of years ago, I read a great blog post by Chris Web that has a technique of doing nested calculations in Power Query, for which I have found many practical applications (click for blog post).
While going through page 153, David solved a budget-related problem using a different technique in Power Query.
I now realized that both methods could solve any of the problems; this is something that I will confirm in a future blog post.
Finalizing Step 1
I am done with the book. As expected, even if I have read it before and now I am a more experienced PBI dude, there was lots of learning.
I would recommend this book to any person starting with DAX-PBI, the goal of the book is not to be a detailed guide but a showcase of possibilities you will find when you enter this wonderful world.
Step 2— Introducing DAX Video Course
The second stop in my journey to becoming a DAX Guru is a video course, “Introducing DAX.” Regardless of the experience, I often find the basic classes interesting and eye-opening; I believe this is because we learn a lot of times empirically, not paying too much attention to the technique and only focusing on the task. The problem with empirical learning is that many times, we gain vices that halt our maximum performance without even noticing.
Introduction to DAX
After less than 90 seconds of the first lesson started, I was already learning stuff. DAX is a functional language where we define the flow of execution through a set of function calls, and as it is a formatting language, we need to follow formatting rules. If you want to know more about formatting DAX, please see my blog post.
One of the things I enjoyed a lot is the explanations of the engine behind Power BI; for example, Marco masterfully goes through basic concepts that is always better to understand from the beginning; at this moment I am thinking on how many headaches I could have avoided if I had learned this from the beginning:
- What a calculated column (or a measure) means in terms of RAM, CPU, size of the model, and how measures work at aggregated level; this are concept that matters, and it is better to understand when working with big fact tables.
- Why should we favor the creation of measures over the creation of calculated columns.
- The usefulness of variables, which in reality are constant values. You will thank knowing variables as your DAX queries start becoming more complex-
Mindblower 2: Cero is equal to blank but not strictly equal to blank.
When going through the “Calculated Columns” video, I realized Marco used the
ISBLANK()function for fixing a result; why have I never used this function with a simpler syntax?
When digging into this, I got into a big f’n rabbit hole, which I haven’t yet been able to come out of there fully; I did some research which made me pretty freaking confused, and then after some more research, I learned the importance of using the
ISBLANK() function rather than using
[Column] = BLANK(). Basically, for DAX, a blank, a cero (0), an empty string (“”), or a null value are evaluated differently depending on if we use equal (=) or the strictly equal (==) operator.