Conditional formatting

[FlavioMe3101]

Hello, I am facing a challenge with a business intelligence (BI) project that I am working on. The project involves displaying the expiration status of some fruits in a table.  The table has the first column as the batch of fruits and the second is the expiration date. I want to apply a conditional formatting rule to the table, such that the cells in the date column change their color based on a expiration date. Basically I want that all dates after the expiration date become colored, even when there is no data on it. How can I achieve this functionality in my BI tool?

[FlavioMe3101]

IT Worked!!!!!!!!  Thank you guys!!!!. all you guys help me a lot!!!!!! I really apreciate for it!!!!!!!!

[Anonymous]

Hi @FlavioMe3101 ,

Both @PijushRoy and @MFelix are all capable users and you can trust them! Their advice are fine, and you can accept them.

If you're a beginner and still have doubts about measures or instructional documentation, I'll provide a step-by-step process to achieve the effect on your screenshots. Take a look:

Supposing that your have a table1 has 2 columns and table2 has 3 columns as follows.

1.Create a calendar table to get full dates(from 1/1/2024 to 1/13/2024).

Under Modeling, click 'New table' and enter the below formula.

Calendar = CALENDAR(DATE(2024,1,1),DATE(2024,1,13))

2.Create a matrix visual, and put fields into it.

Measure = CALCULATE(SUM('Table (2)'[Count]),FILTER('Table (2)',[Date]=MAX('Calendar'[Date])))

3.Turn off the stepped layout option, you will get the expected layout.

4.Create another measure for conditional formatting.

Measure 2 = IF(MAX('Calendar'[Date])>=MAX('Table'[Valid]),"red")

Then the important conditional formatting is begun here.

As you can see, the blank cells don't have a background color, which you can fix by modifying the previous measurements. This is the modified measure.

Measure = var _value=CALCULATE(SUM('Table (2)'[Count]),FILTER('Table (2)',[Date]=MAX('Calendar'[Date])))
return IF(_value=BLANK(),"",_value)

Hopefully, the steps I have followed above will be helpful to your business intelligence (BI) project.

The key to success above is also the creation of relationships, which you can download my attachment to view in the model view.

Best Regards,

Stephen Tao

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

[MFelix]

Hi @FlavioMe3101 ,

Create a measure similar to this one:

Color format = IF(MIN(Calendar[Date]) >= SELECTEDVALUE(Table[Valid]) , "Red")

 Then use this metric has your condittional formatting.

I assume that the date you have on the columns comed from a calendar table or similar.

[PijushRoy · Accepted solution]

Hi @FlavioMe3101 

Please follow the articles that shows all options of conditional formatting 
https://blog.coupler.io/power-bi-conditional-formatting/

For your requirement please follow point no 4, conditional formatting with measure.
Please create a measure for Date change and use the measure as conditional formatting.

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
Linkedin