cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PowerBIonical Frequent Visitor
Frequent Visitor

Conditional Formatting based on calculated column inc + and - returned results

Hello

 

I'm trying to change the background colour of a cell which is a returned result of a calculated column based on data from two other columns.

 

Background: I have a column of scheduled arrival, a column of actual arrival and a calculated column of how many minutes late/early it was.

 

I'd like to make the cell showing the early/late minutes Green for early/on time (a minus value), Amber for up to 5 minutes late and Red for 5 minutes + late.

 

The cells look like this:

 

Sch Arrival     Actual Arrival     Diff

8:00:00 AM    8:02:00 AM        2 mins

8:00:00 AM    7:59:00 AM        -1 mins

 

If it matters, the DAX function for the Diff column is as follows:

 

= Duration.ToRecord ([Arrival]-[Sch. Arrival])

 

Would anyone be able to point me in the right direction please? I've tried all of the in built conditional formatting options but they seem to based off the count of a column which doesn't work for me in this example.

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Conditional Formatting based on calculated column inc + and - returned results

@PowerBIonical 

You can write a measure that you will use just to format your text.

Format Measure = MAX ( 'Table'[Diff] )

Then in the conditional formatting you apply the rules to that field.

conditionalformat.jpg

7 REPLIES 7
Super User
Super User

Re: Conditional Formatting based on calculated column inc + and - returned results

@PowerBIonical 

You can write a measure that you will use just to format your text.

Format Measure = MAX ( 'Table'[Diff] )

Then in the conditional formatting you apply the rules to that field.

conditionalformat.jpg

PowerBIonical Frequent Visitor
Frequent Visitor

Re: Conditional Formatting based on calculated column inc + and - returned results

Hi @jdbuchanan71 

 

I really appreciate your help! I feel like I'm so close to solving this but I'm having a small issue.

 

I've made the measure as required:

 

Diff..JPG

 

It has then entered itself into the report as expected:

 

Format Measure.jpg

 

But when applying the rule, the measure is Greyed out and not selectable:

 

Font Colour - Diff.jpg

 

Do you know why this might be? I've tried putting the measure itself into the actual report instead of Diff, selecting it and de-selecting it etc to no avail.

 

I'd really appreciate further guidance.

 

Thank you @jdbuchanan71 

Community Support Team
Community Support Team

Re: Conditional Formatting based on calculated column inc + and - returned results

Hi @PowerBIonical ,

 

Please refer to the similar case: https://community.powerbi.com/t5/Desktop/Conditional-Formatting-based-on-percentage-of-multiple-valu... 

 

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
PowerBIonical Frequent Visitor
Frequent Visitor

Re: Conditional Formatting based on calculated column inc + and - returned results

Thank @v-diye-msft , I will look at that thread to get an idea about what I'm doing wrong although @jdbuchanan71  had me soooo close to solving this thing.

 

I know it's going to be something super simple too get this rectified but it's eluding me at the moment.

 

Would you happen to know why the measure isn't selectable in the conditional formatting pane?

 

Thank you

Community Support Team
Community Support Team

Re: Conditional Formatting based on calculated column inc + and - returned results

Hi @PowerBIonical ,

 

It's unselectable in my side either, he should prabably add the calculated column rather than measure.  that I'd like to suggest you add the format measure and then applied it to values measure.  and the formula won't be very complicated. you can refer to the second part of my solved cases above.

 

Best regards,

Dina Ye

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Community Support Team
Community Support Team

Re: Conditional Formatting based on calculated column inc + and - returned results

Hi @PowerBIonical ,

 

It seems i happen to know the point, what's the data type of your measure? it does work when I use whole number, but failed to use non-number type. 

 

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
PowerBIonical Frequent Visitor
Frequent Visitor

Re: Conditional Formatting based on calculated column inc + and - returned results

@jdbuchanan71  @v-diye-msft 

 

You two are the best!!!!

 

Thank you so much!

 

This is now solved so I will mark as complete.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 196 members 2,433 guests
Please welcome our newest community members: