Data looks like this. Products are sold and each sale may be associated with a promotion. The promotion scheme is not constant for a product and can be used across a product

Fact -
SKU Id, Promotion Id
x , null (not associated with a promotion)
y , 1
z , 2
a , 1

Dimension - SKU
Product Id
SKU Id
members are
1001,x
1001,y
1001,z
1002,a


The counts i needs are
number of products (distinct count) - here 2
number of promoted products - here 2
number of non promoted product - here 1

The product 1001 will appear in the promoted as well as non promoted count.

I am able get the first by just setting up a distinct count with the source as the product ID from the SKU dimension table

Can some one help me do the other two please. I am working with Yukon, and looks like we are not allowed to use an iif in the measure expression to create a derived measure. Even if I were, i am not sure how to pass the results to a distinct count, as a distinct count counts nulls also.

thanks very much

--
Message posted via http://www.sqlmonster.com