Ok, here it goes.
I have a fact table (Fact_Sales). Sometime, some sales are FLAGGED as SPECIAL (the whole invoice, not only a invoice line). So the field IsSpecial is TRUE every invoice line of a particular invoice.
It is a TRUE/FALSE flag (so I would like using a Bit, in my fact table).
Here is an example of data : (forget the sample, I cant post a table, nor do an HTML table, nor formating text as a table with space)....just imagine it...
Now, my SSAS2005 cube is good and works very well.
First attempt: placing the InvoiceID or NULL in the FLAG field, and DISTINCT COUNT it
In My first attempt, I put the InvoiceID or NULL in the IsSpecial field (that was not a byte), then do a DISTINCT COUNT, directly by adding my measure to a new measure group (so no MDX here). However, SSAS is not that bright, and count the NULL as an occurence. So I always see '1' as the minimal value (finding a lot of "NULL" occurence, so having a count of 1). I tried modifying the base measure source to Preserve Null, or ZeroOrBlank, but it will still shows me the value '1' everywhere (except when there is really a IsSpecial)..
SECOND ATTEMPT : creating a MDX query that will DISTINCT count the IsSpecial 'measure'
I read that here : http://ask.sqlservercentral.com/questions/50286/calculated-member-to-get-distinct-count-on-a-condi.html. My problem, is that there is no dimension to compare to. I mean, WHAT will I be counting on?
Third attempt : Creating a dimension. NO. Note a road I want to go. My customer dont want to split every of their measure into 2 members : Special invoice, and normal invoice. They only wants 1 new measure, named "Nb of Special Invoice".
As you may have noticed, my SSAS capabilities is not that good, as I dont touched to that since 3-4 years, and my client want me to do that...
thanks
Why do you not want to create an attribute in dimension ? That would be the way to go: No need to write anything in the calculation script, fast access, easy to use for users.
Your SPECIAL flag is not a measure: it is not numeric with a meaningful aggregation like sum; it is a categorization, i. e. it should be put into an attribute having member values like e. g. "Special" and "Standard".
Even to implement the user interface as a measure "Nb of Invoices", the best way is to use an attribute, say [Dim1].[SpecialFlag], and then implement the measure "Nb of Invoices" as a calculated measure using the tuple expression
([Measures].[Nb of Invoices], [Dim1].[SpecialFlag].[Special])