Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Defining DAX Measures In The With Clause Of An MDX Query

with 3 comments

It’s a little-known fact (but certainly not completely unknown – it was mentioned in Marco, Alberto and my SSAS Tabular book I think) that you can define measures using DAX in the WITH clause of an MDX query. This means you can write queries like the following against an SSAS Tabular model:

with
measure ‘Date’[Demo Calc] =
countrows(‘Date’)

select {measures.[Demo Calc]} on 0,
[Date].[Calendar Year].members on 1
from [Model]

image

The official documentation, such as it is, is here:
http://msdn.microsoft.com/en-us/library/hh758441.aspx

Unfortunately you can’t use it from Excel 2013 using the new ‘create calculated measure’ functionality; I also talked to the nice people behind OLAP PivotTable Extensions and there are some very good reasons why they can’t support this either.

What use is this then? You’re only going to be able to use it in scenarios where you control the generation of the MDX on the client side, such as SSRS reports, which may not be all that often; in fact, in these situations you might be better off writing the whole query in DAX. It’s only going to be useful when you need the power of MDX and DAX in the same query. For example, you might want to take advantage of DAX’s superior ability to detect multiselects, but write all your other calculations in MDX. I’m clutching at straws here though! Still, it’s an interesting thing to know about. Please leave a comment if you can thing of a situation where you can use it…

Written by Chris Webb

July 14, 2013 at 11:43 pm

3 Responses

Subscribe to comments with RSS.

  1. Good to know because you can use this functionality with my frontend OpenView:)

    Hakan Altınok

    July 15, 2013 at 8:39 am

  2. I saw that people tried to take a look at OpenView. You can find the details through my blog.

    There is free personal edition of OpenView

    http://analyzerhakan.wordpress.com/

    Hakan Altınok

    July 16, 2013 at 7:45 am

  3. Reblogged this on analyzerhakan and commented:
    I think of putting this functionality into OpenView

    analyzerhakan

    July 29, 2013 at 4:09 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,867 other followers

%d bloggers like this: