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

7 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

  4. Hi Chris:

    I work as team leader of BI team in IMSHealth, Dhaka. We generally are trying to shift from SSAS Multidimensional to SSAS Tabular. Previously we built complex cubes and wrote complex MDX to generate the output which would go as feed of a .NET application to develop Dashboards. I was trying to replace the cube with tabular and MDX queries with DAX queries. But found that, it is not that straightforward. Even a simple userdefined hierarchy cannot be selected by DAX (by EVALUATE) where as it is pretty easy in MDX. I guess these features are not present in DAX. So, Can you suggest me about how we should move towards tabular ? My little understanding says – we can replace the cube with tabular data model and query the tabular with MDX but not with DAX. We can redesign the tabular database with the power of DAX. If, we use the same MDX for cube and tabular model, will the output for tabular be faster than output for cube ? Please shed some light on me. Thanks in advance.

    Tanvir Zobair Mahboob

    April 22, 2014 at 8:34 am

    • Hi Tanvir, it’s wrong to say that Tabular will always give you better performance than Multidimensional – there are too many factors to consider. All you can say is that it’s going to have different strengths and weaknesses. So maybe you should reconsider why you want to move to Tabular? You’re right, a lot of things that are easy in MDX are hard in DAX; it’s also true that a lot of things that are hard in MDX are easy in DAX.

      Chris Webb

      April 22, 2014 at 9:58 am

      • Hi Chris:
        Thanks for your insight and clear remarks. I know there are specific fields where multidimensional is better than tabular and vice versa. But so far I sensed that Microsoft has not been very optimistic about investing more on multidimensional and we saw that in sqlserver 2012, 2014. Rather they are focused on in-memory architecture. So, I was wondering of switching the technology specially for our online dashboards, you know pretty much better as you were here. So, for a relatively smaller data-size, can we do some pilot project by replacing the cube with tabular and restructuring the MDX reports a little bit and see the difference of performances ? or You can suggest something else ? I would be really grateful if you can show some technical roadmap, please. Thanks in advance.

        Tanvir Zobair Mahboob

        April 22, 2014 at 11:52 am

      • Yes, certainly MS have been investing more in Tabular and Power Pivot recently. In theory, yes you could replace your Multidimensional cube with a Tabular model and your dashboard should still work with only minimal changes to the MDX. However this could be misleading: the hardware requirements for Tabular are very different, so you would probably need to use a different server; you would also need to make sure you have tuned your model properly too. So I don’t think it’s easy to compare Multidimensional with Tabular unless you are willing to redesign your solution for Tabular. It wouldn’t hurt to run a quick test though and see what the results were.

        Chris Webb

        April 22, 2014 at 2:52 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,868 other followers

%d bloggers like this: