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 ...
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.

amitchandak's avatar
amitchandak
Super User
5 years ago

bideveloper555 , Try something like

union(
summarize(filter(Table,not(isblank(table[OpenDate]))), table[ID], table[OpenDate])
summarize(filter(Table,not(isblank(table[closeDate]))), table[ID], table[closeDate])
)

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.