Append one table onto another filtering by common ID using DAX

[Anonymous]

I have two tables; table 1 and 2 that contains the same columns. I would like to append table 2 onto table 1, but only when the parent ID in table two matches a parent ID in table 1. The case can be illustrated as follows:

Table 1
ID	Parent ID	Month	Year
1_1_2018	1	1	2018
2_1_2018	2	1	2018
2_2_2018	2	2	2018
2_3_2018	2	3	2018
2_4_2018	2	4	2018

Table 2
ID	Parent ID	Month	Year
3_1_2018	3	1	2018
2_4_2018	2	4	2018
2_5_2018	2	5	2018
2_6_2018	2	6	2018
2_7_2018	2	7	2018

Result
ID	Parent ID	Month	Year
1_1_2018	1	1	2018
2_1_2018	2	1	2018
2_2_2018	2	2	2018
2_3_2018	2	3	2018
2_4_2018	2	4	2018
2_4_2018	2	4	2018
2_5_2018	2	5	2018
2_6_2018	2	6	2018
2_7_2018	2	7	2018

How can this be done in dax?

Thank you for your input!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

New a calculated table with DAX.

Table3 =
UNION (
    Table1,
    FILTER (
        Table2,
        LOOKUPVALUE ( Table1[Parent ID], Table1[Parent ID], Table2[Parent ID] )
            <> BLANK ()
    )
)

Best regards,

Yuliana Gu

[Anonymous]

Thank you so much @v-yulgu-msft. This works well!

To others reading this, i got another suggestion as well that also works:

Res= UNION (test1; filter(test2; test2[ParentID] IN DISTINCT(test1[ParentID])))

Cheers