Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello -
I would like to apply conditional formatting by ROW based on a measure. For example, I have a number of bids to compare by site. I would like to change the color of the MIN bid in each row (by site). I've tried a few things I've seen posted but can't quite figure it out. Any suggestions is greatly appreciated.
Thanks!
Christine
Solved! Go to Solution.
I was able to figure this out thanks to so many of you who post tips, tricks, and solutions. This is my first Power BI dashboard and had no idea about DAX functions. After reading many posts, below is how I made it work.
First, I created 2 new Columns and 1 new Measure.
Lowest Rate =
CALCULATE(
MIN(
'All Responses'[2. F1 Rate]),
FILTER('All Responses','All Responses'[1. Store ID]=EARLIER('All Responses'[1. Store ID])
)
)
Winner =
IF('All Responses'[2. F1 Rate]='All Responses'[Lowest Rate],"1","0")
Background Color =
VAR HoldColor = SELECTEDVALUE('All Responses'[Winner])
RETURN
IF HoldColor = "1", "#A0D1FF")
Then I did Conditional Formatting on the new Measure.
Just did a quick slicer and it appears to be working correctly. Perhaps its the FILTER / EARLIER functions that is enabling this to work.
Thank you for the insight. I have not gotten to slicers yet and will be working on the rest of the dashboard tomorrow. I will update with ALLSELECTED and give it a whirl.
The only problem with your solution is that it'll not work correctly with all slicers and selections. This is because you have not used ALLSELECTED in your code. Your solution is STATIC, meaning that it'll not respond to slicers and filtering correctly. Try it and you'll see...
I was able to figure this out thanks to so many of you who post tips, tricks, and solutions. This is my first Power BI dashboard and had no idea about DAX functions. After reading many posts, below is how I made it work.
First, I created 2 new Columns and 1 new Measure.
Lowest Rate =
CALCULATE(
MIN(
'All Responses'[2. F1 Rate]),
FILTER('All Responses','All Responses'[1. Store ID]=EARLIER('All Responses'[1. Store ID])
)
)
Winner =
IF('All Responses'[2. F1 Rate]='All Responses'[Lowest Rate],"1","0")
Background Color =
VAR HoldColor = SELECTEDVALUE('All Responses'[Winner])
RETURN
IF HoldColor = "1", "#A0D1FF")
Then I did Conditional Formatting on the new Measure.
I can't sem to find the magic combination. I'm new to Power BI and DAX. I have the lowest bid (MIN) per location in a measure that works correctly. I just need to figure out how to turn that measure into a value that can be used in the Conditional Formatting.
The solution seems to be in getting a RANKX by location then doing a SWITCH to set the color. I'll keep working down that path.
Write a measure that for each Location will return the rank of the bid. The smallest bid should have rank 1. Use this measure's value in the conditional formatting. The measure will have to use ALLSELECTED somehow. Or, alternatively, you can write a measure that will return the smallest bid for each location. Then you can use this value in the conditional formatting as well. All measures you're gonna write will call for ALLSELECTED.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |