Distinct count problem

Hi,

I'm trying to create a measure (distinct count) for counting the number of stores which sell product. I used to fact table to count store_key but I've just realized this measure returns a count including stores which do not sell the product.

Daily Sales fact : store_key, net sales dollars .....

How should I define the calc in this case? I'm thinking conditional statements but not clear yet.

Please give me some comments. Thanks.

We do something similar - a count of invoices that have a coupon applied to them. There are two ways (that I know of) to do this:

1) at the fact level. Have a measure in your fact table that contains a 1 or 0 for each store that sells the product. Not a very efficient way, but it will get the job done. Do a sum on this measure. Depending on how you want to count your products, you could have a measure group that is store_key, product_key, sells_product (int). This will work, but definitely not efficient.

2) The way we do it is to make the item you are measuring an attribute of the dimension itself. In our case we have an invoice dimension which contains a "has_coupon" attribute. As part of the ETL we determine if the value of has_coupon shoud be "Yes" or "No". You then can query according to the values of this attribute. We then can do [Invoice].[Has Coupon].Children.Count, or any other derivation of it.

I would recommend option #2, but it all depends on your situation. If anyone else knows of other ways to model, I'm curious too.

Good luck,

John Hennesey

|||Not totally clear, but is it counting a NULL value when you don't want it to?

Just create another fact table in the DSV based off of the fact table where the key is not null:

select *
from fact
where
key is not null

And create the DISTINCT COUNT measure based of this new new table.
|||

thanks.

it's easy and it works fine

sql