Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Dear Power BI enthusiasts,
these days I did struggle with the combination of two datasets. I want to Sum in tab_1 the values of tab_2 that matches the two criteria columns. With LookUpValue I failed since in tab_2 are multiple rows per criteria set. A SumX I did only manage when hardcoding the criteria = "123" = "abc" as String, but I want to refer the formula to the specific row it is in. Can anyone help me with a SumIF like formula to bring the Value from tab_2 into tab_1.
tab_2
Crit 1 | Crit 2 | ValTab_2 |
123 | abc | 1,01 |
123 | abc | 0,56 |
123 | def | 0,40 |
456 | def | 0,15 |
456 | abc | 3,52 |
456 | ghi | 13,93 |
789 | ghi | 14,09 |
789 | def | 0,50 |
789 | ghi | 0,19 |
123 | xyz | 0,67 |
789 | xyz | 0,67 |
tab_1
Crit 1 | Crit 2 | ValTab_1 | ValTab_2 (result expected) |
123 | abc | 3,04 | 1,58 |
123 | def | 1,20 | 0,40 |
456 | def | 0,46 | 0,15 |
456 | abc | 10,55 | 3,52 |
456 | ghi | 41,78 | 13,93 |
789 | ghi | 42,28 | 14,29 |
789 | def | 0,06 | 0,50 |
123 | xyz | 0,67 | |
789 | xyz | 0,67 |
This would already help me to get a viable entry into the world of formulas.
But there is also an addition: How to manage, that, if in tab_2 are rows that do not have a corresponding 'target' in tab_1 to consolidate them to via a SumIF. Could those rows be attached to tab_1? But only the rows that do not have a match with SumIf ! I tried this via the 'append' function, but there ALL rows have been added.
(The bold rows from tab_1 are the result. In tab_2 thesd two are italic.)
Is Power BI Desktop capable of doing this?
Many many thanks for your thoughts on a) My SumIf Problem and b) the "attach rows without a target" problem.
Best regards, Andreas
Solved! Go to Solution.
Hi @Andreas_H
Try this calculated table
from the modelling tab>>>NEW TABLE
New tab_1 = VAR missingrows = ADDCOLUMNS ( EXCEPT ( ALL ( tab_2[Crit 1], tab_2[Crit 2] ), ALL ( tab_1[Crit 1], tab_1[Crit 2] ) ), "ValTab_1", BLANK () ) RETURN ADDCOLUMNS ( UNION ( tab_1, missingrows ), "valtab_2", CALCULATE ( SUM ( tab_2[ValTab_2] ), FILTER ( tab_2, tab_2[Crit 1] = EARLIER ( [Crit 1] ) && tab_2[Crit 2] = EARLIER ( [Crit 2] ) ) ) )
Dear Muhammad,
I can hardly believe! That is awesome! I still don't get it... with VBA I would take me hours and hours to write such a script and you returned a result in some minutes only. My sincere respect goes to you!
I need to further look into your approach and try to understand the syntax. And I need to learn DAX! The possibilities seem to be endless.
Many, many thanks to you!
Have a great weekend! Best wishes, Andreas
PS: I still can not believe... Wow...
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
86 | |
83 | |
65 | |
49 |
User | Count |
---|---|
127 | |
108 | |
89 | |
70 | |
66 |