Id numbers in multiple rows combined to one status based on the status of the individual rows

[Jacqee]

I have a large dataset where reach ID/Account number column has multiple rows.  Each row transaction has 1 of 4 statuses and I would like to get to an ID/Account level.  The 4 possible statuses in the column are: Ready, Building, Building Materials, and Verification.

The logic should go like this:

If any row for the ID/Account has a status of Verification, the ID/Account status should be Verification regardless of any of the other statuses being present.

If any row for the ID/Account doesn't have Verification but has Building Materials, then the ID/Account status should be Building Materials.

If any row for the ID/Account doesn't have Verificayion or Building Materials, but has Building, then the ID/Account status should be Building.

If any row for the ID/Account doesn't have Verification, Building Materials, or Building but has Ready, then the ID/Account status should be Ready.

I'm currently using SWITCH to create a new column:

Acct Lvl Status = SWITCH(TRUE(),

Row_Level_Status = "Verification", "Verification",

Row_Level_Status = "Building Materials", "Building Materials",

Row_Level_Status = "Building", "Building",

Row_Level_Status = "Ready", "Ready", "Needs Review")

This gets to the row level information but I'm not sure what to add to roll it up to the ID/Account Level.

[Jacqee]

Thank you for the quick response.  I took the response to mean that I'll need to create an additional column for 'Final Status'.  When I do just the first portion above, I'm receiving "Cannot convert value 'TX123456' of type Text to type True/False".  Is this due to the ID having both alpha and numeric characters?

[AmiraBedh · Accepted solution]

You might need to create an intermediary column that assigns numeric values to each status if you are nit handling this case :

Numeric Status = 
SWITCH(
    'Table'[Row_Level_Status],
    "Verification", 4,
    "Building Materials", 3,
    "Building", 2,
    "Ready", 1,
    0  
)

Then :

Acct Lvl Numeric Status = 
CALCULATE(
    MAX('Table'[Numeric Status]),
    ALLEXCEPT('Table', 'Table'[ID])
)

and finally : 

Final Status = 
SWITCH(
    [Acct Lvl Numeric Status],
    4, "Verification",
    3, "Building Materials",
    2, "Building",
    1, "Ready",
    "Needs Review"  
)

[AmiraBedh]

Use CALCULATE combined with MAXX to find the highest priority status for each ID/Account :

Acct Lvl Status = 
CALCULATE(
    MAXX(
        VALUES('Table'[ID]), 
        SWITCH(
            TRUE(),
            COUNTROWS(FILTER('Table', 'Table'[Row_Level_Status] = "Verification")) > 0, 4,
            COUNTROWS(FILTER('Table', 'Table'[Row_Level_Status] = "Building Materials")) > 0, 3,
            COUNTROWS(FILTER('Table', 'Table'[Row_Level_Status] = "Building")) > 0, 2,
            COUNTROWS(FILTER('Table', 'Table'[Row_Level_Status] = "Ready")) > 0, 1,
            0
        )
    ),
    'Table'[ID]
)

Final Status = 
SWITCH(
    [Acct Lvl Status],
    4, "Verification",
    3, "Building Materials",
    2, "Building",
    1, "Ready",
    "Needs Review"
)