Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I want to match two column in both tables from data to key and return the cost column into the data table.
I have a key table
Type Type 2 Cost
40 Half 80
40 Full 150
20 Full 40
20 Half 90
I have a data table
It lists similar to the key but doesnt have cost. I would like to match the two columns from the key table and return the cost in the data table.
Type Type 2
40 Half
20 Half
40 Full
etc etc
Output : In Data Table
Type Type 2 Cost
40 Half 80
20 Half 40
40 Full 150
20 Full 90
I have a relationship between Key Table - Type ( Many to Many) to Data Table - Type.
Solved! Go to Solution.
You could add a column onto the Data table like
Cost = LOOKUPVALUE( 'Key'[Cost], 'Key'[Type], 'Data'[Type], 'Key'[Type 2], 'Data'[Type 2])
@Anonymous
You could create a new column that is 'Type' & 'Type 2' on both tables and join it like that.
You would add the Type_Key filed to both tables and join the using that field.
Assuming the entries on your key table are unique based on Type & Type2 the join should be a *:1 and then you can use the RELATED function to pull the data.
You could add a column onto the Data table like
Cost = LOOKUPVALUE( 'Key'[Cost], 'Key'[Type], 'Data'[Type], 'Key'[Type 2], 'Data'[Type 2])
Thanks very much, this worked perfectly!
User | Count |
---|---|
80 | |
74 | |
62 | |
61 | |
46 |
User | Count |
---|---|
108 | |
97 | |
88 | |
81 | |
61 |