7 Replies Latest reply on Nov 14, 2013 2:39 AM by . Indumon

# Using MDX Calculated Members to filter a measure by a dimension

Hi all, I apologise ahead of time if I am being a little bit slow when it comes to using MDX.

We're discovering the joy of using Tableau connected to an OLAP cube, and one of the things we need to figure out how to do is to create a calculated member measure that is the result of another measure, sliced by a dimension.

Given a dimension called ResponseType of "Yes", "No", "Other", and a measure of "Responses", we need the calculated measure of "YesResponses" to be a count of all Responses that are Yes.

It seems simple, but I've tried most of the solutions on this forum and others, without much luck. The common wisdom seems to be doing something like the below, however this is only producing a null value.

([ResponseType].[Yes],[Measures].[Responses])

Any help here is gratefully received!

Thanks,

Clare

Message was edited by: Clare Duxbury - putting the periods in the right places, d'oh.

• ###### 1. Re: Using MDX Calculated Members to filter a measure by a dimension

Hey Clare -

A couple of years ago, I might have been able to answer this for you -- no more. My poor MDX skills have atrophied to nothing.

I frankly think you'll get a better response out on the Microsoft SSAS forums. There are very few glutton-for-punishment,  MDX loving folks here

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlanalysisservices

Good luck!

• ###### 2. Re: Using MDX Calculated Members to filter a measure by a dimension

Hi Clare,

Count(NonEmpty(Descendants([Response Dimension].[Response Type].Currentmember),[Measures].[Responses]))

• ###### 3. Re: Using MDX Calculated Members to filter a measure by a dimension

oh no this will count the descendants of dimension, you actually want the count from fact table. I will try once I connect to my cube tomorrow my morning

• ###### 4. Re: Using MDX Calculated Members to filter a measure by a dimension

Hi Russell,

Thanks for taking the time to reply - unfortunately most of the SSAS related forum responses don't work very well in Tableau - believe me, I've looked all over.

Thanks though

Clare

• ###### 5. Re: Using MDX Calculated Members to filter a measure by a dimension

Hi .Indumon,

Thanks for looking into this - hopefully you can help answer what should be a fairly simple thing, but that has four fairly keen minds stumped at this end.

My fingers are crossed!

Clare

• ###### 6. Re: Using MDX Calculated Members to filter a measure by a dimension

Hi all,

We have now figured it out!

The problem we had is that MDX does not recognise the names that we alias fields to. You always have to use the core name as specified within the cube. D'oh!

Thanks for everyone's time.

Clare

• ###### 7. Re: Using MDX Calculated Members to filter a measure by a dimension

Hi Clare,

Thank you for the update and happy to hear that your problem is resolved