Forum Discussion

pani_victoria's avatar
pani_victoria
Icon for Helper III rankHelper III
2 years ago
Solved

union 2 tables with date condition

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 🙂

Need Help

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    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))
     
  • DAX_Machine's avatar
    DAX_Machine
    Frequent Visitor
    2 years ago

    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's avatar
      pani_victoria
      Icon for Helper III rankHelper III
      2 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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