Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
I am trying to change the background color on a card based on the calculated measure.
What I have:
Employee Name | Gender | City_Index | Country_Index |
Zen | Male | -87 | 50 |
Kim | Male | 0 | 36 |
Sharyn | Female | -45 | 22 |
Lexi | Female | -66 | 30 |
Maria | Female | -91 | 1 |
John | Male | -70 | 11 |
Shan | Male | -23 | 0 |
Calculated Average Measure:
Average_City_Index = CALCULATE(AVERAGE(Table[City_Index), FILTER(Table,Table[City_Index] <> 0 ))
Average_Country_Index = CALCULATE(AVERAGE(Table[Country_Index), FILTER(Table,Table[Country_Index] <> 0 ))
Measure to change color:
Condition_Measure = MAXX(Table,
IF([Average_City_Index] >= -50 && [Average_City_Index] <= -1, 1,
IF([Average_City_Index] >= -75 && [Average_City_Index] <= -51, 2,
IF([Average_City_Index] >= -100 && [Average_City_Index] <= -76, 3, 4))))
Background Condition
Result:
When I create a card to get count of employee and then filter it based on gender in the filter section. The value I get is 4 which is right because there are 4 males but the background color is light blue instead of yellow, the average of city_index for male is -60.
Solved! Go to Solution.
Hi @Anonymous,
I think you can use 'field value' mode and direct to return color code and use on color formatting, it should more simple than rule mode.
Use conditional formatting in tables
BTW, maxx function seems not needed in your formula, after I remove this and modify your formula, it works well on my side.
Color =
VAR avgCity =
CALCULATE ( AVERAGE ( T1[City_Index] ), T1[City_Index] <> 0 )
RETURN
IF (
avgCity >= -50
&& avgCity <= -1,
"Green",
IF (
avgCity >= -75
&& avgCity <= -51,
"Yellow",
IF ( avgCity >= -100 && avgCity <= -76, "Red", "light Blue" )
)
)
If above not helps, please share more detailed information to help us clarify your scenario.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @Anonymous,
I think you can use 'field value' mode and direct to return color code and use on color formatting, it should more simple than rule mode.
Use conditional formatting in tables
BTW, maxx function seems not needed in your formula, after I remove this and modify your formula, it works well on my side.
Color =
VAR avgCity =
CALCULATE ( AVERAGE ( T1[City_Index] ), T1[City_Index] <> 0 )
RETURN
IF (
avgCity >= -50
&& avgCity <= -1,
"Green",
IF (
avgCity >= -75
&& avgCity <= -51,
"Yellow",
IF ( avgCity >= -100 && avgCity <= -76, "Red", "light Blue" )
)
)
If above not helps, please share more detailed information to help us clarify your scenario.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@Anonymous , Not very clear. You can create a color measure and use that in conditional formatting by choosing "Field value" option
Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")
Color Year = if(FIRSTNONBLANK(Table[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))
Color = if(FIRSTNONBLANK(Table[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170
,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))
Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")
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-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692
You know you can provide colors directly in your measure and then use field values instead of rules to drive the formatting, right?