The Fun and Pain of DAX

I spend a lot of time working in Power BI, and thus I frequently use many of the languages found in the application. This includes DAX (short for Data Analysis Expressions), which first debuted in tabular database models like SQL Server Analysis Services. While I connect to Analysis Services models all the time (especially through DAX queries to import the data into Power BI), the closest I’ve come to building an Analysis Services model is through creating an XMLA endpoint in the Power BI service.

Power BI is a tool that bridges the gap between the backend (where IT sets up and maintains databases for example) and the front end (where business users often make decisions with this data). The first time I encountered DAX was just after Power BI debuted several years ago, and not long after I started working with Power BI myself. At that point, I knew Excel very well, and I'd dabbled a bit in programming languages, like the ones I learned in school (Java and MATLAB of all languages), but nothing too intensive at that point. Even after becoming very well-versed in the languages of data science (R, Python, and SQL), DAX still sometimes seems a bit strange because it works so differently. And DAX isn't a programming language so to speak, but it's definitely a language that requires quite a bit of time to become proficient at!

I found along the way that once I got my footing with a few key DAX concepts, I was well on my way to becoming effective at using DAX formulas to get what I wanted from my Power BI models. In this newsletter, we’ll cover two of the concepts I’ve found most helpful in my own work on a high level.

Navigating Filters

One of the first big hurdles I jumped over in my learning path for learning DAX was figuring out how the filters worked in calculating DAX measures. On a high level, here's how get these measures to work properly.

1. Filter the data tables for the rows necessary for the calculation.
2. Perform the aggregated calculation on these filtered rows for the selected field with the desired arithmetic operators or DAX functions.

Check out a more complex example of how to do these DAX calculations in a recent Power BI Weekly below!

This is an oversimplification in many ways as getting the filters to do exactly what the DAX measure calculation requires can often take a lot of work, and even some trial and error along the way too. One of my own best practices for creating DAX measures is to first create a table visual in Power BI as a backbone to test the calculation results along the way.

There are numerous different DAX filter functions we can use in creating DAX measures. Some of the most impactful in my own work are time intelligence DAX filter functions, which I categorize into two high-level filter buckets of operation types.

1. They can move the date filter to a completely new date for evaluating the DAX measure.
2. Alternatively, they can expand the filters over which we’re evaluating the DAX measure to a wider range of dates than the initial pivot coordinates.

There are also DAX filter functions like PREVIOUSDAY or NEXTDAY that both move the date and expand the date range for this new date in the calculation. Either way, these time intelligence filters alter dates for the measure calculations much as we see in a ruler (see the cover image as well)!

Joining Model Tables

Another DAX function that’s immensely helpful for navigating filters in Power BI – except this time explicitly between tables – is USERELATIONSHIP, which I cover in my most recent Power BI Weekly video.

Navigating the joins between tables is very similar to what we see in Analysis Services database models, which was the predecessor to the modeling backend for Power BI. While connecting tables together is much like joins in SQL queries or even lookup formulas in Excel, how these tables fit together is much different in the sense that we want to avoid flattening them.

What I mean by unflattening is avoiding the tendency to create a single data table to do calculations. Instead, we want to create dimensions (for the keys) and fact tables (for the data) and join them together to build the model. One example of a dimension key that’s helpful in almost every Power BI model is a date dimension table. Creating a table exclusively for dates enables us to add key fields for the dates to the model like weekdays, or the week number of the year, quarter, or month.

Below is an example of what the date dimension table looks like in relation to other tables like the fact (data) table. This example, both in the graphic and in the actual Power BI Weekly video, displays data within the realm of marine traffic (which is also the umbrella for supply chain management). This is a great example of how impactful the USERELATIONSHIP function is because there’s both an arrival date and a departure date for each docking that both use the date dimension table in the model.

Coming Up!

I just finished filming the latest quarter of Power BI Weekly videos to come in the first three months of 2023. Here’s a sneak peek of the topics along with some of the other topics to come this quarter, as well as the videos already published in the LinkedIn Learning library for this course. Check out previous versions of the newsletter for more examples and background information on these published videos!

Next week, I'm also excited to share that I'll be doing a newsletter edition focused on one of my favorite parts of data science: writing Python code (and then using it in Power BI)!

-HW