join two tables and find min after doing group by

[achakilam]

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

Could you please help me figure out how to do this in PowerBI?

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!

[Zubair_Muhammad · Accepted solution]

@achakilam 

Please try this calculated column

Column =
MINX (
    TOPN (
        1,
        FILTER ( Data, [ID] = EARLIER ( [ID] ) ),
        RELATED ( Priority[PriorityOrder] ), ASC
    ),
    [Reason]
)

[achakilam]

Thanks a lot!

[AlB]

Hi @achakilam 

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