Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
You're the best, thanks! This works perfectly. I left out ID though, that doesn't need to match.
@Anonymous
Thank you, if you could please Kudo my answer if it was helpful.
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |