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.
I have one table like this:
Area | Location | Value
and anther table like this:
Area | Location | Value | Worth
What I want to do is return the worth value. However, I cannot create a relationship as I need to lookup the SUM of the value from the first table. For example:
I may have the following data set
Area | Location | Value
AK | NZ | 10
AK | NZ | 5
AK | AU | 4
What I need to do is not return the worth on each row - so I don't want to look up the 10 and 5 for the NZ location, instead I want to lookup 15 (the SUM of the values). The end table I want to create would look like this - it would be a summary:
AK | NZ | 15 | 500
Does anyone have any ideas how I could achieve this?
Solved! Go to Solution.
Hi @WESTi,
In this scenario, you can first summarize your source table.
NewTable1 = SUMMARIZE ( 'Return Value Table1', 'Return Value Table1'[Area], 'Return Value Table1'[Location], "Total Value", SUM ( 'Return Value Table1'[Value] ) )
In 'NewTable1', get the worth value from second table via LookUpValue.
Return Worth = LOOKUPVALUE ( 'Return Value Table2'[Worth], 'Return Value Table2'[Area], NewTable1[Area], 'Return Value Table2'[Location], NewTable1[Location], 'Return Value Table2'[Value], NewTable1[Total Value] )
Best regards,
Yuliana Gu
Hi @WESTi,
In this scenario, you can first summarize your source table.
NewTable1 = SUMMARIZE ( 'Return Value Table1', 'Return Value Table1'[Area], 'Return Value Table1'[Location], "Total Value", SUM ( 'Return Value Table1'[Value] ) )
In 'NewTable1', get the worth value from second table via LookUpValue.
Return Worth = LOOKUPVALUE ( 'Return Value Table2'[Worth], 'Return Value Table2'[Area], NewTable1[Area], 'Return Value Table2'[Location], NewTable1[Location], 'Return Value Table2'[Value], NewTable1[Total Value] )
Best regards,
Yuliana Gu
Can you clarify if you are hoping to add a calc column to 1 of your table, create a new table, or are we talking about just creating measures and visuals?
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
104 | |
93 | |
83 | |
64 |