In this tutorial, we’re going to discuss the easiest way to simplify DAX formulas using variables and comments in Power BI.
DAX codes can become complex and hard to comprehend once they get more advanced. If we don’t optimize them, other Power BI users within the organization will have a difficult time understanding the data model.
Using variables and comments will make it easier to create, update, and optimize formulas.
Using DAX Variables In Measures
The first technique in simplifying DAX formulas is by using variables.
When dealing with variables, always remember that they’re only measure specific. They can’t be global variables.
For now, variables can only be referenced within a measure. This is a the current limitation in Power BI, however, changes will happen in the future.
Using DAX Variables To Reference Values
In this example, we’ll be using a specific syntax for the formula.
Type in VAR to indicate a variable then type in its name. In this case, we’ll call this variable CustomerMin. This variable will have a value of 5.
This makes it reference inside the calculation instead of referencing it outside as a variable.
Next, we’re going to create a second variable with the name CustomerMax, and with a value of 20.
The third variable will be called CustomerChannel. This will be equal to “Wholesales”.
After moving down two rows, we need to type RETURN.
The RETURN code is a crucial part when dealing with variables in a measure.
In creating variables in Power BI, we need to remember that they should only be named using one word; variables can’t contain spaces. We can use underscores instead. Also, each variable name should be unique.
Now, once we’ve created variables in our formulas, we can use them to reference the values they’ve been assigned with.
Instead of typing in 5, we can use CustomerMin. The IntelliSense feature also picks up this reference and suggests it as we type.
Repeat this step for CustomerMax and CustomerChannel.
With that, our formula is now complete.
If we want to change 5 to 10, all we need to do is change the value on its corresponding variable.
This is extremely useful when we’ve referenced that value multiple times within the measure. Instead of changing the values individually, we can easily change it in one location.
Using DAX Variables To Reference Tables
Other than values, we can also reference tables.
Using the same measure, let’s add a new variable called CustomerRange. We can reference a table by adding the FILTER statement as this variable’s value, instead of placing it after RETURN.
For the second FILTER statement, we can again add another variable.
So now, to get the sum of the quantities from the Sales data, all we need is to type the CALCULATE function, Total Quantity, and then the variables, CustomerRange and Channel.
With this, the formula has now been shortened and simplified.
Adding Comments Within DAX Codes
Another way to simplify measures or formulas is to add short comments within the DAX code.
This is a great way to make the code easier to understand so that other members in the organization know what it’s for. Adding comments is an effective method to improve user experience.
Comments can be placed anywhere in the formula box.
We only need to type two forward slashes (//) at the beginning of the sentence or statement to make it a comment.
This won’t, in any way, affect how the measure or formula works.
However, we need to make sure to keep comments concise. We don’t want to put too much that it becomes overwhelming.
***** Related Links *****
Why Using Power BI Variables Is A Better Way To Write DAX Measures
Using Variables In Power BI – A Detailed Example
Top 3 Best Practices To Organize Your Power BI Models
Conclusion
Using variables and including comments in DAX codes are effective ways to optimize any measure in Power BI.
We also need to remember that using variables isn’t mandatory. However, it’s a great technique when you want to simplify calculations especially as they get more complex and advanced.
Good formatting and efficient use of DAX functions are all it takes to create a well-organized Power BI model.
All the best,
Sam
