Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jitpbi
Post Patron
Post Patron

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

Hi,

 

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

 

Spoiler
ICRInverterSCBUnitCount of AnomalyDateTime
ICR1INV1SCB10U111/17/2021 7:17
ICR1INV1SCB10U111/17/2021 7:27
ICR1INV1SCB10U111/17/2021 8:19
ICR1INV1SCB10U111/17/2021 8:24
ICR1INV1SCB10U111/17/2021 9:21
ICR1INV1SCB10U111/17/2021 9:31
ICR1INV1SCB10U111/17/2021 9:42
ICR1INV1SCB10U111/17/2021 10:15
ICR1INV1SCB10U111/17/2021 11:36
ICR1INV1SCB10U111/17/2021 15:18
ICR1INV1SCB10U111/17/2021 16:20
ICR1INV1SCB10U111/17/2021 17:17
ICR1INV1SCB10U111/17/2021 17:27
ICR1INV1SCB10U111/17/2021 18:14
ICR2INV1SCB10U111/17/2021 7:17
ICR2INV1SCB10U111/17/2021 7:27
ICR2INV1SCB10U111/17/2021 8:19
ICR2INV1SCB10U111/17/2021 8:24
ICR2INV1SCB10U111/17/2021 9:21
ICR2INV1SCB10U111/17/2021 9:31
ICR2INV1SCB10U111/17/2021 9:42
ICR2INV1SCB10U111/17/2021 10:15
ICR2INV1SCB10U111/17/2021 11:36
ICR2INV1SCB10U111/17/2021 14:16
ICR2INV1SCB10U111/17/2021 14:21
ICR2INV1SCB10U111/17/2021 14:36
ICR2INV1SCB10U111/17/2021 15:02
ICR2INV1SCB10U111/17/2021 15:18
ICR2INV1SCB10U111/17/2021 17:17
ICR2INV1SCB10U111/17/2021 17:27
ICR2INV1SCB10U111/17/2021 18:14
ICR1INV1SCB10U211/17/2021 18:14
ICR2INV1SCB10U211/17/2021 18:14

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

 

jitpbi_0-1611577113994.png

jitpbi_0-1611577807786.png

 

 

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 

 

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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 greenin 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.

View solution in original post

4 REPLIES 4
jitpbi
Post Patron
Post Patron

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
Community Support
Community Support

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 greenin 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
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi @PhilipTreacy ,

 

please check i have added the sample dataset.

 

Thanks

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.