Union Columns in Same table with Min/Max function

[bideveloper555]

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

ID	OpenDate	Status	ClosedDate	TestDate
1	01/09/2019	New
1		Closed	01/10/2019
1	01/09/2019	Test	01/10/2019	05/09/2019
ID	Date	status
1	01/09/2019	New
1	01/10/2019	Closed

Thanks

[v-stephen-msft · Accepted solution]

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]

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