CODEWITHSUNDEEP
sql-serverssasmdxolapcube
mohan111
mohan111

Reputation: 8865

How to get combination of Measure count using same dimension

how can i get all the combination of Dimensions Measure count in MDX. In the Below image I'm getting individual value measure count but how can i get combination of values measure count.Say for example i have got the individual value count for 262,210 and 198 DrKey Dimension.I would need value count combination like 1) 262 and 210 along with measure count 2) 262 and 198along with measure count 3) 198and 210 along with measure count 4) 262,210 and 198 along with measure count enter image description here

My below query : 

WITH 
  MEMBER DrKey AS 
    [DimPopulation].[Population Key].CurrentMember.Member_Key 
  MEMBER [Measures].[DrList] AS 
    NonEmpty
    (
      {[DimPopulation].[Population Key].&[262], [DimPopulation].[Population Key].&[210]}
     ,[Measures].[DrPatientKeyCnt]
    ) 
SELECT 
  {
    DrKey
   ,[Measures].[DrPatientKeyCnt]
   ,DrList
  } ON COLUMNS
 ,NonEmpty
  (
    {
      [DimPopulation].[Population Key].&[262]
     ,[DimPopulation].[Population Key].&[210]
     ,[DimPopulation].[Population Key].&[198]
    }
   ,[Measures].[DrPatientKeyCnt]
  ) ON ROWS
FROM [abc]
WHERE 
  (
    [DimAnchorDate].[Date Key].&[20141031]
   ,[DimReport].[Report Key].&[1]
  );

Upvotes: 1

Views: 197

Answers (2)

SouravA
SouravA

Reputation: 5243

Is it still showing error?

//WITH 
  //MEMBER DrKey AS 
    //[DimPopulation].[Population Key].CurrentMember.Member_Key 

WITH
 MEMBER [Measures].[DrList] AS 
    NonEmpty
    (
      {[DimPopulation].[Population Key].&[262], [DimPopulation].[Population Key].&[210]}
     ,[Measures].[DrPatientKeyCnt]
    ) 


SELECT 
  {
    //DrKey,
   [Measures].[DrPatientKeyCnt]
   ,DrList
  } ON COLUMNS
 ,NonEmpty
  (
    {
      [DimPopulation].[Population Key].&[262]
     ,[DimPopulation].[Population Key].&[210]
     ,[DimPopulation].[Population Key].&[198]
    }
   ,[Measures].[DrPatientKeyCnt]
  ) ON ROWS
FROM [abc]
WHERE 
  (
    [DimAnchorDate].[Date Key].&[20141031]
   ,[DimReport].[Report Key].&[1]
  );

Upvotes: 1

whytheq
whytheq

Reputation: 35557

Try just using aggregate function around the tuple:

WITH 
  MEMBER DrKey AS 
    [DimPopulation].[Population Key].CurrentMember.Member_Key 
  MEMBER [Measures].[DrList] AS 
    AGGREGATE(        
      NonEmpty
      (
      {[DimPopulation].[Population Key].&[262], [DimPopulation].[Population Key].&[210]}
     ,[Measures].[DrPatientKeyCnt]
      ) 
    )
SELECT 
  {
    DrKey
   ,[Measures].[DrPatientKeyCnt]
   ,DrList
  } ON COLUMNS
 ,NonEmpty
  (
    {
      [DimPopulation].[Population Key].&[262]
     ,[DimPopulation].[Population Key].&[210]
     ,[DimPopulation].[Population Key].&[198]
    }
   ,[Measures].[DrPatientKeyCnt]
  ) ON ROWS
FROM [abc]
WHERE 
  (
    [DimAnchorDate].[Date Key].&[20141031]
   ,[DimReport].[Report Key].&[1]
  );

Upvotes: 0

Related Questions