Create multiple sets and union them in MDX

All replies

• 

  

  0

  Sign in to vote

  So if you want the .CurrentMember to be affected by the filters on [DD Date] and [Cash Loads Date] you should put them into the WHERE clause not into a subselect as subselects do not alter the currentmember context.

  eg

  With SET [SetA]

   AS EXISTS([Card].[Card ID].[Card ID], ([DD Date].[Calendar Hierarchy].Currentmember , 
  [Cash Loads Date].[Calendar Hierarchy].[All])
  ,"Transactions")
  SET [SetB]
   AS EXISTS([Card].[Card ID].[Card ID], ([Cash Loads Date].[Calendar Hierarchy].Currentmember , 
  [DD Date].[Calendar Hierarchy].[All])
  ,"Transactions")
  select NULL on 0,
  [setA] + [SetB] on 1
  from AmexServe
  WHERE
  (
  [DD Date].[Calendar Hierarchy].[Date].&[20200621],
  [Cash Loads Date].[Calendar Hierarchy].[Date].&[20200624]
  ) 
  

  ---

  http://darren.gosbell.com - please mark correct answers

  • Proposed as answer by Lukas DreiKleinMicrosoft contingent staff Monday, August 17, 2020 6:49 AM
  • Marked as answer by Prajwal Potula Monday, August 24, 2020 6:21 AM

  Sunday, August 16, 2020 11:05 PM
• 

  

  0

  Sign in to vote

  Thanks for the reply Lukas DreiKlein. 

  Darren, 

  what you wrote making sense. I want to create this as a calculated measure in the cube. Finally, want to get a distinct count of cards from [Set A] and [Set B]. Can I use something like below or any other suggestion? and requirement is, this cube will be browsed from excel 

  With [Set A] as XXXXXX

  [Set B] as xxxxx

  member [Measures].[X] as count( exists([Card].[Card].[Card],exists([card].[card].[card],[Set A],'Transactions'),'Transactions'))

  is this correct? please advise. 

  thanks in advance. 

  ---

  prajwal kumar potula

  Monday, August 17, 2020 2:32 PM
• 

  

  0

  Sign in to vote

  In Excel you don't define sets in the WITH clause, but if you create the set as a DYNAMIC set you will get similar behaviour and dynamic sets have the added bonus that they get filtered both by the WHERE and any sub-selects which is good as Excel often injects filters using the sub-select syntax.

  What is the difference between the [Card] and [Card ID] attributes? If it's a one to one relationship and one Card has one Card ID then you could just do COUNT( [SetA] ) if [Set A] was declared as a dynamic set in your cube.

   

  ---

  http://darren.gosbell.com - please mark correct answers

  Monday, August 17, 2020 10:06 PM
• 

  

  0

  Sign in to vote

  Hi Darren, thanks, I created two sets in cube. I am trying to browse the cube from excel. when I selected DD Date in filter and [DDSet] on rows , i see a card. But, when I seleted Debit Loads Date in filter, 
  I dont see anything. Theoretically, Debit Loads Date should not affect the DDSet.
  
  CREATE DYNAMIC SET CURRENTCUBE.[DDSet] AS
  EXISTS([Card].[Card ID].[Card ID], ( [DD Date].[Calendar Hierarchy].Currentmember
       ,[Debit Loads Date].[Calendar Hierarchy].[All]),"Transactions"); 
  
  CREATE DYNAMIC SET CURRENTCUBE.[DebitLoadsSet] AS
  EXISTS([Card].[Card ID].[Card ID], ([DD Date].[Calendar Hierarchy].[All] 
  ,[Debit Loads Date].[Calendar Hierarchy].Currentmember),"Transactions"); 
  
  My ultimate goal is count of cards from 2 sets. 
  
  CREATE DYNAMIC SET CURRENTCUBE.[AllCards]
   AS [DDSet]+[DebitLoadsSet] ; 
  
  CREATE MEMBER CURRENTCUBE.[Measures].[TotalCards]
   AS Count([AllCards]) 

  

               

  ---

  prajwal kumar potula

  
  

  • Edited by Prajwal Potula Tuesday, August 18, 2020 7:10 PM

  Tuesday, August 18, 2020 7:09 PM
• 

  

  0

  Sign in to vote

  It's hard to say without access to the model, but it's possible that the default measure is causing this. The NON EMPTY that Excel inserts before the axis will force the default measure to be used since there is not other measure specified in the query and if that measure returns an empty value for the filter condition you will not see any rows.

  ---

  http://darren.gosbell.com - please mark correct answers

  Wednesday, August 19, 2020 5:35 AM
• 

  

  0

  Sign in to vote

  Thanks Darren, 

  I got a completely different question. I am browsing a cube from excel. applied few filters and browsed sales. my question is, how to capture the filter values and load into another tab in the Excel? Please let me know if you have any suggestions.  Thanks in advance. 

  ---

  prajwal kumar potula

  Monday, August 24, 2020 6:22 AM
• 

  

  0

  Sign in to vote

   my question is, how to capture the filter values and load into another tab in the Excel? Please let me know if you have any suggestions.  Thanks in advance. 
  

  So you should be able to either right click on the tab and copy the whole tab. Or you can highlight the whole pivot table and copy and paste it into another sheet and it should retain the connection and filters

  ---

  http://darren.gosbell.com - please mark correct answers

  Tuesday, August 25, 2020 1:56 AM