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