February 2nd, 2013, 05:49 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 1
Time spent in forums: 49 m 52 sec
Reputation Power: 0
|
|
|
Distinct Count Measure Group not distinct in MDX script
I am attempting run the following MDX query to determine the distinct amount of transaction numbers for a set including 2 dimension attributes. (POS and Concierge) When I run the query, I seem to be getting the distinct amounts for each added to together. If the same transaction number exists in both, they are both counted. However if I attempt to get the same result using an Excel Pivot table, I get the desired transaction number count. Could anyone help me with getting this result in the MDX query?
Code:
WITH
SET [Week] AS StrToMember("[Date].[Hierarchy - Fiscal].[Fiscal Week].&[2012]&[48]")
SET [Store] AS {[Financial Transaction Channel].[Transaction Channel Desc].&[POS],
[Financial Transaction Channel].[Transaction Channel Desc].&[CONCIERGE]}
MEMBER [Store Transactions With Returns] AS SUM([Week] * [Store], [Measures].[Operational Transactions Count])
SELECT
{
[Store Transactions With Returns]
} ON COLUMNS,
{
[Week]
} ON ROWS
FROM EDW
Results: Store Transactions With Returns
Wk. 48 - 2012 78,636
The same attempt using an Excel Pivot table yields this:
Code:
Row Labels Operational Transactions Count
Year 2012 78,368
Half 2 - 2012 78,368
Qtr. 4 - 2012 78,368
December - 2012 78,368
Wk. 48 - 2012 78,368 78,636
1 US STORES 78,368
CONCIERGE 3,668
POS 74,968
Grand Total 78,368
If you add the POS and Concierge amounts, you get 78,636. Which is the same result I am getting from my MDX query, but after I went through the transactions line by line, I was able to determine that 78,368 is the correct amount of distinct transaction numbers.
Any help on this would be greatly appreciated.
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
