Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Look Up Value with Fallback result

Hi All,    I am trying to create a measure that looks up a value in a lookup table based on a combination of date and region. If that combination doesn't exist in the lookup table, then it should u...
Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    Anonymous here is the fix and the output:

     

    LookupValueWithFallback = 
VAR CurrentDate = MAX('Combination Table'[Date]) // Replace with your actual date column
VAR CurrentRegion = MAX('Combination Table'[Region]) // Replace with your actual region column

VAR LookupResult =
    CALCULATE(
        MAX('Lookup Table'[Rolling Headcount]),
        FILTER(
            'Lookup Table',
            'Lookup Table'[Date] = CurrentDate &&
            'Lookup Table'[Region] = CurrentRegion
        )
    )
VAR FallbackDate = EOMONTH(CurrentDate, -2) + 1
VAR FallbackResult =
    CALCULATE(
        MAX('Lookup Table'[Rolling Headcount]),
        FILTER(
            'Lookup Table',
            'Lookup Table'[Date]= FallbackDate &&
            'Lookup Table'[Region]= CurrentRegion
        )
    )

RETURN
    IF(
        NOT ISBLANK(LookupResult),
        LookupResult,
        FallbackResult
    )

     

     

parry2k's avatar
parry2k
Super User
2 years ago

Anonymous here is the fix and the output:

 

LookupValueWithFallback = 
VAR CurrentDate = MAX('Combination Table'[Date]) // Replace with your actual date column
VAR CurrentRegion = MAX('Combination Table'[Region]) // Replace with your actual region column

VAR LookupResult =
    CALCULATE(
        MAX('Lookup Table'[Rolling Headcount]),
        FILTER(
            'Lookup Table',
            'Lookup Table'[Date] = CurrentDate &&
            'Lookup Table'[Region] = CurrentRegion
        )
    )
VAR FallbackDate = EOMONTH(CurrentDate, -2) + 1
VAR FallbackResult =
    CALCULATE(
        MAX('Lookup Table'[Rolling Headcount]),
        FILTER(
            'Lookup Table',
            'Lookup Table'[Date]= FallbackDate &&
            'Lookup Table'[Region]= CurrentRegion
        )
    )

RETURN
    IF(
        NOT ISBLANK(LookupResult),
        LookupResult,
        FallbackResult
    )

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you.