Create a #dax Measure in a Session in #BISM #Tabular

Thanks to Greg Galloway that pointed me out this is possible, in this post I want to show how to create a measure local to a session. This could be done in SQL Server Management Studio, even if it is probably not so useful for a Tabular model considering that you can easily define measures local to a DAX query. However, maybe you will have a reason to do that (I can only imagine a long list of measures defined in a session that are used in small queries sent from a client – it could be an idea for custom clients connecting to Tabular that sends many short queries using the same measures, for example a dashboard or a set of charts).

First, you have to connect to Analysis Services by including the Cube=<cubename> in the connection string. By default, the cube name of a tabular model is “Model”, so you can use Cube=Model in the connection string. You can do that in SQL Server Management Studio by placing that string in the Additional Connection Parameters, as you can see in the following picture.

Then, instead of using DEFINE MEASURE before EVALUATE statement, like you would do in the following statement:

DEFINE

MEASURE ‘Internet Sales'[Internet Total Sales] = SUM([Sales Amount])
MEASURE ‘Internet Sales'[Internet Total Product Cost] = SUM([Total Product Cost])
MEASURE ‘Internet Sales'[Internet Total Margin] = [Internet Total Sales] – [Internet Total Product Cost]
EVALUATE
SUMMARIZE(
‘Internet Sales’,
‘Date'[Calendar Year],
“Sales”, ‘Internet Sales'[Internet Total Sales],
“Cost”, ‘Internet Sales'[Internet Total Product Cost],
“Margin”, ‘Internet Sales'[Internet Total Margin]
)

You can define measures by using the CREATE SESSION statement. Please note that if you didn’t set the Cube in the connection string, you would get an error.

CREATE SESSION
MEASURE ‘Internet Sales'[Internet Total Sales] = SUM([Sales Amount])
MEASURE ‘Internet Sales'[Internet Total Product Cost] = SUM([Total Product Cost])
MEASURE ‘Internet Sales'[Internet Total Margin] = [Internet Total Sales] – [Internet Total Product Cost]

At this point, you can reference the measures you defined in your DAX query:

EVALUATE
SUMMARIZE

(
‘Internet Sales’,
‘Date'[Calendar Year],
“Sales”, ‘Internet Sales'[Internet Total Sales],
“Cost”, ‘Internet Sales'[Internet Total Product Cost],
“Margin”, ‘Internet Sales'[Internet Total Margin]
)

I’d be interested to hear whether you find some scenario in which it is useful using this feature!