Excluding Default Member from Measure Calculations

Question

I have built a cube that contains ticket sales information.

I have a measure that is a distinct count of customer numbers from the fact table. The unsold tickets for each performance are recorded in the fact table and are assigned a "special" customer number that refers to a dummy customer record.

I would like to have the distinct count of customer numbers not count the "special" customer number that is used for the unsold tickets.

My best attempt so far is to create a calculated measure that excludes a single customer number from the results. For the query below, the numbers are correct.

WITH SET [RealPeople] AS
    Filter([Customers].[CustomerNumber].CHILDREN, 
           [Customers].[CustomerNumber].CurrentMember.MemberValue <> -100)

MEMBER Measures.[Num Real Households] AS
    DISTINCTCOUNT([RealPeople])

SELECT {Measures.[Num Households], Measures.[Num Real Households]} ON 0,
[Location].[PostalCode].CHILDREN ON 1
FROM Tickets

But this doesn't work if the user queries the same dimension that contains the customer number. When that happens the [Num Real Households] measure returns the total number of distinct customer numbers for every attribute member in the results, instead of providing sub-totals for each attribute member.

I got around this by creating a hidden dimension that contains just the customer numbers. I use that dimension for the RealPeople set. No-one will query that dimension, so I've avoided the problem described above.

Is there a better way to do this?

Darren Gosbell Darren Gosbell · Accepted Answer · 2009-11-30T00:44:27

I think the best way to do this is with simple Math. Subracting the number of this special customer from the Distinct Count measure (this only works because I am assuming that you could never have an "unsold" ticket in the same household as a sold ticket)

WITH 
  MEMBER Measures.[Num Real Households] AS Measures.[Num Households] 
                        - (Measures.[Num Households],[Customers].[CustomerNumber].&[-100])
SELECT {Measures.[Num Households], Measures.[Num Real Households]} ON 0
,[Location].[PostalCode].CHILDREN ON 1
FROM Tickets

Excluding Default Member from Measure Calculations

1 Answers