DAX is a formula language developed by Microsoft to help data analysts enrich their data set and extract useful information out of raw data. DAX code snippets have nested function calls which are difficult to understand if you’re a beginner in DAX. Therefore, variables are used to break the code into smaller understandable units. In this blog, you will learn how to use variables to increase code readability and make debugging easier.
Improving code Readability:
To see how variables can be used to increase readability:
- Right click on the All Measures table.
- Click on New Measure.
Measure tools open.
- Type the following expression in the formula bar and press Enter.
In this expression:
- Only the Products in the Product table that belong to the category of “Bikes” and have a List Price greater than 2500 are filtered.
- Click on the Card visual in the Visualization Pane.
- Drag and drop No of Premium Bikes (CM) from All Measures table to the Fields.
Notice that we have 13 premium bikes in our data base. This result is simple but the code consists of nested DAX functions which may not be that easy to understand and comprehend..
Let’s get the same results by breaking down the code in smaller steps and increasing code readability by assigning variable names at different steps of the calculation.
- Right click on No of Premium Bikes (CM) in the All Measures table.
Measure tools open.
- Erase the existing code.
- Type the following code in the formula bar and press Enter.
In the Expression:
- First of all, the List Price for the Premium bikes is defined and the value is stored in a variable PremiumBikesListPrice.
- After this, a virtual table of products is created where the product category is “Bikes”. This variable is stored in a variable name BikesCategoryProducts.
- As the third step, a table of the products from the BikesCategoryProducts that have the List Price greater than PremiumBikesListPrice is evaluated and stored in PremiumBikeCategoryProducts.
- Finally, the total rows in the PremiumBikeCategoryProducts are evaluated.
Notice the value in the card visual is still the same. However, the logic behind the calculation is much easier to understand.
Variables for debugging:
You can also change the variables after return. This helps to debug code.
- Right click on No of Premium Bikes (CM) in the All Measures table.
Measure tools open.
- Replace the PremiumBikeCategoryProducts in the argument of COUNTROWS() with BikesCategoryProducts.
Notice the change in the value of card visual.
Conclusion:
Variables are used in DAX to improve the code readability, make code debugging easier and reduce code complexity. The DAX engine evaluates the variables only once during the code execution, making the code faster and more readable. This blog presents a step by step guide on how variables can be used to improve the code readability and perform code debugging.
Are you looking for an instructor-led Power BI or Power Apps training in Australia? We provide classroom as well as online live trainings for the following courses:
