Lookupvalue no longer working - A table of multiple values was supplied error

[mdex]

After my latest data refresh one of my formula is no longer working. DTO Rates hasn't changed but we have more lines in TRSBookings

It's probably overcomplicated but I want to compare the custom columns from 2 TRSBookings and DTO Rates then return the DTO Rates[Rate], if the custom doesn't provide a match then compare only the TRS/Request_No, if no match on either the value should be blank/0/null.

DTORate = if(IFERROR(LOOKUPVALUE('DTO Rates'[Rate],'DTO Rates'[Custom],TRSBookings[Custom]),LOOKUPVALUE('DTO Rates'[Rate],'DTO Rates'[TRS],TRSBookings[REQUEST_NO])) =0,LOOKUPVALUE('DTO Rates'[Rate],'DTO Rates'[TRS],TRSBookings[REQUEST_NO]),LOOKUPVALUE('DTO Rates'[Rate],'DTO Rates'[Custom],TRSBookings[Custom]))

Example data

TRSBookings

Request_No	Team_ID	Custom	DTORate
R042258	1100	R042258 1100	715.62
R041506	1192	R041506 1192	533.89
R041506	1198	R041506 1198	408.5
R041101	1198	R041101 1198
R041506	1192	R041506 1192	533.89

DTO Rates

TRS	Team_ID	Custom	Rate
R042258			715.62
R041506	1192	R041506 1192	533.89
R041506	1198	R041506 1198	408.5

Could someone please help or point me in the right direction?

[amitchandak · Accepted solution]

@mdex , create a new column in TRSBookings

maxx(filter('DTO Rates', 'DTO Rates'[TRS]= TRSBookings[REQUEST_NO && 'DTO Rates'[Custom] =TRSBookings[Custom] && 'DTO Rates'[Team_ID] =TRSBookings[Team_ID]),'DTO Rates'[Rate])

if need you can remove Team_ID if not needed

[mdex]

Hi Amit,

Thanks for taking a look. I realise there was a slight discrepany with my example data. DTO Rate column is being calculated in TRSBookings I incorrectly had this in the table as rate.

The rate for R042258 was also missing from both tables. The view you see if my expected result.

Your formula works ok where custom columns match, but if no custom match then just the TRS/Request_NO should be compared.

Hope this makes sense.

EDIT: Think I have it!

DTORate = IF(maxx(filter('DTO Rates', 'DTO Rates'[Custom] =TRSBookings[Custom]),'DTO Rates'[Rate]) = 0,maxx(filter('DTO Rates', 'DTO Rates'[TRS]= TRSBookings[REQUEST_NO] ),'DTO Rates'[Rate]),maxx(filter('DTO Rates', 'DTO Rates'[Custom] =TRSBookings[Custom]),'DTO Rates'[Rate]))