Matrix visual Hierarchy and conditional color based on overall color

[fahadkhan]

Hi everyone, 

I have a simple usecase for matrix visual i want to display. 

I have a table with Project, Project group, Team lead, task and status color in one table. 

so based on the heirachey i want to overall color of a from project group and and project level. for example. 

if any task in project group is incomplete it should show yellow, if any task in project group level is red then overall color is red. green is only possible if all tasks in proejct are green. 

similarly if we move one level above i.e at project level it should show color based on on project group level. for example. 

if any of the project group is red then overall project is red. if there is no red and any one of the project group is yellow then overall project is yellow and green is only possible if all project groups are green. 

here is example screen shot:

I want to create a measure that would display color based on heirachy level. 

[speedramps]

Yes, this solution drills up and down

Please click the thumbs up because I have tried to help.
Then click accept solution if it works.

Project group colour = 
var mylist = 
    CALCULATETABLE(
        VALUES(yourdata[Status]),
        ALLEXCEPT(yourdata,yourdata[Project Group]),
        yourdata[Status] <> "Complete"
    )
RETURN
IF (COUNTROWS(mylist) > 0, "Red", "Green")

[vojtechsima · Accepted solution]

Hey @fahadkhan 

TEst this one, bit lengthy tho:

coloring = 
var currentProject = SELECTEDVALUE('Table'[Project])
var currentStatus = SELECTEDVALUE('Table'[Task Status])
var isTaskLevel = HASONEVALUE('Table'[Task])

var delayedRedOnes = 
COUNTX(
    VALUES('Table'[Project Group]),
    CALCULATE(
        COUNTX( 
            VALUES('Table'[Project]),
            CALCULATE(
                COUNTROWS('Table'),
                'Table'[Task Status] = "Delayed",
                REMOVEFILTERS('Table'[Task])
            )
        ),
    REMOVEFILTERS('Table'[Project])
    )
)
var inProgressOnes = 
COUNTX(
    VALUES('Table'[Project Group]),
    CALCULATE(
        COUNTX( 
            VALUES('Table'[Project]),
            CALCULATE(
                COUNTROWS('Table'),
                'Table'[Task Status] = "In Progress",
                REMOVEFILTERS('Table'[Task])
            )
        ),
    REMOVEFILTERS('Table'[Project])
    )
)
var completeGreenOnes = 
COUNTX(
    VALUES('Table'[Project Group]),
    CALCULATE(
        COUNTX( 
            VALUES('Table'[Project]),
            CALCULATE(
                COUNTROWS('Table'),
                'Table'[Task Status] = "Complete",
                REMOVEFILTERS('Table'[Task])
            )
        ),
    REMOVEFILTERS('Table'[Project])
    )
)
var taskLevelCheck = 
SWITCH(
    TRUE(),
    isTaskLevel && currentStatus = "Delayed", "red",
    isTaskLevel && currentStatus = "In Progress", "yellow",
    isTaskLevel && currentStatus = "Complete", "green"
)

var idk = SWITCH( TRUE(),
completeGreenOnes > 0 && ISBLANK(delayedRedOnes) && ISBLANK(inProgressOnes), "green",
delayedRedOnes > 0 ,"red",
isTaskLevel, taskLevelCheck,
"yellow"
)

return idk

[fahadkhan]

does it work on drill up and drill down? for example if drill up to see the project group level in a matrix visual will it show the correct number/code?

[vojtechsima]

I think so @fahadkhan 

I am attaching the file, you can see

[speedramps]

Create a measure like this

Please click thumbs up because I have tried to help.

Then click [accept solution] if it works

Project group colour = 
var mylist = 
    CALCULATETABLE(
        VALUES(yourdata[Status]),
        ALLEXCEPT(yourdata,yourdata[Project Group]),
        yourdata[Status] <> "Complete"
    )
RETURN
IF (COUNTROWS(mylist) > 0, "Red", "Green")