Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
Please try this calculated column
Column = MINX ( TOPN ( 1, FILTER ( Data, [ID] = EARLIER ( [ID] ) ), RELATED ( Priority[PriorityOrder] ), ASC ), [Reason] )
Please try this calculated column
Column = MINX ( TOPN ( 1, FILTER ( Data, [ID] = EARLIER ( [ID] ) ), RELATED ( Priority[PriorityOrder] ), ASC ), [Reason] )
Thanks a lot!
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 ) )
User | Count |
---|---|
102 | |
91 | |
85 | |
77 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |