Forum Discussion
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 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
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
2 Replies
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.
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
