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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jitpbi
Post Patron
Post Patron

colour status of device based on fault counts

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

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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"))
  1. Create a Matrix and place like this:

v-robertq-msft_0-1611904481444.png

 

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

v-robertq-msft_1-1611904481452.png

 

And you can get what you want, like this:

v-robertq-msft_2-1611904481456.png

 

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.

View solution in original post

2 REPLIES 2
v-robertq-msft
Community Support
Community Support

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"))
  1. Create a Matrix and place like this:

v-robertq-msft_0-1611904481444.png

 

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

v-robertq-msft_1-1611904481452.png

 

And you can get what you want, like this:

v-robertq-msft_2-1611904481456.png

 

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

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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