Power BI Practice Solution ROUND 7 I Apply CONDITIONAL FORMATTING for each individual row in a table

Applying custom conditional formatting for each individual row in a table can be tricky. In this Power BI Practice Round we highlight the highest and lowest prices row by row for each city. The dataset contains the price development for different foods over time for different countries.

Requirements:
- Create a matrix table that shows the average price per month and year (on columns) and country (on rows)
- Add slicers for country and commodity
- Choose the country Armenia, commodity carrots, and year 2016 as a filter
- Write a dax measure that returns red when the price equals the highest price for that country and green when it equals the lowest price
- Duplicate the page and remove the filter on year for that page
- Add another measure that does the same as the measure before, however checks each price vs highest and lowest price for a certain country AND year

Share your approach, questions, feedback below or on Twitter using the hashtag #PowerBIPractice and tag @HowToPowerBI

--------------------------------
📊 TRAININGS 📊
---------------------------------

---------------------------------
😍 JOIN 😍
----------------------------------

---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------

* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!

Thanks for being a part of this channel and all your support! 💪 🙏

#HowToPowerBI​ #PowerBI​ #DataTraining​
#powerbidesktop​ #powerbitraining​ #powerbideveloper​ #DAX

[HowtoPowerBI]

What do you think about this Practice Round?

[cavanagd]

I just love this series so much. I've been following along and also taking some notes on a MS word document. I thought I'd paste it here, it may be useful for people as MS has updated the layout of Power BI since this video came out and it can actually be a little hard to find where to put in the CF measure (I'd never used a measure in CF before). Also thank you so much Baz you really have a knack for getting through complicated things quickly and efficiently and explaining how this stuff works step by step. Hope this is useful for some people:

NOTES:
This is a conditonal formatting measure that lets you highlight max/min values in both columns (for years/months for example) and for rows (for cities/countries, for example)

First of all, know where MS hide conditional formatting (Of course, like everything with MS, they design it for weirdos so it’s hidden behind 50 clicks)
Click the dropdown arrow for ‘average of price’ -> conditional formatting -> background colour -> format style -> field value (It took me ages to find this) Then put in the measure that you create in the datatable

Back to the formula:
You create multiple variable rules
First you apply a calculate formula to filter down (or better to say, to remove the filter) do this for max and for min
Then you have an IF statement (Well actually a Switch statement) linking the VAR rule so that if it’s maxprice it’s “RED” and minprice it’s “GREEN”. If you know the Hex code you can put it in there too. Then don't forget to finish the VAR with Return and Result

CF MINMAX =
VAR PRICE =
VAR MaxPrice =
CALCULATE(
MAX(food_prices[Price]),
ALLSELECTED(dimDate[Month], dimDate[MonthNo])
)
VAR MinPrice =
CALCULATE(
MIN(food_prices[Price]),
ALLSELECTED(dimDate[Month], dimDate[MonthNo])
)
VAR Result =
SWITCH(
TRUE(),
Price=MaxPrice, "RED",
Price=MinPrice, "Green"
)
Return
Result

[susanlee]

This video is OUTSTANDING! Thank you for putting it together. Your solution is ALMOST 100% what I need, but I need to create conditional formatting based on a measure, and not a column within the data. From what I can tell the "SELECTEDVALUE" will only work with table columns. I've tried changing it to "SELECTEDMEASURE" but that was not successful. I'm brand new to variables within Power BI, so forgive me if this is a very basic skill I'm missing.

[sandeepreddy]

You're videos are much informative. Keep share your knowledge with us.

[jankucera]

Hello, nice tutorial, thank you. But I am not able to usi it in my case, as you used dimDate as a diferent table and I have all data in one table. Any idea how to solve this part? Thank you

[will]

Best way to build dax.. and to understand...

[Lgp]

Hello, you have a great video. Do you have the tutorial video to build the table visual that appears in this video (a table based on monthly date like yours or based on quarter )? If you have it would u give me the keyword or link perhaps? Thank you very much

[varshakrishna]

Thanks I found the solution in this after video after struggling for 2days …. My issue was I need to have conditional formatting for only max column(same measure spread across matrix table for last 5 weeks) so I need to do conditional formatting for latest week only

[wheninrome]

i am fairly new to Power Bi. i am trying to create a scorecard with metrics which have different targets, (green - good, yellow - needs, improvement and red - alert) some of these metric targets are 100% and some the targets are 0%. we would like the conditional formatting to work for the whole table (with varying targets). I have tried several things but can't seem to get it to work completely. any advice would be appreciated.

this is my latest attempt.

Colors_BG5 =
VAR TargetS = Percentage])
VAR Result =
IF(TargetS>=[LOW TARGET%], "green"&
IF(TargetS<=[HIGH TARGET%], "green"&
IF(TargetS>=[LOW THRESH%], "yellow"&
IF(TargetS<=[HIGH THRESH%], "yellow"&
IF(TargetS>=[LOW FAIL%], "red"&
IF(TargetS<=[HIGH FAIL%], "red"

RETURN
Result

[feketefe]

Hi
First of all, great videos!
Second - Don't know why on my matrix it doesn't work perfectly ( some rows has no color for values)
But my question Is - how to create a measure with SCALE conditional formatting for each row?
ROWS: Type of Rooms
Column: Booking Window, based on creation date
Values: Av. price
Looking forward for some tips!

[Chennu-zzme]

Hi, could you please do a video on conditional formatting with Rank. Bottom rank rows should show in red colour

[mustahsenali]

HI
Could you please make videos on Data Modelling and DAX?

[oricchannel]

Very useful video. Thank you for sharing! Is there a chance for new videos for this serie ?

[pilarbaldominos]

Fantastic video very clear and explaining all potential problems! Thanks for your help

[genevieve]

Hi Bas - Always good exercices - I hope you will create additional Round in 2023!
I have one question :
In the PBI version dec2022, it seems that the Switch function is not allowed in conditional formatting (in CELL ELEMENTS of format menu)
--> do you have a workaround ? For the moment, I have duplicated the pivot table and I have created 2 measures : one min and one max but wondering if an IF function with 2 arguments can work but does not know how to build it...
Thank you.

[DanielADamico]

Your videos are awesome! Thanks for sharing! 👏👏

[speulstra]

Hi very useful video, thanks for that. Is it also possible to highlight the rows by a range and the columns by a range based on categories, not a measure? Lets say I have 10 categories on the y-axis and 10 categories on the x axis of a matrix, now I want to conditionally highlight lets say categorie 3 to 7 of the x axis and 4 till 8 on the y axis. So the conditional format should be dynamic for both category axis, based on the category axis, not the measure! And I don’t want to filter away any row or column only highlight them.

[gFowmy]

Great videos!

Removing the sort column for month is not something that everyone is aware of

[ongz]

Cool stuff!! Any idea how one would create a diverging color scale on each row, with the min value being the most green, mid-value being orange, and the highest red?

[haboos]

here is my solution:

Color = var maxv = CALCULATE(MAX(food_prices[Price]), ALLEXCEPT(food_prices, food_prices[Country], food_prices[City], food_prices[Commodity], dimDate[Year]))
var minv = CALCULATE(MIN(food_prices[Price]), ALLEXCEPT(food_prices, food_prices[Country], food_prices[City], food_prices[Commodity], dimDate[Year]))
var selected=
var color=if(selected=minv, "Green", IF(selected=maxv, "Red"))
return color

i think your solution in in a higher level :D

waiting for Round 8 :D