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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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