cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nordgard Frequent Visitor
Frequent Visitor

Append one table onto another filtering by common ID using DAX

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   
IDParent IDMonthYear
1_1_2018112018
2_1_2018212018
2_2_2018222018
2_3_2018232018
2_4_2018242018
 
Table 2   
IDParent IDMonthYear
3_1_2018312018
2_4_2018242018
2_5_2018252018
2_6_2018262018
2_7_2018272018
 
Result   
IDParent IDMonthYear
1_1_2018112018
2_1_2018212018
2_2_2018222018
2_3_2018232018
2_4_2018242018
2_4_2018242018
2_5_2018252018
2_6_2018262018
2_7_2018272018
How can this be done in dax?
 
Thank you for your input!
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Append one table onto another filtering by common ID using DAX

Hi @nordgard,

 

New a calculated table with DAX.

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

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Append one table onto another filtering by common ID using DAX

Hi @nordgard,

 

New a calculated table with DAX.

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

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
nordgard Frequent Visitor
Frequent Visitor

Re: Append one table onto another filtering by common ID using DAX

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