Calculated Member distinct count expression

_{Thursday, September 4, 2014 7:51 PM}

Hi , 

 I want to create a new calculated member in OLAP Cube to count the number of distinct clients,  I'm trying to write  this expression, but i don't know how to make it in MDX: 

Distinct count ([Dim.clients].[CuNumber], where Sum([Measures].[QQT - FACT Ventes] >=1) 

Any help please !

Thanks!

_{Friday, September 5, 2014 7:09 AM}

Hi Merouane,

According to your description, you want to create calculated measure to calculate the distince clients, right?

In Analysis Services, the distinct count measure is very useful. For example, we need to know the number of users who buy our products. We sometimes have a customer that buys many products, but we need to know the number of unique users. Here are some links which describe several options for creating a distinct count measure in SSAS. Please see:
http://www.mssqltips.com/sqlservertip/3043/different-options-for-creating-a-distinct-count-measure-in-ssas/
http://www.databasejournal.com/features/mssql/article.php/10894_3342491_4/MDX-in-Analysis-Services-Introducing-DISTINCT-COUNT.htm

Regards,

Charlie Liao
TechNet Community Support

_{Tuesday, September 9, 2014 7:02 AM | 1 vote}

That's true, but M can also use a calculation to determine the distinct count at execution time using the client dimension.  There are pros and cons see this for more info.

M, can you change your calculation expression to the following?

count (nonempty([Dim.clients].[CuNumber].[CuNumber],[Measures].[QQT - FACT Ventes])) 

This assumes that you aren't holding zero values for [QQT - FACT Ventes].  If you are, you will need to swap the nonempty with a

count(filter([Dim.clients].[CuNumber].[CuNumber],[Measures].[QQT - FACT Ventes]>0)), which might be a little slower.

Richard

_{Thursday, September 11, 2014 2:32 PM}

Hi thank you for replies, 

I spent couple of days trying to make the query work, but without big progress. 
First, I ran an SQL query on my datawarehouse to know what result should I get on my OLAP Cube, 
this is my SQL query: 

use [Warehouse]

select count(*) as count_row

From 
(Select  F.FaCunumberX
from [dbo].[Dim_FaClients] F
inner join [dbo].[FACT_Ventes] V on F.[SK_FAClients] = V.SK_FaClients
inner join [dbo].[Dim_Date] D on D.SK_Date = V.SK_Date
where
D.Year = '2014'
Group by F.FaCunumberX
having  SUM(V.QQT) >= 1) test

the result I get is : 26026

On my OLAP Cube I tried several queries, but I didn't get the same result 

this is some of the expressions that I tried : 

WITH SET MySet AS

(Filter({[DIM FA Clients].[FaCuNumberX].[FaCuNumberX]}*{([Dim Date].[Year].&[2014],[Measures].[QQT - Fact Ventes])},[Measures].[QQT - Fact Ventes]>1 or [Measures].[QQT - Fact Ventes]=1)

MEMBER MEASURES.SETDISTINCTCOUNT AS
DISTINCTCOUNT(MySet)
SELECT {MEASURES.SETDISTINCTCOUNT} ON 0
FROM [CubeAll]

the result I get with this one is : 31575

I tried also this expression : 

DistinctCount(Filter([DIM.Clients].[CuNumber].[CuNumber].Members,
                     [Measures].[QQT - FACT Ventes] >= 1
                    )
             )
the same result : 31575

sincerely, I don't see what I'm missing on my expressions. 

Thank's  for your help ! 

_{Thursday, September 11, 2014 4:51 PM}

hello, 

will try this way 

With 

SET Test AS  Filter ([Product].[Product].members,SUM([Measures].[Reseller Order Count])>1)
MEMBER  test1 AS Distinctcount(Test*[Measures].[Reseller Order Count])

SELECT {Test1} ON 0,
[Reseller].[Reseller].MEMBERS
ON 1
FROM [Adventure Works]

what error you  are getting  ,your expression looking good check if you are missing any where condition 

blog:My Blog/

Hope this will help you !!!
Sanjeewan

_{Thursday, September 11, 2014 5:38 PM}

Hi Sanjeewan, 

first thank's for your reply.

There is no error on my expression, it's working fine, but it's giving me wrong result and I can't figure out why.

I put in my post above my SQL script that I used in my relational warehouse which gives me the right result, but I couldn't reach this result in my OLAP Cube with MDX expression.   

Thank's!

_{Friday, September 12, 2014 3:09 AM}

Hello,

Have you tried to reprocess the cube ?

blog:My Blog/

Hope this will help you !!!
Sanjeewan

_{Friday, September 12, 2014 1:04 PM}

Hi, 

Yes, everyday

Thank's!