Full outer join in DAX

DAX DAX Calculations
1

Please I have 2 table and I am trying to create a full outer join with dax. I will like to use calculated tables to derive it. I have seen different approach line but they do not work, Please any advice. Thank you

Table 1

image
image1051Ă—218 4.56 KB

Table 2

image

Combined

image
image1112Ă—253 11 KB

Full Outer Join.pbix (104.5 KB)

2

@akeko Full Outer Join.pbix (97.1 KB)

image
image794Ă—367 15.7 KB

Your result is incorrect, OUTER JOIN doesn’t work like that for AG there will be 4 rows 2 * 2.

image
image1336Ă—621 39.4 KB 

Table 2 = 
VAR FirstTable =
    SELECTCOLUMNS (
        Table1,
        "Company", Table1[Company] & "",
        "Outlet_", Table1[Outlet] & "",
        "Group_", Table1[Group] & "",
        "Date_", Table1[Date] & "",
        "Price_", Table1[Price] & ""
    )
VAR SecondTable =
    SELECTCOLUMNS (
        Table2,
        "Company", Table2[Company] & "",
        "Company2", Table2[Company] & "",
        "Outlet", Table2[Outlet] & "",
        "Group", Table2[Group] & "",
        "Date", Table2[Date] & "",
        "Price", Table2[Price] & ""
    )
VAR ExistsInSecondNotInFirst =
    SELECTCOLUMNS (
        CALCULATETABLE (
            Table2,
            EXCEPT ( VALUES ( Table2[Company] ), VALUES ( Table1[Company] ) )
        ),
        "Company2", Table2[Company],
        "Outlet", Table2[Outlet],
        "Group", Table2[Group],
        "Date", Table2[Date],
        "Price", Table2[Price]
    )
VAR LeftJoin =
    NATURALLEFTOUTERJOIN ( FirstTable, SecondTable )
VAR NewCols =
    ADDCOLUMNS (
        ExistsInSecondNotInFirst,
        "Company", "",
        "Outlet_", "",
        "Group_", "",
        "Date_", "",
        "Price_", ""
    )
VAR FinalTable =
    SELECTCOLUMNS (
        NewCols,
        "Company", [Company],
        "Outlet_", [Outlet_],
        "Group_", [Group_],
        "Date_", [Date_],
        "Price_", [Price_],
        "Company2", [Company2],
        "Outlet", [Outlet],
        "Group", [Group],
        "Date", [Date],
        "Price", [Price]
    )
VAR Result =
    UNION ( LeftJoin, FinalTable )
RETURN
    Result
2 Likes
3

Thank you, The combined table I added was incorrect

4

@akeko Awesome! that previous version was a prototype and I spent some more time on this and modified the code to be more readable and data type suitable, earlier I used “” which will break non text data type, now I use BLANK() and instead of using ADDCOLUMNS to add new columns SELECTCOLUMNS is used, this way the code is more readable and short.

Full Outer Join.pbix (102.6 KB)

If you want to read and understand how everything works you can read this blog:

FULL OUTER JOIN =
VAR FirstTable =
    SELECTCOLUMNS (
        Table1,
        "Company", Table1[Company] & "",
        "Outlet_", Table1[Outlet],
        "Group_", Table1[Group],
        "Date_", Table1[Date],
        "Price_", Table1[Price]
    )
VAR SecondTable =
    SELECTCOLUMNS (
        Table2,
        "Company", Table2[Company] & "",
        "Company2", Table2[Company],
        "Outlet", Table2[Outlet],
        "Group", Table2[Group],
        "Date", Table2[Date],
        "Price", Table2[Price]
    )
VAR LeftJoin =
    NATURALLEFTOUTERJOIN (
        FirstTable,
        SecondTable
    )
VAR ExistsInSecondNotInFirst =
    CALCULATETABLE (
        Table2,
        EXCEPT (
            DISTINCT ( Table2[Company] ),
            DISTINCT ( Table1[Company] )
        )
    )
VAR AddAndArrangeCols =
    SELECTCOLUMNS (
        ExistsInSecondNotInFirst,
        "Company", BLANK (),
        "Outlet_", BLANK (),
        "Group_", BLANK (),
        "Date_", BLANK (),
        "Price_", BLANK (),
        "Company2", Table2[Company],
        "Outlet", Table2[Outlet],
        "Group", Table2[Group],
        "Date", Table2[Date],
        "Price", Table2[Price]
    )
VAR Result =
    UNION (
        LeftJoin,
        AddAndArrangeCols
    )
RETURN
    Result