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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
PamWren
Frequent Visitor

Conditional Formatting in Power BI

Hi,

 

I have a matrix which shows apprentices and planned end dates, with amounts paid per month for training.  I want to highlight any payments made after the end date but I can't figure out how to do this.  Any help gratefully received.  Screenshot below:

 

PamWren_0-1700732306422.png

 

So the top apprentice has an end date of Sept 2023 but a payment was made in October 2023, this is the figure I wish to highlight.

 

Many Thanks

2 REPLIES 2
TobyNye
Resolver II
Resolver II

Just realised I didn't mention how you use this measure, sorry! You will want to click on the matrix visual with your data then navigate to cell elements and select the value you want to apply this to:

TobyNye_1-1700739264470.png

 

Then turn on the conditional formatting you want to apply (I have chosen background in this case) and click the Fx button next to it, this should open this window for you:

TobyNye_2-1700739335095.png

Click the dropdown on Format Style and choose Field Value, then in the 'What Field should we base this on?' field, choose the measure you created (in my case it would be 'Conditional Format Value').

TobyNye
Resolver II
Resolver II

I made a sample power bi dataset based on how I think your data is structured, I have an employee table containing the employee name and the end date. I have another table called Payments which contains the employee; payment date and paid amount. These are linked on the employee name column. I believe the below meets your requirements:

TobyNye_0-1700739029026.png

 

The measure is effectively calculating the amount paid after the end date then returning a colour if the date in the table is larger than the end date and the paid amount after end date is not 0. Raw measure code:

Conditional Format Value =
VAR _end = SELECTEDVALUE(Employee[End Date])
VAR _check = CALCULATE(SUM(Payments[Paid Amount]), FILTER(ALL(Payments),
                                                        Payments[Employee] = SELECTEDVALUE(Employee[Employee])
                                                        && Payments[Payment Date] > _end
)
)
RETURN
IF(_check <> 0 && SELECTEDVALUE(Payments[Payment Date]) > _end, "Yellow" /*Put your colour here, can be a hexcode*/, BLANK())
 
Hope this helps, let me know if you have any issues or further questions.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.