cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Conditional formatting help required?

Hi All,

 

I Seem to be stuck at what should be quiet a simple task. 

 

I have a table with four columns (fields) per row. All i want to is highlight each time the value in column 1 is less than any of the other 3 column valves.

 

As always any help would be greatly appreciated

 

KR

 

1 ACCEPTED SOLUTION
Solution Sage
Solution Sage

Hi @dmoggie

 

You can create the following Measure 

back color = if(COALESCE([Val 1]<[Val 2], [Val 1]<[Val 3], [Val 1]<[Val 4])= BLANK(), "#FFFFFF", "#008000")

then use the Format by Field Value type of conditional formatting

 

richbenmintz_0-1596633166758.png

 

Linkt to Sample pbix, sample.pbix 

 

Hope this Helps,

Richard


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

 

View solution in original post

7 REPLIES 7
Solution Sage
Solution Sage

Hi @dmoggie

 

You can create the following Measure 

back color = if(COALESCE([Val 1]<[Val 2], [Val 1]<[Val 3], [Val 1]<[Val 4])= BLANK(), "#FFFFFF", "#008000")

then use the Format by Field Value type of conditional formatting

 

richbenmintz_0-1596633166758.png

 

Linkt to Sample pbix, sample.pbix 

 

Hope this Helps,

Richard


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

 

View solution in original post

Wow so many quick replies, what a great forum. I didnt know you could resolve in all these different ways

 

@tex628 thanks.

 

This was the missing bit for me. Many thanks @richbenmintz 

 

Kind Regards

Memorable Member
Memorable Member

Hi @dmoggie ,

 

Follow the below steps:

1. Create a new column with below condition:

Color_Column = if(Tablename[Column1] < Tablename[Column3],"Color's Hex Code","")
 
2. Go to Format -> conditional Formatting -> Enable Background color -> Format by: Field Value and Based on Field: Color_Column
 
Check below for example(Highlight Column1(Sixes) if No. of Sixes < No. of Fours(Column3)):
bg change.PNG
 
Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!
Super User II
Super User II

Assuming that it's measures you have it should be something along the lines of:

Measure = 
IF( [Measure 1] < [Measure 2] , 1 ,
IF( [Measure 1] < [Measure 3] , 1 ,
IF( [Measure 1] < [Measure 4] , 1 , BLANK()
)))


If you're just column aggregating i would recommend creating 4 measures instead:

Measure 1 = SUM(Table[Column1])

etc. 

Finally apply conditional formatting to Measure 1 and use the "Based on field" condition: 
image.png
Where you choose the measure written above with the condition 1 = Colored

Br,
J




Connect on LinkedIn
Super User II
Super User II

Hi @dmoggie  - create a measure or a column (depending on what your values are already), that produces 1 if your condition is met and 0 if it doesn't.  Then do conditional formatting on that field, but using the value of the new measure/column.

 

Hope this helps

David




Proud to be a Super User!




Super User IV
Super User IV

@dmoggie , create a color measure like this and use that in conditional formatting with "Field" option

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions
"red"
)

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Solution Sage
Solution Sage

Hi @dmoggie,

 

Can you provide some sample data and a desired outcome

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors