Suppose i have Table with Column A having Repeated Reference and Column B Status with Text Value of G1, G2, G3, G4 and G5 which might be assigned to distinct reference with mutiple status like as below.
Now I want to assign the status in column where if from following data Rahul is having status as G1, G2, G3 then i should get status as G3 for all distinct ref in column.
Priority for Status is G5, G4, G3, g2 and G1
Column A Column B
Rahul G1
Rahul G2
Rahul G3
Mehul G1
Mehul G5
Rohit G1
Ranjeet G2
Ranjeet G5

I have a table of data from Azure DevOps that contains a sequence of status changes for work items. Each record contains an ID for the work item, a status, a date at which the status changed and a date on which the work item was created.

Could some one help me how to achieve the below scenario in power bi for table visual.