In out last Power BI training in Sydney for Power BI Basic course, we were asked “What are variables and comments in DAX”. So we decided to dedicate a complete blog to this top so we can fully address this knowledge gap.
DAX is formula language and is logically different from programming languages. These two however hold some similarities. Like all programming languages, DAX also has variables and comments. This is especially useful when you’re writing complex calculations that involve compound expressions. Variables and comments in DAX can:
- Improve code readability: using simple understandable DAX variables, a DAX expression becomes easier to understand and interpret.
- Reduce Complexity: the expressions stored in variables become reusable and can be referred in other functions.
- Improve Performance: Values stored in the variables need not be calculated twice and this improves the performance.
- Simplify debugging: Variables help you in debugging by allowing you to test your expression using RETURN expression to the output of variable.
In this blog post, the concept of variables and comments in DAX using Power BI has been covered.
Creating a variable
Variables are easy to create. Variables are created using the keyword ‘var’ , designating a descriptive name, assigning an expression and returning the value.
The parts of the variable include:
- Keyword identifier.
- Variable name.
- Expression.
- Return statement.
Once the variable is created, Power BI intelligently detects the variable and it can be verified by ‘xy’ symbol.
Features of a variable
The usability of the variables is self-evident from the features of the variables:
- Variables can be used for scaler values or tables.
- Variables can be defined by other variables.
- Variables make the code easier to read.
- Variables are evaluated right where they are defined.
- Variables can be defined anywhere in an in-line DAX expression.
- Variables improve the performance by in avoiding duplicating computations.
- Variables might help avoid issues with row and filter context mistakes this is because they are evaluated where they are defined.
Location of Variable evaluation
Variables must be defined at the correct location. They are used for single evaluations of complex subexpressions. DAX optimizer evaluates the value for the variable only once. This makes the code much faster as the same sub expression needs not to be evaluated twice. Variables can be defined anywhere in the DAX expression using the correct syntax.
Conditional computation using variables
Writing conditional statements becomes exceptionally easier with the use of variables as they rely on the IF-ELSE principle. A use case of variables used for conditional computations is:
When the expression above is executed, the sum of the order quantity from the Sales table is stored in the variable named ‘totalquantity’. After this, the value is compared with a given threshold and the subsequent steps are performed.
Comments in DAX
Comments are added in the DAX expression to make code understandable. The lines are executed by the DAX optimizer and therefore act as ‘sticky notes’ in the code snippet. There are two ways of writing comments in DAX:
- Single line comments.
- Multi-line comments.
Writing single line comments
Single line comments begin with the ‘//’ symbol and are automatically terminated as the line ends. Single line comments span one line only. An example of single line comments is:
Writing multi line comments
Multi-line comments can be written on more than one line. They begin with ‘/* ‘and are ended with ‘*/.. Everything written inside these two identifiers is not executed by the DAX optimizer. An example of multi-line comments is:
Comments to the RESCUE!
What do you do when you’re half way through writing a complex impression and want to save it instead of Power BI throwing an error.
You can simply wrap your expression in a multi-line comment and finish it later.
Consider the following complex partially written measure.
The formula written is not important. The formula isn’t yet complete either and if-left untreated this formula throws error. The best solution is to use a multi-line comment here.
Conclusion:
Variables and comments are added in DAX expressions to increase the code readability, improve performance, reduce complexity and simplify debugging. In this blog post, the concept of variables and comments in DAX has been summarized using examples of code snippets and other use cases.
Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide our services in the following regions
