LOOKUPVALUE not working for one column but works for another

[RobertasUC]

Hello,

Could anyone please advise, why LOOKUPVALUE works using one column, but not another one from the same table?

Example,

LOOKUPVALUE(Financing[Loan #],Financing[Loan #],'Interest Expense 2024'[Reference]) returns values as requested.

When i try to add column [TYPE] to be returned it results into: "A table of multiple values was supplied where a single value was expected."

LOOKUPVALUE(Financing[Type],Financing[Loan #],'Interest Expense 2024'[Reference])

[Anonymous · Accepted solution]

Hi, @RobertasUC 

As @Greg_Deckler  mentioned, there are duplicate values, causing you this error. I use the following sample data to explain why the LOOKUPVALUE function does not work properly:

Exchange rate table:

Order table:

When I want to get the rate from the Exchange rate table to the Order table, use the following DAX expression:

rate = LOOKUPVALUE('Exchange rate'[Rate],'Exchange rate'[Foreign currency type],'Order'[Foreign currency type])

The results are as follows:

The reason for reporting this error is that the rate corresponding to dollar in my Exchange rate table has two different values in July, but now I want to put the rate into the Order table. Lookupvalue doesn’t know which value to choose:

Lookupvalue is usually not good at this situation. You can solve the problem by combining calculate with filter. Here's how I solved this problem:

Rate1 = 
VAR _currency='Order'[Foreign currency type]
VAR _yearmonth='Order'[Year &Month]
RETURN
CALCULATE(
    AVERAGE('Exchange rate'[Rate]),
    FILTER('Exchange rate',
    'Exchange rate'[Foreign currency type]=_currency&&'Exchange rate'[Year & Month]=_yearmonth)
)

Aggregate the rate in the Exchange table by average. If there is only one rate in the corresponding month and currency type, this unique rate is returned; if there are multiple values in the search result, the average of the multiple values is returned.

Based on your description, I tried to write a possible DAX expression for you, you can try:

Type =
VAR _type = 'Interest Expense 2024'[Reference]
RETURN
    CALCULATE (
        MAX ( Financing[Type] ),
        FILTER ( 'Financing', Financing[Loan #] = _type )
    )

You can adjust this DAX expression specifically according to your actual situation. I hope it will be helpful to you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@RobertasUC It's likely that you have duplicate row values and LOOKUPVALUE doesn't like that. Try using MAXX(FILTER(...), [column]) instead.