Get distinct count on calculated member

mdxolapssas

I'm using SSAS and created a measure group that is linked to Fact_SalesMetrics on SQL Server. In that, I have seven measures. All measures have aggregation of Sum except Sales Invoice which has aggregation of Distinct Count.

Everything works great. I need to make the aggregation of 'CustomerID' also to Distinct count, but I can't be able to. It says me error states, "The measure group has more than one distinct count measure."

So, I need to have these two measures's aggregation as DistintCount. How can I? Any help will be appreciated.

I thought to create a calculated member to achieve this but got failed as I don't know what expression I have to give!

Best Answer

The limitation of a single distinct count per measure group is related to performance. To get both distinct count measures to show up in the same measure group, you can use the workaround suggested here by Darren Gosbell.

best of both worlds...performance benefit from each DC-measure residing in its own measure group, UX benefit from having all related measures appearing in same measure group.

Related Solutions

ParallelPeriod returning null for Feb. 29 in date dimension

Another option would be to use a union to get parallel periods for the current and previous member. Then extract the first item out of this, which will always be the current member (except in the case of Feb 29 where it should fall back to the previous member).

That is, effectively coalesce with the previous member parallel period as per below:

union 
(
    PARALLELPERIOD(
        [Date].[Fiscal Detail].[Fiscal Year],1,
        [Date].[Fiscal Detail].currentmember) 
    *(will be null if Feb 29)*
    ,
    PARALLELPERIOD(
        [Date].[Fiscal Detail].[Fiscal Year],1,
        [Date].[Fiscal Detail].currentmember.lag(1))  
    *(will be the prior year Feb 28 when above is Feb 29 )*    
).item(0)  *(get the first member out of the union set)*

Sql-server – MDX – Sum values greater than then a median calculated measure

You need first to define a measure that will compte the median price across the siblings:

[Measures].[Siblings_median_price] 
AS 
MEDIAN([DimItems].[Dim Items Id].CurrentMember.Siblings, [Measures].[Price])

The following measure should return what you want:

[Measures].[Sum_below_median] 
AS 
SUM(
     [DimItems].[Dim Items Id].CurrentMember.Children
   , IIF([Measures].[Price] < [Measures].[Siblings_median_price]
   , [Measures].[Quantity], Null)
)

Best Answer

Related Solutions

ParallelPeriod returning null for Feb. 29 in date dimension

Sql-server – MDX – Sum values greater than then a median calculated measure

Related Question