DAX measures in Power BI Designer – and new DAX syntax finally here

The last update of Power BI Designer allows you to create measures (not calculated columns yet). Download the new version of Power BI Designer and you will see the New Measure button. The editor is much better than anything you have seen in Excel 2010/2013, but it can be improved (larger real estate is the first request).

The real important fact is another. You have a new version of DAX in your hands. It is not just because you have a some new functions or because the engine is faster (way faster). No, the big change (which is not a breaking change, but just a new feature) are “variables“. I’m not sure this is the right name, but it is the intuitive name you give to a feature where you use the keyword VAR before specifying an identifier. What are we talking about? Look at this example:

Quantity :=
VAR
    TotalQuantity = SUM ( Sales[Quantity] )
RETURN
    IF (
        TotalQuantity > 1000,
        TotalQuantity * 0.95,
        TotalQuantity
    ) 

You can assign an expression to an identifier within a larger DAX expression. The evaluation context is the one where you write the definition. You can avoid repeating the same expression multiple times within the same measure, and you can simplify the writing of code avoiding too many nested evaluations and avoid using EARLIER in most of the cases. For example, consider this expression

= SUMX ( Sales, Sales[Date] <= EARLIER ( Sales[Date] ) )

Now you can write:

=
VAR
    CurrentDate = Sales[Date]
RETURN
    SUMX ( Sales, Sales[Date] <= CurrentDate )

Which is longer, but way more readable.

A longer and more detailed article about the new VAR / RETURN syntax in DAX is available at Variables in DAX on SQLBI.