Forum Discussion

bideveloper555's avatar
bideveloper555
Helper IV
5 years ago
Solved

Union Columns in Same table with Min/Max function

hi

Below is sample data.

I want to do union on same table for 3column a table(id,opendate,Status) as table 1 and (ID,CloseDate,Status) as table 2.

Union 

Filter table 1 with status = New and min on opendate

and filter table2 status = Closed and min on ClosedDate

 

 

IDOpenDateStatusClosedDateTestDate
101/09/2019New  
1 Closed01/10/2019 
101/09/2019Test01/10/201905/09/2019
     
     
     
IDDatestatus  
101/09/2019New  
101/10/2019Closed  

 

Thanks

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi bideveloper555 ,

     

    You may try to create a calculated table by New table Under Calculations.

    NewTable =
VAR table1 =
    SELECTCOLUMNS (
        FILTER (
            'Table',
            [Status] = "New"
                && [OpenDate] = CALCULATE ( MIN ( 'Table'[OpenDate] ), ALL ( 'Table' ) )
        ),
        "ID", [ID],
        "Date", [OpenDate],
        "Status", [Status]
    )
VAR table2 =
    SELECTCOLUMNS (
        FILTER (
            'Table',
            [Status] = "Closed"
                && [ClosedDate] = CALCULATE ( MIN ( 'Table'[ClosedDate] ), ALL ( 'Table' ) )
        ),
        "ID", [ID],
        "Date", [ClosedDate],
        "Status", [Status]
    )
RETURN
    UNION ( table1, table2 )

     

    You can check more details from here.

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi bideveloper555 ,

     

    You may try to create a calculated table by New table Under Calculations.

    NewTable =
VAR table1 =
    SELECTCOLUMNS (
        FILTER (
            'Table',
            [Status] = "New"
                && [OpenDate] = CALCULATE ( MIN ( 'Table'[OpenDate] ), ALL ( 'Table' ) )
        ),
        "ID", [ID],
        "Date", [OpenDate],
        "Status", [Status]
    )
VAR table2 =
    SELECTCOLUMNS (
        FILTER (
            'Table',
            [Status] = "Closed"
                && [ClosedDate] = CALCULATE ( MIN ( 'Table'[ClosedDate] ), ALL ( 'Table' ) )
        ),
        "ID", [ID],
        "Date", [ClosedDate],
        "Status", [Status]
    )
RETURN
    UNION ( table1, table2 )

     

    You can check more details from here.

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.