LOOKUPVALUE from lookuptable with nulls

[Anonymous]

Hi all,

I have a challenge with getting a value in my table1 from my table2.

I need to fill the DURATION column with values from table2. The only thing is, table2 has 'fallback' values, in which case it applies to everything not otherwise matched. ACTION is my key on which I always need to match.

So for example Table1 ID 3 should have duration 2:00 (action & type match, but region doesn't match, so it falls back on table2 ID 2)

Table1 ID 4 should have duration 1:50 (action, type, and region all match)

Table1

ID	ACTION	TYPE	REGION	DURATION
1	PICKUP	A	West
2	DELIVERY	B	East
3	DELIVERY	B
4	PICKUP	A	West
5	PICKUP	A

Table2

ID	ACTION	TYPE	REGION	DURATION
1	PICKUP			1:00
2	DELIVERY			2:00
3	PICKUP	A	West	1:50
4	PICKUP	A	East	3:50
5	DELIVERY	B	West	4:00

How should I approach this? Can this be done with a LOOKUPVALUE?

Thanks!

[vojtechsima · Accepted solution]

Hi, @Anonymous,
something like this should work:

CustomDuration = 

var currentID = 'Table 1'[ID]
var currentAction = 'Table 1'[ACTION]
var currentType = 'Table 1'[TYPE]
var currentRegion = 'Table 1'[REGION]

var _fullMatch = maxx(FILTER('Table 2', 'Table 2'[ACTION] = currentAction && 'Table 2'[TYPE] = currentType && 'Table 2'[REGION] = currentRegion),'Table 2'[DURATION])
var _fullMatchCheck = IF(ISBLANK(_fullMatch),MAXX(FILTER('Table 2', 'Table 2'[ACTION] = currentAction && ISBLANK('Table 2'[TYPE]) && ISBLANK('Table 2'[REGION])) ,'Table 2'[DURATION]), _fullMatch)

return _fullMatchCheck

In Table 1

[Anonymous]

You're the best, thanks! This works perfectly. I left out ID though, that doesn't need to match.

[vojtechsima]

@Anonymous 
Thank you, if you could please Kudo my answer if it was helpful. 

[Samarth_18]

Hi @Anonymous ,

Create below column in both the table:-

conct = [ID]&" "&[ACTION]&" "&[TYPE]&" "&[REGION]

Now create a column in table 1 like below:-

Column = LOOKUPVALUE(Tab2[DURATION],Tab2[conct],Tab1[conct])

Thanks,

Samarth

[Anonymous]

This was also the first idea that popped into my head, but it doesn't account for the fallback.

For example Table1 ID 3 (without ID in the conct) makes "DELIVERYB", but there will be no match from Table2 because there is no row that matches with both DELIVERY and B (and no region).