THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at https://www.sqlbi.com/blog/marco/ - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

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. 

Published Wednesday, April 22, 2015 6:12 PM by Marco Russo (SQLBI)

Comments

 

Simon said:

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 8, 2015 8:36 AM
 

Marco Russo (SQLBI) said:

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

May 8, 2015 8:46 AM
 

Sergiy said:

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!

July 9, 2017 4:31 AM
 

Sergiy said:

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])) )

)

July 9, 2017 4:35 AM
 

Marco Russo (SQLBI) said:

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

July 11, 2017 8:03 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Privacy Statement