lookupvalue

[Cyriackpazhe]

Can someone help me out with the usage of two lookupvalue and how the expanded table avoids it

[v-denglli-msft]

Hi @Cyriackpazhe ,

May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster .
Thank you very much for your kind cooperation!

Best Regards,
Dengliang Li

[DataNinja777]

Hi @Cyriackpazhe ,

The first approach in the image uses two separate LOOKUPVALUE calls to retrieve the exchange rate. The first LOOKUPVALUE retrieves the CurrencyKey for "EUR" from the Currency table, which is then used in a second LOOKUPVALUE to fetch the exchange rate from the Daily Exchange Rates table based on the retrieved CurrencyKey and the Order Date from the Sales table. This method works but is less efficient because it requires multiple queries.

ExchangeRateToEUR =
VAR CurrencyKey =
    LOOKUPVALUE (
        'Currency'[CurrencyKey],
        'Currency'[Currency Code], "EUR"
    )
VAR CurrentDate = Sales[Order Date]
VAR Result =
    LOOKUPVALUE (
        'Daily Exchange Rates'[Rate],
        'Daily Exchange Rates'[CurrencyKey], CurrencyKey,
        'Daily Exchange Rates'[Date], CurrentDate
    )
RETURN
    Result

The optimized approach eliminates the need for the first LOOKUPVALUE call by leveraging the expanded table, which allows direct searching into the Currency table without first retrieving CurrencyKey. Since the expanded table includes related data, the Currency Code column from Currency can be used directly inside LOOKUPVALUE, making the query more efficient by reducing redundancy.

ExchangeRateToEUR =
VAR CurrentDate = Sales[Order Date]
VAR Result =
    LOOKUPVALUE (
        'Daily Exchange Rates'[Rate],
        'Currency'[Currency Code], "EUR",
        'Daily Exchange Rates'[Date], CurrentDate
    )
RETURN
    Result

By using the expanded table, this version avoids unnecessary lookups, allowing LOOKUPVALUE to work with the Currency Code column directly instead of first retrieving CurrencyKey. This approach simplifies the calculation and improves performance, especially when working with large datasets.

Best regards,