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

Floriankx's avatar
Floriankx
Solution Sage
8 years ago

Hello,

 

it seems quite complicated with LOOKUPVALUE.

Maybe you should check out SAMEPERIODLASTEYEAR or DATEADD, if you have a contigous Date Column.

 

Apart from that I think you have to create two measures.

Measure 1: SUMX(Main,If(LookupDateType="Percentage",BLANK(),Main[Value]).

Measure 2: AVERAGEX(Main,If(LookupDateType<>"Percentage",BLANK(),Main[Value]).

 

LookupDateType is your LOOKUPVALUE Measure for DateType.

 

Afterwards you can add these Measures in a separate Measure if you like to have it in one.

 

For further support you should provide the structure of your table and your expected result.