Calculation based on not the primary attribute of the dimension

Question

Calculation based on not the primary attribute of the dimension

I have a dimension with two attributes and a fact table. The dimension is linked to the fact table by the primary attribute. Attributes are of the same nature, i.e. the list of possible values is the same. In fact, these are related codes.

Now I can get from the cube next result split by both attributes:

Dim.Item    Dim.LinkedItem  Facts.Amount

Item1       Item3           10

Item2       Item3           20

Item3       Item2           50

Or like this if I use the second attribute:

Dim.LinkedItem  Facts.Amount

Item2           50

Item3           30

I need a calculation that will allow me to get the next result:

Dim.Item    Dim.LinkedItem  LinkedAmount

Item1       Item3           0

Item2       Item3           50

Item3       Item2           30

In other words, for each value of the main attribute I should get the sum from the fact table, where this value acts as the second attribute.

Please help to write MDX formula for this calculation.

Answer 1

You need to use "strtoval","currentmember" and "defaultmember" . Your Measure defination will be as below

with member 
measures.t 
as 
strtoval('(Dim.Item.defaultmember,[Product].[Category].['+Dim.LinkedItem.currentmember.name+'],Facts.Amount)')

Answer 2

MoazRub,

Thank you very much for your answer! But I still didn't get the correct result. To clarify my case, I will use queries with real examples.

(1) This query returns NULL and this is correct:

select Time.Year.[Calendar 2018] on 0,
[Item].[Item Code].F268955709 on 1
from [SEL DEMO] where (Measures.[SellIn - Qty])

(2) This returns correct value for Linked Item:

select Time.Year.[Calendar 2018] on 0,
[Item].[Linked Item].F268955709 on 1
from [SEL DEMO] where (Measures.[SellIn - Qty])

(3) I try to use your advice, but get value for all items:

with member Measures.[SellIn LI] as 
StrToValue('( Measures.[SellIn - Qty], [Item].[Item Code].DefaultMember, [' + [Item].[Linked Item].CurrentMember.Name + '])')
select crossjoin ([Item].[Item Code].F268955709,
                  Measures.[SellIn LI]) on 0
                  from [SEL DEMO] where (Time.Year.[Calendar 2018])

I tried using string StrToValue('( Measures.[SellIn - Qty], [Item].[Item Code].DefaultMember, [' + [Item].[Linked Item].CurrentMember.Name + '])') as expression for calculation in SSAS cube and get value for all items too.

I replaced code on the same but without StrToValue and get the same value...

with member Measures.[SellIn LI] as 
'([Item].[Item Code].DefaultMember, [Item].[Linked Item].CurrentMember, Measures.[SellIn - Qty])'
select crossjoin ([Item].[Item Code].F268955709,
                  Measures.[SellIn LI]) on 0
                  from [SEL DEMO] where (Time.Year.[Calendar 2018])

Maybe there are some other ideas? I really appreciate your help.