Distinct count with where clause

Hi there

I've little experience with MDX and would appreciate any help. I have a distinct count of a fact table of customer_id but I want a second measure where the distinct count has a where clause, where revenue(measure) >0. Both of the fields are in the fact table.

Thanks in advance.

Derek

Assuming you're using AS 2005, a more efficient option may be to add a named query as a 2nd fact table, based on the SQl where clause: revenue >0. Then the 2nd measure (in its own measure group) can directly be a distinct count of customer_id on the 2nd fact table.|||

Hey Deepak

Thanks for the reply. Yes I'm using 2005. Wow I wouldn't have ever thought of that so are you suggesting that I have just the player_id date and revenue in this second table, would I remove revenue from the first table? Also how will this affect performance seeing that I'm almost doubling the amount of fact rows.

Cheers,

Derek

|||

Derek,

you could create just named query, not additional table. The query should contain all necessary dimension links as the original one (or do you have just date dim?). You're right, amount of data in cube will increase, but in sake of speed. I'm using same thing for distinct invoices and I have separate named queries for different filters. Performance is really good with this approach.

Radim Hampel

|||

I supose you dont want to display the 0 value, then try this:

for example

1.- Create a count measure ME1

2.- Create your distinct count measure ME2

both of (customer_id)

3.- Create a calculated member

iif (isempty([Measures].[ME1]),([Measures].[ME1]),([Measures].[ME2]))

then when your distinctcount is 0 it is going to pick the empty value of the count measure and when is >0 it is going to pick the value of the distinct count. Then you arent going to have 0 in your results

it really works for me

Jose

|||

Thanks Guys

You are correct performance seems pretty good, and as you mentioned also Radim, I actually have more then one extra distinct count I want to do so am including field on which to perform distinct count and all foreign keys to dimension tables in each of these seperate queries. Who would of thought?

Cheers,

Derek

|||

Hey Jose

this sounds a bit like what I was expecting for an answer originally, looks interesting I'l give it a try and let you know how I got on.

Thanks

Derek

|||The above sounds good