Health Checking Applications

[RandomFactor]

I have a CSV that has columns for Application, ServerName,Color.  

Application	ServerName	Color
APPL1	ABCPMAPP01	GREEN
APPL1	ABCPMAPP02	GREEN
APPL1	ABCPMAPP03	AMBER
APPL2	BCDPMAPP04	RED
APPL2	BCDPMAPP05	GREEN
APPL3	CDEPMAPP06	YELLOW
APPL3	CDEPMAPP07	RED
APPL3	CDEPMAPP08	GREEN
APPL3	CDEPMAPP09	RED

I am trying to determine what color that application is. If a RED is there it is RED, if it does not have a RED but does have an AMBER then it would be AMBER. With Green as the best and Red as the worst, we are looking for the worst. The order is GREEN, YELLOW, AMBER, RED. 

I am new to Power BI so I really appreciate anything you can tell me and thank you in advance.

Steven

[Fowmy]

@RandomFactor 

Sorry, your question isn't clearn enough, please explain with the expected result.

[RandomFactor]

Sorry Fowmy, I should of provided that. What I am looking for is some way to display a chart on a Dashboard in a Stacked Bar Chart where the Application Name is one item and the worst color the second part.

APPL1            AMBER

APPL2            RED

APPL3            RED

I have about 200 applications and each of those applications can have 1 to 200+ servers associationed with it and each of those servers could have any of the 4 colors associated with them. Example, if the server has Windows 2012 R2 as the Operating system on it then the color is RED and the Application is RED because it is on unsupported hardware. All the other servers could be GREEN but 1 RED makes it a RED. Let me know if this provides enough information.

Thank you for replying.

[Fowmy · Accepted solution]

@RandomFactor 

I created the following measure, add the Application and the folliwng measure:

App Status = 
//GREEN, YELLOW, AMBER, RED. 
VAR __Colors = VALUES( Table08[Color] )
VAR __Red = "RED" IN __Colors
VAR __Amber = "AMBER" IN __Colors
VAR __Yellow = "YELLOW" IN __Colors
VAR __Green = "GREEN" IN __Colors

VAR __Result = 
    SWITCH(
        TRUE(),
        __Red, "RED",
        NOT __Red &&  __Amber , "AMBER",
        NOT __Red && NOT __Amber && __Yellow , "Yellow",
        NOT __Red && NOT __Amber && NOT __Yellow , "Green"
    )
RETURN
    IF( 
        ISINSCOPE( Table08[Application ] ),
        __Result
    )

[RandomFactor]

Thank you Fowmy, that worked perfectly.