Forum Discussion

Sdg8481's avatar
Sdg8481
Helper I
8 years ago
Solved

Using a Measure in a lookupvalue

Hi all, i've tried searching for an answer on the internet for this but no joy, so wondering if someone might be able to advise.   Basically, I have the following two data items built into the same...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Sdg8481,

     

    The correct syntax for LOOKUPVALUE should be:

    LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)

     

    As we can see, the returned value is a column rather than a measure. So, we cannot refer to a measure in LOOKUPVALUE function.

     

    In your scenario, rather than create a measure using IF statement, why don't you create a calculated column:

    Column: new = IF('Main'[Combined Metadata.Value type] = "Percentage", <Average expression>, <Sum expression>)

     

    Then, refer to above new column in LOOKUPVALUE.

    Column:  Prior Yr Value = LOOKUPVALUE(Main[new],Main[Measure_Date Key],Main[Measure_EquivDate Key])

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Sdg8481,

 

The correct syntax for LOOKUPVALUE should be:

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)

 

As we can see, the returned value is a column rather than a measure. So, we cannot refer to a measure in LOOKUPVALUE function.

 

In your scenario, rather than create a measure using IF statement, why don't you create a calculated column:

Column: new = IF('Main'[Combined Metadata.Value type] = "Percentage", <Average expression>, <Sum expression>)

 

Then, refer to above new column in LOOKUPVALUE.

Column:  Prior Yr Value = LOOKUPVALUE(Main[new],Main[Measure_Date Key],Main[Measure_EquivDate Key])

 

Best regards,

Yuliana Gu