Lookup Function to return max and or first occursance

[Invesco]

Hi Experts

When using the following measure 

SLSL Name = LOOKUPVALUE(shc_shingrix_activity_volume_and_cf_days[slsl_name],shc_shingrix_activity_volume_and_cf_days[crm_call_name],dev_shc_shingrix_activity[crm_call_name])

I am getting the following error message: A table of multiple values was supplied where a single value was expected.

How can i modify the measure to return the either the max and or the first occurance, 

[Anonymous · Accepted solution]

Hi @Invesco,

lookupvalue function are used to search for values, the results can be single or list of values which can’t be stored in measure(measure only support aggregate values).
For this scenario, you need to add expression to convert them to single value at first:

SLSL Name =
LOOKUPVALUE (
    shc_shingrix_activity_volume_and_cf_days[slsl_name],
    shc_shingrix_activity_volume_and_cf_days[crm_call_name], dev_shc_shingrix_activity[crm_call_name],
    CONCATENATEX (
        VALUES ( shc_shingrix_activity_volume_and_cf_days[slsl_name] ),
        [slsl_name],
        ","
    )
)

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

Regards,

Xiaoxin Sheng