Dax: Full Outer Join so all rows from both tables are kept

[AliPoa]

Hi, Please can anyone help?

I need to join 2 tables so that all rows from each table are displayed, even if they don't match i.e. a full outer join.

I am unable to do this in Power Query as I am using tables from Semantic Models drawn from the PBI Service.

For example:

Table1:

No		CollectionDate		Customer		Description		Value
FAR00002		09/02/2026		CustomerA		ProductA		33840
FAR00002		09/02/2026		CustomerA		ProductB		720
FAR00005		09/02/2026		CustomerB		ProductC		41040
FAR00005		09/02/2026		CustomerB		ProductC		50760
FAR00010		09/02/2026		CustomerX		ProductD		12345

Table 2:

CollectionDate		No		Qty
09/02/2026		FAR00002		1111
09/02/2026		FAR00005		3333
09/02/2026		FAR00007		5555

Which should result in Table 3:

No		CollectionDate		Customer		Description		Value		Qty
FAR00002		09/02/2026		CustomerA		ProductA		33840		1111
FAR00002		09/02/2026		CustomerA		ProductB		720		1111
FAR00005		09/02/2026		CustomerB		ProductC		41040		3333
FAR00005		09/02/2026		CustomerB		ProductC		50760		3333
FAR00010		09/02/2026		CustomerX		ProductD		12345
FAR00007		09/02/2026								5555

Can anyone share the DAX code I need to make the above work please?

Many thanks.

[v-karpurapud]

Hi @AliPoa 

Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to @johnt75  and @alish_b   for offering helpful suggestions.

Could you let us know if the suggested solution resolved your issue? If it's still pending, please let us know any further details so we can help.

Best regards,
Community Support Team.

[alish_b · Accepted solution]

Hi @AliPoa ,

Power Query would have been the second best option while source level modification being the best. Well, hope is not all lost in DAX though. We have NATURALLEFTOUTERJOIN which again is not as explicit on joining columns and requires common names for matching but will do for this case. This will not fetch all the rows though, all from first and matching from second and then we will do an UNION for the ones left behind in Table 2 (making sure that the number and order of columns is maintained). Something as follows:

Table 3 = 

VAR LeftJoin = 
    SELECTCOLUMNS(
        NATURALLEFTOUTERJOIN('Table 1', 'Table 2'),
        "No", 'Table 1'[No],
        "CollectionDate", 'Table 1'[CollectionDate],
        "Customer", [Customer],
        "Description", [Description],
        "Value", [Value],
        "Qty", [Qty]
    )

VAR RightOnly = 
    SELECTCOLUMNS(
        FILTER(
            'Table 2',
            NOT(
                CONTAINS(
                    'Table 1',
                    'Table 1'[No], 'Table 2'[No],
                    'Table 1'[CollectionDate], 'Table 2'[CollectionDate]
                )
            )
        ),
        "No", [No],
        "CollectionDate", [CollectionDate],
        "Customer", BLANK(),
        "Description", BLANK(),
        "Value", BLANK(),
        "Qty", [Qty]
    )

RETURN
UNION(LeftJoin, RightOnly)

Well I tried it with tables created with 'Enter Data' in Power Query and got it to work. Did not work with DATATABLE created tables. Did not try it with semantic model tables but give it a try.

Hope it helps!

[johnt75]

This might not have great performance if you have lots of rows, but you can use

Cross Joined =
VAR InBoth =
    FILTER (
        CROSSJOIN ( 'Table1', 'Table2' ),
        'Table1'[No] = 'Table2'[No]
            && 'Table1'[CollectionDate] = 'Table2'[CollectionDate]
    )
VAR _Table1 =
    SUMMARIZE ( 'Table1', Table1[No], Table1[CollectionDate] )
VAR _Table2 =
    SUMMARIZE ( 'Table2', Table2[No], Table2[CollectionDate] )
VAR OnlyIn1 =
    EXCEPT ( _Table1, _Table2 )
VAR OnlyIn2 =
    EXCEPT ( _Table2, _Table1 )
VAR OnlyIn1AllColumns =
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Table1',
            "No", 'Table1'[No],
            "CollectionDate", 'Table1'[CollectionDate],
            "Customer", 'Table1'[Customer],
            "Description", 'Table1'[Description],
            "Value", 'Table1'[Value],
            "Qty", BLANK ()
        ),
        OnlyIn1
    )
VAR OnlyIn2AllColumns =
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Table2',
            "No", 'Table2'[No],
            "CollectionDate", 'Table2'[CollectionDate],
            "Customer", BLANK (),
            "Description", BLANK (),
            "Value", BLANK (),
            "Qty", 'Table2'[Qty]
        ),
        OnlyIn2
    )
VAR InBothAllColumns =
    SELECTCOLUMNS (
        InBoth,
        "No", 'Table1'[No],
        "CollectionDate", 'Table1'[CollectionDate],
        "Customer", 'Table1'[Customer],
        "Description", 'Table1'[Description],
        "Value", 'Table1'[Value],
        "Qty", 'Table2'[Qty]
    )
VAR Result =
    UNION ( InBothAllColumns, OnlyIn1AllColumns, OnlyIn2AllColumns )
RETURN
    Result