Dimension distinct count SSAS within the context of other dimensions

1
votes

I have this MDX;

CREATE MEMBER CURRENTCUBE.[Measures].DistinctOrderCount<br/>
AS 
  DistinctCount([Order].[Order Id].[Order Id] - [Order].[Order Id].[1]),<br/>
FORMAT_STRING = "#,##0;-#,##0", 
VISIBLE = 1,  ASSOCIATED_MEASURE_GROUP = 'Fact Sales';

I am trying to do a distinct of orders (except Order Id=1) within the context of other dimensions in my fact table. For example, distinct order count by dim customers, dim city, dim state etc. but I am not able to achieve this. When I view my measure on a report, I get distinct count of order dimension which is 21 being displayed on all rows of customers.

1
sql-server-2012ssasmdxolap

1 Answers

1
votes

This is the best way I solved this and I am happy with the results. Hope this may help others. The idea is, create a cube measure with aggregation type Distinct Count and set column binding on the column you want distinct count on. Then use the below MDX to filter out the dummy Order Id's from your distinct count. 

create member currentcube.[Measures].OrderCount<br/>
as<br/>
distinctcount
(

    existing 
    CROSSJOIN
             (
              Except([Orders].[Order Id].[Order Id], [Orders].[Order Id].&[1]), 
              [Measures].[Order Count]
             )

),<br/>
FORMAT_STRING = "#,##0;-#,##0", 
VISIBLE = 1, ASSOCIATED_MEASURE_GROUP='Order Count';