colour status of device based on fault counts

[jitpbi]

Hi,

I need to show the status of device (with 3 different colours) based on count of faults in the last 5 days.

these are the conditions to show the status with diff colours:

1. if fault counts are less than 10 for today then green colour

2. if fault counts are more than 10 for today and less than 10 for any day in the last 5 days then orange colour

3. if fault counts are more than 10 for today and more than 10 for each day in the last 5 days then red colour

the sample data is there in the below link:

https://drive.google.com/file/d/1v0Gv4FiXlp3vb6JmiBrnfXxjHTyOiaGs/view?usp=sharing

Please help me how to achieve this.

Thanks

[v-robertq-msft · Accepted solution]

Hi, @jitpbi 

According to your description, you want to set the conditional format for the chart basd on the value of fault count, right? You can try my steps:

1. Create two measures:

Count of fault =

COUNTX(FILTER(ALL('Table'),[ICR]=MAX([ICR])),[ICR])

Color =

var _last1daycount=

COUNTX(FILTER(ALL('Table'),[ICR]=MAX([ICR])&&[DateTime]=MAX([DateTime])-1),[ICR])

var _last2daycount=

COUNTX(FILTER(ALL('Table'),[ICR]=MAX([ICR])&&[DateTime]=MAX([DateTime])-2),[ICR])

var _last3daycount=

COUNTX(FILTER(ALL('Table'),[ICR]=MAX([ICR])&&[DateTime]=MAX([DateTime])-3),[ICR])

var _last4daycount=

COUNTX(FILTER(ALL('Table'),[ICR]=MAX([ICR])&&[DateTime]=MAX([DateTime])-4),[ICR])

var _last5daycount=

COUNTX(FILTER(ALL('Table'),[ICR]=MAX([ICR])&&[DateTime]=MAX([DateTime])-5),[ICR])

return

IF(

    [Count of fault]<=10,"Green",

    IF(

        _last1daycount<10&&_last2daycount<10&&_last3daycount<10&&_last4daycount<10&&_last5daycount<10,"orange",

        "Red"))

2. Create a Matrix and place like this:

3. Set conditional format for the Matrix, like this:

And you can get what you want, like this:

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@jitpbi ,  You have to create a measure like the given example and use that is conditional formatting with the "Field value" option

Switch(true(),

[fault counts today]  <=10 , "green",

[fault counts today]  >10 && [fault counts last 5 days] <=10 , "orange",

"red"

)

check for steps

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values