Distinct count SSAS

Question

I’m facing a little issue to calculate a distinct count a number of clients in SSAS OLAP Cube. The difficulty appears for the credited client’s accounts, in other words, for the clients how have credit (quantity = -1) or for the clients how have bought the product and they receive a credit after (quantity = 0). My actual distinct count in my cube considers these two cases as real buying transaction, but in fact they’re not. I’ve checked in SSAS to make a distinct count with the expression (SUM Quantity > 1), but I didn’t find nothing. Now I’m thinking to model these cases directly in my Datawarehouse, but I don’t see how can’t do it. Can anyone de give me a little help? Thanks.

Answer 1

I would feed this data into SSAS using a SQL View. Within that View I would define a calculation to return NULL for the rows you dont want to count, something like this:

CASE WHEN quantity <= 0 THEN NULL ELSE Client_Account END AS Client_Account_For_Distinct_Count

Then I would use that column as the basis of the SSAS Distinct Count measure.