Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
achakilam
New Member

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

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

 

Data:

IDReason
1ABC
1DEF
3DEF
3GHI
5GHI
5ABC
5DEF

 

Priority:

ReasonPriorityOrder
ABC1
DEF2
GHI3

 

Final Result:

IDReasonReason_Final
1ABCABC
1DEFABC
3DEFDEF
3GHIDEF
5GHIABC
5ABCABC
5DEFABC

 

Thank you!

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@achakilam 

 

Please try this calculated column

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

@achakilam 

 

Please try this calculated column

 

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

Regards
Zubair

Please try my custom visuals

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.