Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Andreas_H
Frequent Visitor

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

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 1Crit 2ValTab_2
123abc1,01
123abc0,56
123def0,40
456def0,15
456abc3,52
456ghi13,93
789ghi14,09
789def0,50
789ghi0,19
123xyz0,67
789xyz0,67

 

tab_1

Crit 1Crit 2ValTab_1ValTab_2 (result expected)
123abc3,041,58
123def1,200,40
456def0,460,15
456abc10,553,52
456ghi41,7813,93
789ghi42,2814,29
789def0,060,50
123xyz 0,67
789xyz 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

1 ACCEPTED SOLUTION
3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.