Get All Linked Members using LinkMember function

Question

Please consider this structure:

Fact:

Sender           Receiver         Amount
----------------------------------------
Reseller1        Resseler2        100 
Reseller1        Resseler3        200
Reseller3        Resseler1        150
Reseller3        Resseler2        50
Reseller1        Resseler4        100
Reseller4        Resseler2        350
Reseller4        Resseler1        500
Reseller4        Resseler3        250

DimReseller:

ResellerCode            ResellerName
---------------------------------------
1                        Reseller1
2                        Reseller2
3                        Reseller3
4                        Reseller4

DimReseller is role-playing dimension and has 2 relation with fact table:

Now I wrote this MDX query to select How much money the resellers have paid each other:

WITH MEMBER SenderAmount AS
[Measures].[Amount], FORMAT_STRING = "Standard"


SELECT    {SenderAmount} ON 0,
NON EMPTY [Sender].[Hierarchy].MEMBERS ON 1
FROM [MyCube]

and I get this result:

Now I want to get how much money the resellers have received from each other and I wrote this query:

WITH 
MEMBER SenderAmount AS
[Measures].[Amount], FORMAT_STRING = "Standard"

MEMBER ReceiverAmount AS
  (LinkMember   
     ([Sender].[Hierarchy].CurrentMember, [Receiver].[Hierarchy])  
     ,[Measures].[Amount]), FORMAT_STRING = "Standard"


SELECT    {SenderAmount, ReceiverAmount} ON 0,
NON EMPTY [Sender].[Hierarchy].MEMBERS ON 1
FROM [MyCube]

and get this result:

I got null for receive amount. I test this MDX query:

select {LinkMember([Sender].[Hierarchy].[Reseller Code].&[1], [Receiver].[Hierarchy])} on 0
from [MyCube]

and I get 650 (the correct amount) for Reseller1 .Now when I change the query this way:

select {LinkMember([Sender].[Hierarchy].currentmember, [Receiver].[Hierarchy])} on 0,
[sender].[Hierarchy].members on 1
from [MyCube]

I get this result(Again for paid):

and when I change the query this way:

select {LinkMember([Sender].[Hierarchy].currentmember, [Receiver].[Hierarchy])} on 0,
[receiver].[Hierarchy].members on 1
from [MyCube]

I got this error:

The Hierarchy hierarchy already appears in the Axis0 axis.

How can I get this result?

Reseller              Received                   Paind                     
--------------------------------------------------------
Reseller1             650                        400
Reseller2             500                        (null)
Reseller3             450                        200
Reseller4             100                        1100

Sorry for too much explanation...

Thanks

score 3 · Accepted Answer · answered Dec 21 '19 at 16:47

3

Try the following change:

MEMBER ReceiverAmount AS
  (LinkMember   
     ([Sender].[Hierarchy].CurrentMember, [Receiver].[Hierarchy])  
     ,[Measures].[Amount]
     ,[Sender].[Hierarchy].[All]), FORMAT_STRING = "Standard"

answered Dec 21 '19 at 16:47

GregGalloway

11,355
3
16
47

Thanks I'll check it. But I want all members not just `[All]` member – Arian Dec 22 '19 at 04:19
That worksssssssss. Thanks alot. Can you explain more why my query didn't work? – Arian Dec 22 '19 at 04:37
1

@Arian you notice that the sender and receiver are never the same. Your formula was setting the context on receiver using LinkMember without clearing the context on sender. My mention of the All sender does that. – GregGalloway Dec 22 '19 at 12:58
You save my job bro – Arian Dec 22 '19 at 13:14

Get All Linked Members using LinkMember function

1 Answers1