I want DAX calculated column in Power BI with following requirements

Question

I have table with column A having repeated reference and column B status with text value G1, G2, G3, G4 and G5 which might be assigned to distinct reference with multiple statuses like as below.

I want to assign the status in column where if from following data: Rahul has status as G1, G2, G3 then it 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

Column A              Column B                     Result
Rahul                        G1                      G3 
Rahul                        G2                      G3
Rahul                        G3                      G3
Mehul                        G1                      G5
Mehul                        G5                      G5
Rohit                        G1                      G1
Ranjeet                      G2                      G5
Ranjeet                      G5                      G5

Answer 1

I created a calculated column for the StatusValue :

StatusValue = 
SWITCH (
    MyTable[ColumnB],
    "G5", 5,
    "G4", 4,
    "G3", 3,
    "G2", 2,
    "G1", 1,
    0
)

Then based on this calculated column I created another one :

Result = 
CALCULATE (
    MAX ( MyTable[ColumnB] ),
    FILTER (
        MyTable,
        MyTable[ColumnA] = EARLIER ( MyTable[ColumnA] ) &&
        MyTable[StatusValue] = CALCULATE (
            MAX ( MyTable[StatusValue] ),
            FILTER (
                MyTable,
                MyTable[ColumnA] = EARLIER ( MyTable[ColumnA] )
            )
        )
    )
)