This year I have gotten back to basics with Power BI. After working a day job that was largely focused on SSRS, on a basic on-premises Power BI setup, and on gathering proper requirements for Power BI solutions, I have returned to work in a more “traditional” Power BI environment utilizing the Power BI web service. This change along with giving a Power BI tips and tricks talk at the Nashville Modern Excel and Power BI User Group has led me to reflect on my own Power BI journey, and more specifically what tips I have received or have given away over the years.
The number one tip I can offer to anyone working with Power BI is to…
Learn DAX!
DAX, short for Data Analysis eXpressions, is really core to all that Power BI has to offer. And while it is entirely possible to be a Power BI user without writing a lick of DAX, that would be like having a high performance sports car and never taking it out of first gear! Or having the top wide receivers in the NFL but only calling run plays! You get the point. Learning DAX will greatly expand your capabilities within Power BI.
Before you start to say to yourself, “Ok, that’s great, but I’m not a programmer,” don’t worry! I wasn’t either when I started, and I’m still not. If you can write Excel formulas, you can write DAX. DAX utilizes functions and operators just like Excel formulas do, and even though the two operate differently under the hood and require different syntax, writing DAX has a similar feel to writing and Excel formula. In this article, we won’t explore all of the similarities and differences between DAX and Excel formulas, but allow me to offer a couple points to show how simple getting started with DAX can be:
- Many of the main aggregation functions use the same terminology, such as SUM, COUNT, MIN, MAX, and AVERAGE.
- Just like Excel formulas, intellisense is available for DAX to expose the syntax necessary to use each function.
Now you know how to shift the sportscar out of first gear, you have pass plays in your playbook. It’s time to put them to use.
Create and Reuse DAX Measures
Most calculations using DAX are in the form of DAX measures. These measures are used to aggregate data or return some sort of value based on the DAX that is written. Many, including myself, will advise you to write a DAX measure for every calculation. “But if I just drop the column of data I want to aggregate in the Values field well, Power BI will do the work for me,” you say. Correct, Power BI can do some simple calculations for you without needing to write any DAX. These calculations are called implicit measures. With implicit measures, you’re limited to simple calculations and the calculations are limited to the scope of the visual where the calculation is being used. However, when you write an explicit measure in DAX, the scope of the measure is broader: it’s reusable in any visual or measure within the model or within any other report(s) connected to that model. In other words, write the measure once and use it as many times as needed. Of the many benefits of writing DAX, I consider this one the greatest.
Let’s expand on this concept a bit. We are producing a sales report with various metrics around sales dollars. The first step is to write a base measure. This base measure serves as a simple calculation for us to base other more complex or more specific calculations on top of. This base measure is a great place to add any business logic, such as specific criteria in the data that indicates a sale. In our example, the base measures is a simple sum of the Sales Amount column:
Sales Amount = SUM( Sales[Sales Amount] )Now, if we want to include a metric that just looks at the sales amount of red products, we can reference the Sales Amount base measure and add filtering criteria that will only calculate the sales amount for red products. The DAX for that would look like this:
Red Products Sales Amount = CALCULATE( [Sales Amount], 'Product'[Color] = "Red" )Note than when referencing a measure in DAX, the measure name gets enclosed in square brackets [ ]. We may also want to know what percentage of our sales were red products:
% Red Product Sales Amount = DIVIDE( [Red Products Sales Amount], [Sales Amount] )The pattern of referencing measures can continue until we satisfy all the metrics requirements. In our example, we reference the Sales Amount base measure anytime we need to calculate the sum of the Sales Amount column. We could also reference another measure that references that Sales Amount base measure if that satisfies our requirement. The point is that we only aggregated the column of data one time, and then reused the calculation to get the other calculations we need. The beauty of this pattern is that if the business logic defining a sale changes or if part of the underlying data structure were to change, we would only need to make a change to the DAX in the base measure. All of the other measure referencing back to that base measure will automatically update.
In general, it’s best practice to break DAX measures into smaller chunks, like examples above. This practice will give you more measures to reference elsewhere, offers more metrics to visualize, and keeps the code easy to read. There are cases where you’ll need to deviate from this practice, but I have not been led astray by going in this direction when possible.
Implement Custom Logic and Business Rules
Writing explicit DAX measures gives you the opportunity to bake in custom logic, specific business rules, and perform complex calculations that implicit measures do not offer. If you have complex reporting that you want to develop in Power BI, you’ll need to write DAX to satisfy those requirements.
DAX is Everywhere in Power BI!
DAX is not just limited to measures though! It is possible to use DAX to add calculated columns to a table, to create a new table in the data model, and can be used everywhere you see the fX icons in Power BI. This is where you can really get creative with DAX, literally. The fX icons allow you to add conditional formatting, custom titles, filters, and more. And if you’d like to go even further, you can use DAX to query Tabular datasets created in Power BI or SSAS.
One Final Reason…
If the preceding reasons have not been enough to convince you to learn DAX, then perhaps this one will be. Writing DAX is fun! There’s nothing quite like getting the DAX just right on a calculation to provide the correct calculation in a performant manner. It’s akin to solving a puzzle. A really nerdy puzzle but the sense of satisfaction is the same.
Resources to Get Started
DAX has been around long enough that there are plenty of great books, YouTube videos, online courses, and blogs available to support you at any level. Here are 2 that I use frequently and recommend to you:
https://dax.guide/ – A full DAX refence from the folks at SQLBI – Marco Russo and Alberto Ferrari, known as the Italians, are the leading experts on DAX and offer great explanations on how and when to use each DAX function or operator
https://learn.microsoft.com/en-us/dax/ – Microsoft’s DAX reference guide
The next Power BI post will cover a few more of my favorite resources for working with DAX. Until then, keep learning!
