Visualising Status of Category by Individual Performance Measures [I need help visualising my data]

[LiamBluett]

Hi all,

I've been trying to figure out a good way that I can dynamically visualise the status of my categories. (Fatal Risk in my example).

I cannot use a big matrix, as the individual measures/columns of the Fatal Risk are not shared - they are either passing or failing, but not the same measures. Ideally, you'd be able to hover over the check or cross and see the measure that's failing.

You will see in my example below, I've tried to achieve this by aligning a bunch of matrixes for every category, i.e. one matrix represents one category. However, this is difficult to maintain and broke to formatting breaking (as you can see in the example).

Another way I've tried representing this is using shapes:

However this is again difficult to maintain and very manual.

My underlying data structure looks something like this:

I have a table for my FATALRISKS (one row per fatal risk)

This is a one-to-many connection to another table called BASECONTROL which is my measure. In this table, I grab the latest measure for the FATALRISKS and calculate the red/green based off of that.

Does anyone have any visual suggestions or any ideas as to how I can create a better visual for this?

[TomMartens]

Hey @LiamBluett ,

I will look closer later today, but I'm wondering

• why there is a 1-to-many relationship between the Risk and the Control table?
• Can a risk have multiple controls?
• Are controls shared between Risks, meaning is there a many to many relationship between Risks and Controls?

I understand that a Control can various questions.

Regards,

Tom

[LiamBluett]

Hey Tom, 

Thanks for your intrigue into my question. In aswer to your questions:

• why there is a 1-to-many relationship between the Risk and the Control table? In the context of the data, a risk is something we want to avoid. To avoid the risk, we make use of controls (preventative controls). Risks can have any number of controls that are intended to prevent the risk from happening. Hence, one risk will have many controls.
• Can a risk have multiple controls? Yes. A risk may have an arbitrary number of controls. 
• Are controls shared between Risks, meaning is there a many to many relationship between Risks and Controls? In practice, yes some risks may share the same preventative controls. In how the data is setup, no it is not. We will make a unique control for every risk.

To provide further clarity,

The performance of controls are measured by 'questions' (100: Passing, 50 Needs Improvement, 0 Fail). One control may have many questions asked. Typically, the questions will all be asked at the same time in a batch, for example once every 6 months. However, sometimes a control's questions will be split, where 5/6 of the questions are asked every 6 months but 1/6 of the questions are asked monthly.

[TomMartens · Accepted solution]

Hey @LiamBluett ,

I consider it not simple to find a visual, that is, visualizing the controls (the questions) in one go for the different risks. This is due to the various number of controls per risk.

For this reason I came up with a different approach, it's more a text based solution than a visualization:

This is the DAX for the measure "visualize AllControls":

visualize AllControls = 
var greencheckmark = UNICHAR( 9989 )
var redCircle = UNICHAR( 9940 )
return
IF( HASONEVALUE( 'Risk'[Name] )
    , CONCATENATEX(
        SELECTCOLUMNS(
            FILTER(
                SUMMARIZE(
                    'Question'
                    , Risk[CODE]
                    , 'Question'[QUESTION]
                    , Question[COMPLETEDAT]
                    , Question[RESULT]
                )
                , [COMPLETEDAT] = 
                    SELECTCOLUMNS(
                                INDEX(
                                    1
                                    , SUMMARIZE(
                                        'Question'
                                        , Risk[CODE]
                                        , Question[CODE]
                                        , Question[COMPLETEDAT]
                                        , Question[RESULT]
                                    )
                        , ORDERBY( Question[COMPLETEDAT] , DESC )
                        , DEFAULT
                        , PARTITIONBY( Risk[CODE] , Question[CODE] )
                    )
                    , "d" , [COMPLETEDAT]
                    )
            )
            , "q" , [QUESTION]
            , "r" , [RESULT]
        )
        , var resultEmoji = 
            IF( [r] = 100 , greencheckmark , redCircle )
        return
        CONCATENATE( CONCATENATE( [q] , ": ") , resultEmoji )
        , UNICHAR( 10 ) -- equals new line unicode character
    )
    , BLANK()
)

and this is the DAX for the measure "visualize OverAllResullt":

visualize OverAllResullt = 
var greencheckmark = UNICHAR( 9989 )
var redCircle = UNICHAR( 9940 )
var overallValue = 
    MINX(
            FILTER(
                SUMMARIZE(
                    'Question'
                    , Risk[CODE]
                    , 'Question'[QUESTION]
                    , Question[COMPLETEDAT]
                    , Question[RESULT]
                )
                , [COMPLETEDAT] = 
                    SELECTCOLUMNS(
                                INDEX(
                                    1
                                    , SUMMARIZE(
                                        'Question'
                                        , Risk[CODE]
                                        , Question[CODE]
                                        , Question[COMPLETEDAT]
                                        , Question[RESULT]
                                    )
                        , ORDERBY( Question[COMPLETEDAT] , DESC )
                        , DEFAULT
                        , PARTITIONBY( Risk[CODE] , Question[CODE] )
                    )
                    , "d" , [COMPLETEDAT]
                    )
            )
            , [RESULT]
    )
return
IF( HASONEVALUE( 'Risk'[Name] )
    , IF(overallValue < 100 , "Fail" , "Pass")
    , BLANK()
)

From the table/matrix visual, I would consider to provide a visual tooltip visual that is filtering a single risk and a drill through report that dives into the details of the single risk.

I think this approach keeps the first view concise, but allows greater detail when looking for a single risk.

My overall result for the risk "Heat exposure" is different from your measure "Most recent score", because this risk is sharing the control "Are the water bottle available for employee" with the risk Drowning. Not sure if I misunderstood your explanation, but I think controls (the questions) are checked separately per risk.

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[TomMartens]

Hey @LiamBluett ,

I'm wondering what a single circle for a single risk represents because you say

"I grab the latest measure for the FATALRISKS and calculate the red/green based off of that."

Consider creating a pbix that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive, Google drive, or Dropbox, and share the link. If you are using Excel to create the sample data instead of the manual input method share the xlsx as well.

Do not forget to describe the expected result based on the sample data you provide.

Regards,

Tom

[LiamBluett]

Hi Tom,

Here's a link to the resources

https://drive.google.com/drive/folders/1K3ldpFX-wjFFNu3FeqBSDFjzv3yrJoQ-?usp=sharing

Note that the model here is simplified - in my case there are a few more layers in between the control and the question. I don't think this will change how it behaves. Essentially, I want to be able to view each Risk and have it's associated Controls (one risk may have a different number of controls to another) represented in a grid-like manner so that someone can immediately see which controls are failing (where the last question corresponding to that control had a 0 for example).

I can't use a matrix as every risk has different controls.

A control is evaluated using questions. Generally, the same set of questions may be asked all of the time. However, sometimes we may want to exclude some questions from being asked as it was asked not so long ago and this would be repetitive for the person completing the questions. I'm not sure if this is very obvious in this simplification of the model.