Count Fact Records Based on Dimension Value

I would like to count the number of fact records that are associated with a dimension value.  (I'm an MDX newbie)

For example, lets say I have a FactOrder table and a DimOrderType table with a regular 1-1 relationship.  I have 2 order types of Internet and Phone and want to have an InternetCount and PhoneCount.

I created an InternetCount calculated member with this in the expression box on the Form View:

([DimOrderType].[OrderTypeDesc].&[Internet], [Measures].[FactOrder Count])

This seems to work except when I look at other items from the DimOrderType table.

My calculated member seems to get repeated for the other order types like:

Order Type    FactOrder Count        InternetCount  

----------     -----------------       -------------

Internet                5                             5

Phone                   10                           5

Grand Total           15                            5

This seems like it could be a common scenario.  How should I do this?

Hi scott. Indeed, since you specifie internet in the orderType dimension, it will return always the same value.

Why dont you use your member as you do, but like this:

IIF([DimOrderType].[OrderTypeDesc].currentmember.properties('KEY')='Internet', [Measures].[FactOrder Count], 0)

This should help you. let me know.

That solves the problem of the number being duplicated, but now there is not a total for the calculated member.  So, it looks like:

Order Type    FactOrder Count        InternetCount  

----------           -----------------           -------------

Internet                 5                           5

Phone                   10                           0

Grand Total           15                           0  <-- would like 5 here

Can't you do that in the report layout? Are you using, table, matrix or tablix(2008)?

don't know for sure, but try something like this:

IIF([DimOrderType].[OrderTypeDesc].currentmember.properties('KEY')='Internet', SUM([DimOrderType].[OrderTypeDesc].currentmember,[Measures].[FactOrder Count]), 0)

I want to make this count available to users that connect to the cube in Excel.  So, its not in a report.  The above produces the same result - no totals. 

I'm wondering if the issue is that when I browse the cube (in BIDS) and it pulls the sum, that the current member is not 'Internet'.  I've spent some time looking at if there is a way to determine what level of data is being pulled from the cube, but am not sure if this is an approach that can work.

Hi Scott,

I think you were right to start with but are unsatisfied that the Phone intersection shows a value.  Your expression is dead on but may need a little tweaking.

- In your example the All member if using ([DimOrderType].[OrderTypeDesc].currentmember,[Measures].[FactOrder Count]) will be 15 and this is not what you want
- Your original solution is correct for [DimOrderType].[OrderTypeDesc].&[Internet] and is 5
- Your want [DimOrderType].[OrderTypeDesc].&[All] to be 5 for this calculated measure, your original solution can be used for this also
- but you don't want the measure to apply to [DimOrderType].[OrderTypeDesc].&[Phone]

You could individually scope the measures

CREATE MEMBER CurrentCube.[Measures].[Internet Count] AS Null;
//Overwrite Internet Count at Internet member intersection and leave all other intersections NULL
SCOPE( [Measures].[Internet Count],[DimOrderType].[OrderTypeDesc].&[Internet] );
  this = ([DimOrderType].[OrderTypeDesc].&[Internet],[Measures].[FactOrder Count])
END SCOPE;
//Overwrite Internet Count at All member intersection and leave all other intersections NULL
SCOPE( [Measures].[Internet Count],[DimOrderType].[OrderTypeDesc].&[All] );
  this = ([DimOrderType].[OrderTypeDesc].&[Internet],[Measures].[FactOrder Count])
END SCOPE;

or create the measure as you did and just NULL it for the phone intersection

CREATE MEMBER CurrentCube.[Measures].[Internet Count] AS
([DimOrderType].[OrderTypeDesc].&[Internet],[Measures].[FactOrder Count]):
//Overwrite the Phone Member with NULL and leave all other intersections as Internet Count tuple
SCOPE( [Measures].[Internet Count],[DimOrderType].[OrderTypeDesc].&[Phone] );
  this = NULL;
END SCOPE;

Hope this helps,
Luke