Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |