lookupvalue

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,