Using a Measure in a lookupvalue

[Sdg8481]

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 table:

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

Measure: new = IF(MAX('Main'[Combined Metadata.Value type]) = "Percentage", AVERAGE('Main'[Value]), SUM('Main'[Value]))

The lookupvalue is finding the value from from the previous year (equivalent date). However, if a the value type is  a % then i need it to average and not sum. I can now do this for the Value column by using a measure, but i'm not sure how to combine the two so that it also does it in the LOOKUPVALUE, as i don't appear to be able to reference the measure in my lookup.

Logically in my head it would be either of these statements, but these cause the following errors :

Measure 2 = LOOKUPVALUE('Main'[new)], Main[Measure_Date Key], Main[Measure_EquivDate Key])

ERROR: a single value for column 'Measure_EquivDate' in table 'Main' connot be determined.

Or at least

LOOKUPVALUE(

(IF(MAX('Main'[Combined Metadata.Value type]) = "Percentage", AVERAGE('Main'[Value]),SUM('Main'[Value]))),

Main[Measure_Date Key],

Main[Measure_EquivDate Key])

ERROR: Function LOOKUPVALUE expects a column reference as argument number 1.

Many Thanks

S

[v-yulgu-msft · Accepted solution]

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]

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.