Distinct Count Measure Group not distinct in MDX script
Results 1 to 2 of 2

Thread: Distinct Count Measure Group not distinct in MDX script

  1. #1
    Join Date
    Feb 2013
    Posts
    1

    Question 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:

    pivot.png

    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.

  2. #2
    Join Date
    Jan 2014
    Posts
    1
    lol.................................

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •