MDX Query : How I can show dummy set in query result if it is not related to other dimension?

Question

Need your help to resolve one issue in client report.

Please check the result of of 2 mdx queries below.

Note : I used Union operator so that I can merge result of 2 members with 1 (France and Germany combined). Use Exist function in Union because need to check first set in function if it related to dimension member of second set.

I used Union with Exist to implement OR condition.

In first MDX query you can find dummy set (France & Germany) in result-set but in second MDX you can't find dummy (France & Germany) set because Product Category hierarchy is related to country and Exists function check that dummy set is not a actual member of Country hierarchy so it remove dummy set from the result-set.

Is there any way or alternate approach so that I can force exists function to show dummy set in final result (mDX 2)?

MDX 1)

WITH 
    SET [Combined] AS {
        [Customer].[Customer Geography].[Country].&[France], 
        [Customer].[Customer Geography].[Country].&[Germany]
    }
    MEMBER [Customer].[Customer Geography].[France & Germany] AS Aggregate([Combined])
SELECT
    [Measures].[Internet Sales Amount] ON 0,
    Union(
        Except([Customer].[Customer Geography].[Country], [Combined]),
        [Customer].[Customer Geography].[France & Germany]
    ) ON 1
FROM [Adventure Works]

Result MDX 1

Internet Sales Amount
Australia   $9,061,000.58
Canada  $1,977,844.86
United Kingdom  $3,391,712.21
United States   $9,389,789.51
France & Germany    $5,538,330.05

MDX Query 2)

WITH 
    SET [Combined] AS {
        [Customer].[Customer Geography].[Country].&[France], 
        [Customer].[Customer Geography].[Country].&[Germany]
    }
    MEMBER [Customer].[Customer Geography].[France & Germany] AS Aggregate([Combined])
SELECT
    [Measures].[Internet Sales Amount] ON 0,

       Union( Exists(Except([Customer].[Customer Geography].[Country], [Combined]),
        {[Product].[Product Categories].[Category].&[3]}  
        , "Internet Sales") ,

      Exists([Customer].[Customer Geography].[France & Germany],{[Product].[Product Categories].[Category].&[3]}  
     , "Internet Sales"))

     ON 1
FROM [Adventure Works]

Result MDX query 2)

Internet Sales Amount
Australia        $9,061,000.58
Canada          $1,977,844.86
United Kingdom  $3,391,712.21
United States   $9,389,789.51

whytheq whytheq · Accepted Answer · 2017-08-20T23:06:16

Why do you need to use exists twice in the 2nd query?

WITH 
    SET [Combined] AS {
        [Customer].[Customer Geography].[Country].&[France], 
        [Customer].[Customer Geography].[Country].&[Germany]
    }
    MEMBER [Customer].[Customer Geography].[France & Germany] AS Aggregate([Combined])
SELECT
    [Measures].[Internet Sales Amount] ON 0,

       Union( 
         Exists(
           Except([Customer].[Customer Geography].[Country], [Combined]),
           {[Product].[Product Categories].[Category].&[3]}  
          ,"Internet Sales"
         )

        //,Exists(
        //   [Customer].[Customer Geography].[France & Germany]
        // ,{[Product].[Product Categories].[Category].&[3]}  
        // ,"Internet Sales"
        // )

        ,[Customer].[Customer Geography].[France & Germany]

       )    
     ON 1
FROM [Adventure Works];

MDX Query : How I can show dummy set in query result if it is not related to other dimension?

1 Answers