Create DAX measures with a session’s scope #dax #ssas #tabular

This short blog post is a note that could help myself in the future, or anyone who is interested in writing a client tool for Analysis Services.

When you establish a session to Analysis Services, you can run both DAX and MDX statements. In DAX, you can create a measure that is local to a query by using the following syntax:

DEFINE MEASURE Sales[Sales Amount] = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

You have to execute this statement every time you run the same query, even when you do that within the same session.

If you have administrative permission, you might deploy such a measure to the data model, but this would affect all the users and is not interesting in the scenarios I’m considering. However, you can use a mix of MDX and DAX syntax to create a measure that has a session scope:

CREATE SESSION MEASURE Sales[Sales Amount] = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] );

You might expect this syntax to work only in MDX statements, but it works also in any following DAX query within the same session.

As you might imagine, this is an interesting idea to “inject” measures in an existing report. I originally evaluated this approach to create DAX measures for an Excel PivotTable connected to Analysis Services Tabular. However, the “MDX Calculated Measure” feature in Excel cannot be used for this purpose, because they require MDX syntax in the measure definition.

Maybe some other tools/developers will find this information useful.