The third pillar in Power BI development is DAX calculations. This tutorial contains a thorough DAX guide for beginners covering the basics on formatting and editing. Frequently used DAX functions are also included in the discussion.
DAX Editor Guide
Before anything else, here are some DAX Editor tips you need to know:
Use Ctrl + mouse scroll wheel to increase or decrease font size in the DAX Editor window.
Use Ctrl + Shift + L to update all instances of a name at once.
Add the “@” prefix to virtual tables or temporary column names in DAX.
DAX Guide For Formatting
Your DAX code needs to be organized and easily understandable to users.
You’re not only writing code for yourself today, but also for yourself in the future.
It’s important to develop DAX codes that are easily readable and understandable by yourself and by others both now and at a later date.
Don’t worry if editing and organizing might end up ruining your code. Computers are smart; as long as the syntax is correct, they’ll be able to interpret and execute the codes you’ve written.
A great quote by Marco Russo goes:
If it’s not formatted, it’s not DAX.
There are a number of ways to format DAX. The recommended option is using the DAX Clean Up Tool available on the Enterprise DNA Analyst Hub.
You can access this tool using this link. Another way is to access the Enterprise DNA website and then selecting Resources. At the bottom of the page, you’ll find the DAX Clean Up Tool.
To use this tool, copy your code from the DAX Editor in your Power BI file. Paste it into the DAX Clean Up Tool and then click Format.
Once the cleanup is done, you can then paste the formatted code back to your Power BI file.
You can also edit the code within the tool.
Commenting In A DAX Code
When dealing with complex DAX codes, it’s a good practice to add comments. You will greatly benefit from a pseudocode explanation. But there are exceptions.
Commenting is a very personal activity. There may be varied opinions on the right level of comments from developers and users. So again, when creating codes and comments, it’s important to take into consideration how they’ll be accepted and understood by users or developers in future instances. Doing so will greatly benefit you and the business in the long run.
DAX Guide For Variables
Use verbose variable names. The key is clarity. It’s important to create names that are clear and easily understandable.
Again, computers are smart and can easily figure out whatever’s being written. So remember that you’re not writing for the computer or for Power BI; you’re writing for yourself and whoever will be using and maintaining your code in the following months.
When using variables in your DAX code, it’s a best practice to use a prefix before a variable name to help ensure that you’ll choose the correct variable in subsequent lines. The underscore ( _ ) is a recommended prefix to use. This also helps reduce the list presented in the IntelliSense feature in Power BI.
Here’s an example:
You can see that when underscore is typed, only two choices are presented. The number of choices are significantly reduced which improves efficiency when coding.
Another best practice is to use the RETURN Result construct.
This makes it easier to incrementally develop and debug DAX codes.
DAX Calculations Qualification
When using columns in DAX expressions, you need to be specific because it’s possible to have the same column name in multiple tables.
For example, a Customer Key column in both the Customers and the Sales tables could happen.
Although they often mean the same thing and contain the same data, that won’t be the case all of the time. To get the desired results, you should always qualify column names.
Additionally, you need to ensure that measures are not tied to a table. They can be moved from one table to another, so it’s important to make sure that measure names are not qualified.
Important DAX Functions To Know
1. CALCULATE
CALCULATE is one of the most important DAX functions as it enables you to change the context of a calculation.
However, this function often causes confusion not because it’s difficult to use but rather because of its name. Even if it’s called CALCULATE, it’s not actually performing a calculation. Rather, it’s changing the context of a calculation.
For example, in this Sales LY measure, the CALCULATE function is used to change the context of the calculation of Total Sales to one year before the current evaluation context.
2. DATEADD
There are many time intelligence functions available in DAX. Usual DAX codes will most likely use the functions SAMEPERIODLASTYEAR, PARRALELPERIOD, and PREVIOUSYEAR among many others. But DATEADD is one of the most versatile DAX time intelligence functions. And this function should be your first choice.
With DATEADD, the same syntax can be used for multiple calculations, either backwards or forward in time, and for YEAR, QUARTER, MONTH, or DAY. This makes it very easy to create multiple time intelligence measures using copy-and-paste with minor editing.
3. DIVIDE
Division in Power BI can be done in many ways. The most basic way is to use simple arithmetic division.
However, division by zero cases usually require elaborate tests to be done before proceeding. Fortunately, there’s a DAX function that handles this for you.
The DIVIDE function automatically handles division by zero cases and also allows you to add an alternate result. Because this is simpler to arithmetic division in Power BI, the code is more readable.
4. SWITCH TRUE
Another DAX code is the SWITCH TRUE logic.
When you have multiple conditions to evaluate, this will lead you to create multiple nested IF statements which are often hard to read and trace through.
The SWITCH TRUE statement gives flexibility to modify code as a later date and to adjust conditions to new ones. It’s also more compact making it easier to read.
DAX Guide To Virtual Tables
When constructing a virtual table in your DAX code, use the SUMMARIZE function to group columns into a base table. Then, surround it with the ADDCOLUMNS construct to add columns.
To reiterate, use the “@” prefix on columns using the ADDCOLUMNS function to prevent ambiguity and ensure that the chosen virtual table column is correct.
Also, when dealing with virtual tables, it’s recommended to use DAX Studio or the Tabular Editor to assist you.
There can be many relationships between a pair of tables in Power BI. The DAX code will use the active relationship by default. But it can be modified to use a particular inactive relationship through the use of the USERELATIONSHIP command.
For example, there may be many relationships between the Date columns of the Dates table and the Sales table. Sales [Order Date] is the active relationship while the Sales [Invoice Date] is the inactive relationship. When you want to use the Sales [Order Date] in a calculation, no extra effort is required. However, if you want to use the Sales [Invoice Date] in a calculation, you need to use it along with the USERELATIONSHIP command.
In the model view in Power BI, hovering over the solid line shows the active relationship. Whereas the two dotted lines show the inactive relationship between each element in the two tables.
Going over the sample report, it can also be seen that there’s a difference between the Sales by Order Date column, Sales by Invoice Date column, and Sales by Ship Date column.
Measures Vs Calculated Columns
It’s a place of comfort for those coming from Excel backgrounds to use Calculated Columns as it allows you to see the data. But the best practice in DAX and Power BI is to use Measures as much as possible.
There are certain cases where calculated columns are used. They are often developed as a starting point to help with the visualization of a calculation. But then, you need to subsequently replace them with measures once you have the calculation fully visualized in your head. Moreover, calculated columns can be used in slicers in contrast to measures.
A measure doesn’t increase the physical size of your data model as it’s calculated; it only does when used in a visual. A calculated column, however, does increase the physical size of your data model because it is calculated and physically stored each time the file is refreshed.
DAX Guide To Measure Branching
One of the most valuable techniques in Power BI is measure branching.
A great way to modularize your code is to start with simple or base measures that perform simple calculations, and then combine them as necessary to perform more complex calculations. Put base and interim measures in tables to see the calculations as they’re being performed.
There are different schools of thought as to where specific measures should reside, and there are pros and cons to all methods. But it’s more advantageous to use a dedicated measure group in Power BI development. If all the measures are located in the same place at the top of the Fields pane, this makes it easier to trace the logic in your DAX calculations.
Context In DAX Calculations
One of the main strengths in Power BI is context. And it’s also one of the most difficult concepts to grasp for developers.
Context is the environment in which a calculation is performed.
Two contexts are often presented: row context and filter context. But in fact, there’s a third one: the evaluation context.
And this brings you back to the CALCULATE command, which again, allows the user to change the context of a calculation. There are two main classes of functions in DAX and Power BI: aggregation functions and iteration functions.
Aggregation functions are those that aggregate the values of a single column. Examples of aggregation functions include MAX, MIN, and AVERAGE. While most aggregation functions work only on numeric or date values, MAX and MIN also work on text values.
Iteration functions are those that compute an expression for each row of a table. They’re also known as X functions. Examples include SUMX, MAXX, MINX, and AVERAGEX. They have two parameters: the table to iterate over and the expression that performs the calculation. This can use multiple columns from the base table or from other related tables.
Here’s a brief example of how the aggregation and iteration functions are used differently and yet provide the same result:
***** Related Links *****
Power BI DAX Measures For Events In Progress
Row Context And Filter Context In A Power BI DAX Code
Power BI Development Techniques Using DAX Patterns | Enterprise DNA
Conclusion
This DAX guide is aimed at assisting new Power BI users who are still exploring and learning. This is also very useful as a one-stop checklist for experienced developers who need a quick refresher course. This tutorial is a great building block for advanced report development in Power BI.
Enterprise DNA has various resources on DAX tutorials from the basics to advanced coding. Make sure to take a look at them to be more well-versed with the DAX programming language.
Greg
