show different colours on matrix visual based on fault counts within a timeframe

[jitpbi]

Hi,

The below is my sample dataset where getting values at every 5 minutes interval.

Spoiler

ICR	Inverter	SCB	Unit	Count of Anomaly	DateTime
ICR1	INV1	SCB10	U1	1	1/17/2021 7:17
ICR1	INV1	SCB10	U1	1	1/17/2021 7:27
ICR1	INV1	SCB10	U1	1	1/17/2021 8:19
ICR1	INV1	SCB10	U1	1	1/17/2021 8:24
ICR1	INV1	SCB10	U1	1	1/17/2021 9:21
ICR1	INV1	SCB10	U1	1	1/17/2021 9:31
ICR1	INV1	SCB10	U1	1	1/17/2021 9:42
ICR1	INV1	SCB10	U1	1	1/17/2021 10:15
ICR1	INV1	SCB10	U1	1	1/17/2021 11:36
ICR1	INV1	SCB10	U1	1	1/17/2021 15:18
ICR1	INV1	SCB10	U1	1	1/17/2021 16:20
ICR1	INV1	SCB10	U1	1	1/17/2021 17:17
ICR1	INV1	SCB10	U1	1	1/17/2021 17:27
ICR1	INV1	SCB10	U1	1	1/17/2021 18:14
ICR2	INV1	SCB10	U1	1	1/17/2021 7:17
ICR2	INV1	SCB10	U1	1	1/17/2021 7:27
ICR2	INV1	SCB10	U1	1	1/17/2021 8:19
ICR2	INV1	SCB10	U1	1	1/17/2021 8:24
ICR2	INV1	SCB10	U1	1	1/17/2021 9:21
ICR2	INV1	SCB10	U1	1	1/17/2021 9:31
ICR2	INV1	SCB10	U1	1	1/17/2021 9:42
ICR2	INV1	SCB10	U1	1	1/17/2021 10:15
ICR2	INV1	SCB10	U1	1	1/17/2021 11:36
ICR2	INV1	SCB10	U1	1	1/17/2021 14:16
ICR2	INV1	SCB10	U1	1	1/17/2021 14:21
ICR2	INV1	SCB10	U1	1	1/17/2021 14:36
ICR2	INV1	SCB10	U1	1	1/17/2021 15:02
ICR2	INV1	SCB10	U1	1	1/17/2021 15:18
ICR2	INV1	SCB10	U1	1	1/17/2021 17:17
ICR2	INV1	SCB10	U1	1	1/17/2021 17:27
ICR2	INV1	SCB10	U1	1	1/17/2021 18:14
ICR1	INV1	SCB10	U2	1	1/17/2021 18:14
ICR2	INV1	SCB10	U2	1	1/17/2021 18:14

I need to display the status of unit (based on count of anomaly) in matrix visual like below.

The below are the conditions to display the colours:

1.Green Colour:  if the anomaly count is less than 10 for the current day

2. Orange Colour: if the anomaly count is equal or greater than 10 for the current day and the anomaly count for the any day in the last 6 days is less than 10 

3. Red Colour: if the anomaly count is equal or greater than 10 for the current day and anomaly count for each day in the last 6 days is equal or greater than 10 

Additional Condition: we have to conisder the values only for the time in between only 9:30 AM to 5:30 PM 

Please help me to achieve this.

Thanks

@PhilipTreacy 

[jitpbi]

Hi @v-yingjl ,

This is working for me and need to add one more condition in the code:

> There is one more field "Actual" in the dataset, so if "Actual" value is 0 then display with grey colour

Please help to add this logic also in the code you shared.

Thanks

[v-yingjl · Accepted solution]

Hi @jitpbi ,

Basically you can try to create this measure:

count = 
VAR tab =
    FILTER (
        'Table',
        TIME ( HOUR ( 'Table'[DateTime] ), MINUTE ( 'Table'[DateTime] ), SECOND ( 'Table'[DateTime] ) )
            >= TIME ( 9, 30, 0 )
            && TIME ( HOUR ( 'Table'[DateTime] ), MINUTE ( 'Table'[DateTime] ), SECOND ( 'Table'[DateTime] ) )
                <= TIME ( 17, 30, 0 )
    )
VAR currentcount =
    COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () ) )
VAR p1 =
    COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 1 ) )
VAR p2 =
    COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 2 ) )
VAR p3 =
    COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 3 ) )
VAR p4 =
    COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 4 ) )
VAR p5 =
    COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 5 ) )
RETURN
    SWITCH (
        TRUE (),
        currentcount < 10, UNICHAR ( 128994 ),
        currentcount >= 10,
            IF (
                p1 < 10
                    || p2 < 10
                    || p3 < 10
                    || p4 < 10
                    || p5 < 10,
                UNICHAR ( 128992 ),
                IF (
                    p1 >= 10
                        && p2 >= 10
                        && p3 >= 10
                        && p4 >= 10
                        && p5 >= 10,
                    UNICHAR ( 128308 )
                )
            )
    )

in my sample it should be all green

Attached the sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

Hi @jitpbi 

Can you share some sample data as a file or at least copy.paste it into the forum/  Even with my high res screen I can't make out the column names or data values with my bad eyesight.

Regards

Phil

[jitpbi]

Hi @PhilipTreacy ,

please check i have added the sample dataset.

Thanks