Hi everyone. In my fact table I have numerous transaction type records each related to a specific customer via their account number. I can sum the results of their transactions, but when I try to create a measure that's a DistinctCount of their account numbers (to see how many there are by type and period) via the pulldown in the properties box in the Cube Editor, I get an Excel '#VALUE' type error in the totals when I deselect any of the members of a Dimension. It totals correctly only when all members are selected, so I'm looking to do a calculated member instead - maybe it'll help???
I've created numerous calculated members where its simply 'Sales$ / Customers', but when I try to incorporate "DistinctCount([Measures].[Acct])" the syntax checker gives me a Syntax error near the closing parenthesis. Selecting DistinctCount from the Function List produces a "<<Set>>" template and when I double click on my Acct measure it fits nicely inside automatically, but then it bombs on the syntax check.
The book I have shows using an MDX example to create a SPECIFIC resultset, but I need this to be calculated on the fly as the user interacts with the pivot table - not with fixed columns or rows. Does anyone know the syntax to use the Distinct Count function of a Measure within the Calculated Member Builder? Thanks!
I've created numerous calculated members where its simply 'Sales$ / Customers', but when I try to incorporate "DistinctCount([Measures].[Acct])" the syntax checker gives me a Syntax error near the closing parenthesis. Selecting DistinctCount from the Function List produces a "<<Set>>" template and when I double click on my Acct measure it fits nicely inside automatically, but then it bombs on the syntax check.
The book I have shows using an MDX example to create a SPECIFIC resultset, but I need this to be calculated on the fly as the user interacts with the pivot table - not with fixed columns or rows. Does anyone know the syntax to use the Distinct Count function of a Measure within the Calculated Member Builder? Thanks!