PowerPivot RELATED function with dynamic column reference

[tryamamoto]

Is it possible to create a calculated column in a PowerPivot table using the RELATED function and a dynamic column reference? In Excel, I used an Index-Match arrangement to pull the appropriate category from my dimension matrix, but have been unable to adapt this particular requirement to PowerPivot.

Here's an example of what I am trying to acheive.

If I use the formula '=RELATED(DIM[Text1])', CalcColumn returns the correct value of "AA" for row1 and "CC" for row3; however, the problem of course is that the "Text1" column of the DIM table is referenced for all rows, which does not produce the desired result (Returns BB and DD instead of FF and NN respectively in the illustration above). What I need is a way (appropriate syntax) to reference the CODE column in the formula for CalcColumn (e.g. '=RELATED(DIM[FACT

]') so that CalcColumn will look to a different DIM column based on the value in the CODE column.
 
I'm thinking I might be trying to make PowerPivot do something it isn't intended for, but, if there is a way to make this work in PowerPivot, any help to point me in the right direction would be most appreciatied.
 
Thanks,
 
Tak

 

[benvans]

This doesn't use the related function, but I had a similar problem and was able to use this solution very effectively:
http://social.msdn.microsoft.com/Fo...737-8306-4e26-bcbd-382414072541?prof=required