What is variable bi variable
DAX: Using Variables to Improve Formulas
- 3 minutes to read
Writing and debugging DAX calculations can be challenging for data modelers. Complex computational requirements often involve writing compound or complex expressions. Compound expressions can involve the use of many nested functions and possibly the reuse of expression logic.
Using variables in DAX formulas makes it easier to do complex and efficient calculations. Variables support you in the following:
This article demonstrates the top three benefits by using a sample YoY year-over-year (YoY) sales growth measure. (The formula for year-over-year sales growth is “period sales _fewer sales” for the same period last year divided by sales for the same period last year)
Let's start with the following measure definition.
The measure produces the correct result, but now let's see how it can be further improved.
Notice that in the formula, the expression for the calculation is repeated for the same period in the past year. This formula is inefficient because Power BI has to evaluate the same expression twice. The measure definition can be made more efficient with the help of a variable.
The following measure definition shows an improvement. An expression is used to represent a variable named SalesPriorYear assign the result for "same period last year". The variable is then used twice in the "RETURN" expression.
The measure continues to produce the correct result in about half the query time.
Note that selecting the variable name in the previous measure definition makes the expression “RETURN” easier to understand. The expression is short and self-explanatory.
Variables can also help when debugging a formula. To test an expression associated with a variable, temporarily rewrite the RETURN expression to return the variable.
The following measure definition only gives the variable SalesPriorYear back. Notice how the intended phrase "RETURN" is commented out. This technique allows you to easily recover the expression after you have finished debugging.
In previous versions of DAX, variables were not yet supported. Complex expressions that introduced new filters had to use the DAX functions EARLIER or EARLIEST to refer to outer filter contexts. Unfortunately, according to data modelers, these features have been difficult to understand and use.
Variables are always evaluated outside of the filters that the expression "RETURN" applies. If you use a variable within a changed filter context, the same result is achieved as with the "EARLIEST" function. Therefore, the use of the “EARLIER” or “EARLIEST” functions can be avoided. This means you can now write formulas that are less complex and easier to understand.
Note the following definition of a computed column, that of the table Subcategory was added. It evaluates based on the values in the column Subcategory Sales (Sales sub-category), select a rank for each product sub-category.
The "EARLIER" function is used to access the value in the column Subcategory Sales (Sales subcategory) im current line context to refer.
The definition of calculated columns can be improved by using a variable instead of the "EARLIER" function. The variable CurrentSubcategorySales saves the value of the column Subcategory Sales (Sales sub-category) in the current line context, and the expression "RETURN" uses it in a changed filter context.
For more information about this article, see the following resources:
- Regret your love affair
- Sharks can get out of the water
- Should I continue piano lessons?
- Is Chrome or Firefox better for Android
- How strong is Apple's brand loyalty
- Is smoking one cigar a year harmful?
- It is important to know calculations in order to design architecture
- Heroism always includes taking heroic deeds
- What is beyond dimensions and not dimensions
- A single offer can be accepted
- Which is the smallest edible fruit in the world
- How do you test legacy code
- Can I approach marital websites
- What makes playdough soft
- Which is the best shopping chat bot
- How was the Lisp programming language developed
- What big hurricane hit New Orleans
- What is the name of the cube root in Hindi
- What are the major scales
- Why isn't Google indexing my blog
- How important was the 1st World War
- How can you stop the playback of flashbacks
- What is the content of pure water
- What is the quotient of 987654321 123456789
- What is 2cos theta
- Why are people vindictive
- Which stream should I choose after 10
- Why do dreams cause electrical impulses
- Is engineering better than medicine
- Who wins Hancock or Brightburn
- Which is the right favor or favor
- Are rabbit runs with wire floors in order