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

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Analyze multiple EVALUATE statement in a single #dax statements in DAX Studio

A few hours ago, DAX Studio 2.5.0 has been released, with a number of small new features (I will write about DirectQuery and new column in Server Timings in a future blog post and article). In the many bug fixes, this version of DAX Studio does not raise an error when multiple EVALUATE statements are executed within the same Run operation. This could be particularly useful when you analyze the DAX queries generated by Power BI, which optimize the roundtrip between client and server by using exactly this technique. However, DAX Studio still doesn’t have a full support, but it’s good enough to start an analysis. Let’s see in details the current situation:

  • Results: only the rows returned by the first EVALUATE are displayed in the Results pane. Currently, DAX Studio ignores the following resultsets, which are executed on the server and transferred to the client, but not displayed.
  • Query Plan: the logical query plan contains all the operation of all the EVALUATE statements. However, the physical query plan only displays the operations executed for the first statement, ignoring the physical query plans of following EVALUATE statements.
  • Server Timings: all the storage engine events of all the statements are displayed and computed. Thus, if you consider the set of EVALUATE statements as a single operation, the Server Timings does exactly the right thing. However, you cannot easily split the time spent for each EVALUATE statement.

The plan for future improvements is to align Query Plan behavior to Server Timings, showing all the operations of all the EVALUATE statements. For Results pane, we have to find a way to display other resultsets in an efficient way (feedback is welcome – I don’t like the idea of creating a pane for each result).

Looking at this issue, I also found an answer to a problem that I’ve found discussing with Chris Webb one week ago commenting his post Defining Variables in DAX Queries. The question was why you should use the VAR syntax before EVALUATE? For example, why you should use the first syntax instead of the second one?

DEFINE
    MEASURE Sales[Qt] =
        SUM ( Sales[Quantity] )
    VAR TotalQuantity = [Qt]
EVALUATE
ADDCOLUMNS
    ALL ( 'Product'[Color] ), 
    "Qt %", [Qt] / TotalQuantity 
)

DEFINE
    MEASURE Sales[Qt] =
        SUM ( Sales[Quantity] )
EVALUATE
VAR
TotalQuantity = [Qt]
RETURN ADDCOLUMNS
    ALL ( 'Product'[Color] ), 
    "Qt %", [Qt] / TotalQuantity 
)

The reason is now clear to me: when you want to share the same variable in multiple EVALUATE statements, the former syntax guarantees a single definition and evaluation!

DEFINE
    MEASURE Sales[Qt] =
        SUM ( Sales[Quantity] )
    VAR TotalQuantity = [Qt]
EVALUATE
ADDCOLUMNS
    ALL ( 'Product'[Color] ), 
    "Qt %", [Qt] / TotalQuantity 
)

EVALUATE 
ADDCOLUMNS
    ALL ( 'Product'[Brand] ), 
    "Qt %", [Qt] / TotalQuantity 
)

I know, these details are interesting only if you are writing a DAX client and you are not in the Power BI team (they already use this technique) – in this case, write your comments below, I’d like to know who is working on these tools!

Published Thursday, October 20, 2016 11:56 AM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Harvey said:

Marco,

I am writing a DAX client so am very interested. Having traced PBI queries I can see that they do this as part of a neat way of preparing data for pivot in a Matrix. My question is can we do this in AdoMD? Managed to wade through CSDL but not cracked this bit yet.

October 3, 2017 2:01 AM
 

Marco Russo (SQLBI) said:

I don't think so, AdoMD is not updated for DAX and also for other features (e.g. connecting to Azure AS).

Probably it's better if you create your own library for this if you don't want to wait for MS.

October 5, 2017 11:24 AM

Leave a Comment

(required) 
(required) 
Submit

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