Skip to Content

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. 

5 Comments

  • Simon

    May 08, 2015 at 08:36 AM

    Very cool!!!

    Is there any word on how/when this new engine will be moved into other platforms like Excel 2013/16 or SSAS?

  • May 08, 2015 at 08:46 AM

    The syntax is currently available in Excel 2016 preview (and it will be part of SQL 2016, too).

  • Sergiy

    Jul 09, 2017 at 04:31 AM

    hi, Marco!

    I’m trying to apply this logic to my calculation and fail.
    if you can check my screen shot —
    http://take.ms/NGStp

    Thanks in advance!

  • Sergiy

    Jul 09, 2017 at 04:35 AM

    the code itself:

    DEFINE MEASURE
    invoicesENV[QofInv] =
    var invNumber = invoicesENV[Number]
    return CALCULATE( COUNTROWS(invoicesENV), FILTER(invoicesENV, invoicesENV[Number] = invNumber) )

    EVALUATE

    ADDCOLUMNS(
    SUMMARIZE(
    CALCULATETABLE(invoicesENV)
    , invoicesENV[Number]
    ),
    “Q of invoices”,
    CALCULATE( COUNTROWS(invoicesENV), FILTER(invoicesENV, invoicesENV[Number] = EARLIER(invoicesENV[Number])) )
    )

  • Jul 11, 2017 at 08:03 PM

    I think I already replied in comments on the article on SQLBI.

Receive comment updates via RSS

Leave a Comment