Combine two tables (consolidate on multiple criteria) & Add new rows

[Andreas_H]

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

[Zubair_Muhammad]

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] )
            )
        )
    )

[Zubair_Muhammad · Accepted solution]

@Andreas_H

Please see attached file here with your sample data

[Andreas_H]

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...