Forum Discussion

fahadkhan's avatar
fahadkhan
Frequent Visitor
1 year ago
Solved

Matrix visual Hierarchy and conditional color based on overall color

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. 

Need Help
  • vojtechsima's avatar
    vojtechsima
    1 year ago

    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

     

5 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    1 year ago

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

     

     

  • vojtechsima's avatar
    vojtechsima
    Icon for Super User rankSuper User
    1 year ago

    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

     

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    1 year ago

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