cancel
Showing results for
Did you mean:
Frequent Visitor

## join two tables and find min after doing group by

Hi all,

I want to join two tables `Data` and `Priority`  and create a new column in the `Data` table which is Data.Reason_Final

join tables on Data. Reason = Priority.Reason and Data.Reason_Final = min(Priority.PriorityOrder) based on group by Data.ID

Data:

 ID Reason 1 ABC 1 DEF 3 DEF 3 GHI 5 GHI 5 ABC 5 DEF

Priority:

 Reason PriorityOrder ABC 1 DEF 2 GHI 3

Final Result:

 ID Reason Reason_Final 1 ABC ABC 1 DEF ABC 3 DEF DEF 3 GHI DEF 5 GHI ABC 5 ABC ABC 5 DEF ABC

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: join two tables and find min after doing group by

```Column =
MINX (
TOPN (
1,
FILTER ( Data, [ID] = EARLIER ( [ID] ) ),
RELATED ( Priority[PriorityOrder] ), ASC
),
[Reason]
)
```
Try my new Power BI game Cross the River
3 REPLIES 3
Super User

## Re: join two tables and find min after doing group by

```Column =
MINX (
TOPN (
1,
FILTER ( Data, [ID] = EARLIER ( [ID] ) ),
RELATED ( Priority[PriorityOrder] ), ASC
),
[Reason]
)
```
Try my new Power BI game Cross the River
Super User

## Re: join two tables and find min after doing group by

If there's no relationship between the tables Data and Priority, you can create a new calculated column:

```NewColumn =
VAR IDReasons_ =
CALCULATETABLE ( DISTINCT ( Data[Reason] ); ALLEXCEPT ( Data; Data[ID] ) )
VAR AuxTable_ =
CALCULATETABLE ( Priority; TREATAS ( IDReasons_; Priority[Reason] ) )
RETURN
CALCULATE (
DISTINCT ( Priority[Reason] );
TOPN ( 1; AuxTable_; [PriorityOrder]; ASC )
)
```

Frequent Visitor

## Re: join two tables and find min after doing group by

Thanks a lot!

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)