DAX variables

When you write DAX expressions it is a general recommendation not to overestimate their complexity. It is always better to create several measures and further consolidate them within the new formula. The code is more transparent, and there is a lot less chance to make an error. Since recently DAX also allows making variables. They are used to create measures that will be used to create an expression that returns final result.

Variables are created as part of the measure. We start with creating a new measure, and after naming it, press SHIFT + ENTER to switch to a new line. Then we create variables by order. Each variable starts with the VAR command, after which we specify its name, and then write DAX expression that performs required calculations. Finally, we specify the RETURN command and behind it expression that is assigned to the initial measure.

Let’s see how this works on one simple case. We will create a PR measure that calculates the percentage share of earnings. Before this, we created a measure that calculates gross margin (earnings):

Zarada = SUMX(Transakcije,[KOL]*RELATED(Artikli[RUC]))

Next we’ll create measure PR, by selecting Modeling ribbon, and further New Measure option:

PR =

VAR ZarALL = CALCULATE([Zarada];ALL(Partneri))

RETURN DIVIDE([Zarada];ZarALL)

In formula above we have temporarily created the ZarALL variable that calculates the earnings by cancelling all the filters created by columns in the Partner table. Then we used the DIVIDE function. It allows dividing of two numbers, and if it comes to dividing with zero, it returns a blank value or some other one which we specify. It is done by adding its third, optional parameter. Result of this operation is a measure that calculates percentage.

If we add one slicer based on the column from the Partner table, as well as the table with the values KLASIFIKACIJA (CLASSIFICATION) and PR we’ll get desired report. In order to make it even more beautiful, we can add one pie chart, as well as additionally formatize some of its elements.