Using RELATED with a Measure

[jack10063]

Hi,

This is PowerPivot newbie question so apologize for that. I have defined a measure that returns an integer. However, I'd like it to return a string according to a table below:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]ratingCode[/TD]
[TD]ratingDescription[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]BEST [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]GOOD[/TD]
[/TR]
</TBODY>[/TABLE]


I have created this table in the PowerPivot model. So, currently my measure returns a value 2 (ratingCode) but I'd like it to return the ratingDescription "GOOD".

How should I do this?

br,
Jack

 

[MD610]

There are a few ways to accomplish that. As far as which one is best, really depends on what your measure actually is, the context of the pivot in which it is being used, the slicers you are usings, etc...

However, if you don't have too many rating codes, a simple solution may be to use a SWITCH() that looks at your current measure. Something like this:

New Measure:=SWITCH([YourMeasure], 1, "BEST", 2, "GOOD")

 

[jack10063]

Thanks a lot for your answer. SWITCH seems quite handy indeed if there arent too many codes. I have approximately 20 and thus it would be great to use some kind of a lookup function.

br,
Jack

 

[MD610]

Try LOOKUPVALUE().

New Measure:=LOOKUPVALUE(Table1[ratingDescription], Table1[ratingCode], [YourMeasure])

 

[jack10063]

Thanks a lot, that's what I was looking for!

br,
Jack