CODEWITHSUNDEEP
powerbidaxpowerbi-desktoppowerbi-custom-visuals
Recycle_Bin28
Recycle_Bin28

Reputation: 55

Left Joining Tables in Power BI based on multiple columns with duplicate values (DAX)

I am trying to join 2 tables (left join) in Power BI using DAX, and I keep on encountering errors. The dummy table structure and desired outcome are below: Table + Desired Outcome

How would I join the two tables where the Application and Business Unit should match in Both Tables and considering that Table A & B contain duplicate values?

One of the statements I have tried is below, however I keep on encountering errors regarding duplicate values and I am not sure how to proceed. I can't use Power Query unfortunately, this has to be in DAX.

TABLE = 
GENERATEALL (
    Table_A,
    CALCULATETABLE (
        ROW (
            "New vs Old", VALUES (Table_B[New vs Old]),
            "Project", VALUES (Table_B[Project])
        ),
        TREATAS ( ROW ( "Business Unit", Table_A[Business Unit]  ), Table_B[Business Unit] ),
        TREATAS ( ROW ( "Application", Table_A[Application] ), Table_B[Application] )
    )
)

Thank you

Upvotes: 0

Views: 2185

Answers (2)

AlexM
AlexM

Reputation: 3

You need to create a Calculated Column that introduces a Union between "Applications" and "Business Unit", in both tables, then introduce tge relationship with this column created. It’s like creating a Primary Key.

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

Use NATURALLEFTOUTERJOIN Like this:

Table = 


var TABLE_A = SELECTCOLUMNS(
    {
        ("A","BU1","2022-10",100),
        ("B","BU2","2022-11",200),
        ("B","BU3","2022-10",100),
        ("C","BU1","2022-11",400),
        ("D","BU2","2022-12",50)
    },"Application",[Value1],"Business Unit",[Value2], "Month",[Value3], "Cost",[Value4])


var TABLE_B = SELECTCOLUMNS(
    {
        ("A","BU1","Project 1","New"),
        ("B","BU2","Project 2","New"),
        ("B","BU2","Project 5","Old"),
        ("B","BU3","Project 3","Old"),
        ("C","BU1","Project 1","Old"),
        ("D","BU2","Project 4","New")
    },"Application",[Value1],"Business Unit",[Value2], "Project",[Value3], "New vs Old",[Value4])


return NATURALLEFTOUTERJOIN(TABLE_A,TABLE_B)

enter image description here

If your column names or data types don't match you'll need to CONVERT and rename them before joining.

Upvotes: 1

Related Questions