CODEWITHSUNDEEP
ssasmdx
user330612
user330612

Reputation: 2239

How to filter a dimension hierarchy based on another dimension hierarchy in mdx

I have a sender and recipient dimensions which are role playing dimensions out of employee physical table. My fact table has sender, recipient ,messages columns. I want to get messages sent from a employee to everyone in the company except those reporting to his manager. I tried something like this

WITH 
  SET [Others] AS 
    Except
    (
      Ascendants([Recipient].[Manager])
     ,[Sender].[Manager].Parent
    ) 
SELECT 
  [OTHERS] ON COLUMNS
 ,{[Measures].[Messages]} ON ROWS
FROM [cube]
WHERE 
  [Sender].[Manager].&[xyz];

Basic idea is..get all ascendants of the recipients of a given sender and filter those whose ascendant list consists of senders parent.

This doesn't work because I can't do except between two different dimension hierarchies.

Upvotes: 0

Views: 2479

Answers (1)

whytheq
whytheq

Reputation: 35557

Try making the Set more context aware via the keyword EXSITING and then use Filter to compare member_caption

WITH 
  MEMBER [Measures].[SenderName] AS 
    [Sender].CurrentMember.Member_Caption 
  SET [ExistingRecip] AS 
    (EXISTING 
      [Recipient].[Manager].MEMBERS) 
  SET [Others] AS 
    Filter
    (
      [ExistingRecip]
     ,
        [ExistingRecip].Item(
        [ExistingRecip].CurrentOrdinal - 1).Member_Caption
      <> 
        [Measures].[SenderName]
    ) 
SELECT 
  [OTHERS] ON COLUMNS
 ,{[Measures].[Messages]} ON ROWS
FROM [cube]
WHERE 
  [Sender].[Manager].&[xyz];

Upvotes: 1

Related Questions