union 2 tables with date condition

[pani_victoria]

Hello colleagues!

I have two tables.

One table with one set of dates (01/01/15 - 07/22/24), the second table with another set of dates (07/15/24-07/23/24).

I want to merge these two tables (UNION), but so that the dates from the second table that exist in the first table are excluded. That is, to merge from the second table, only rows with the date 07/23/24 were used.

I'm having a hard time writing the formula, please help 🙂

[Anonymous · Accepted solution]

Hi, @pani_victoria 

Thanks for @DAX_Machine  and @ryan_mayu reply. If you have not find the suitable dax, you can try this.

NewTable = 
FILTER(
    'Table 2',
    NOT('Table 2'[Date] IN VALUES('Table 1'[Date]))
)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[DAX_Machine]

DISTINCT (
           UNION (
                      DISTINCT( DATETABLE1[col] ),
                      DISTINCT( DATETABLE2[col] )
           )
)

This formula should produce a table with distinct values of both tables.

Did I answer your question? If so please mark my post as a solution!

[pani_victoria]

Yes, this is an obvious solution, but it doesn't work for me.
In my case, 2 tables may have the same row in meaning, but with different data, therefore it will be unique for both tables, but in fact it will be a duplicate

[ryan_mayu]

@pani_victoria 

you can try this

Table 3 =

VAR _list=DISTINCT('Table 1'[Date])

return union('Table 1',FILTER('Table 2', not 'Table 2'[Date]  in _list))

[pani_victoria]

Thanks! i'll try it