Forum Discussion
Condition Formating with Calculation group
I have a calculation group set up for selection of both percentile and metrics. Different metrics will have different band targets.
Problem occurs when i try to set up for condtional formatting, the colors are not coming off correctly.
Ideally for this < 15 is green, between 15 and 30 is amber and above 30 is red.
ConditionalFormatting =
VAR SelectedMetricOrder = SELECTEDVALUE(MetricSelection[Metric Order])
VAR SelectedMetricValue =
SWITCH(
SelectedMetricOrder,
0, [First Assigned],
1, [First Reply],
2, [First Resolution],
3, [First Reply w Booking Confirmation],
4, [First Resolution w Booking Confirmation],
BLANK()
)
VAR Target1 = [Band 1 Target]
VAR Target2 = [Band 2 Target]
RETURN
SWITCH(
TRUE(),
ISBLANK(SelectedMetricValue), BLANK(),
SelectedMetricValue <= Target1, 1, //green
SelectedMetricValue > Target1 && SelectedMetricValue <= Target2, 3, //amber
SelectedMetricValue > Target2, 2, // red
BLANK()
)
Hi jeanL,
You're absolutely right.
Since you're using Field parameters or dynamic field selection, which your field [SelectedMetricValue] implies, Power BI internally uses implicit measures. This means that [SelectedMetricValue] is not a normal scalar value and cannot be referenced directly in a calculated measure like VAR Value = [SelectedMetricValue], as it results in a syntax error.
To support dynamic metric evaluation using implicit measures, the correct approach is to use SELECTEDMEASURE().
SELECTEDMEASURE() returns the current measure being evaluated in the visual, which is ideal for when you're using field parameter slicers for metric switching. It respects context, unlike trying to hardcode [SelectedMetricValue].
I hope my suggestions provided valuable insights. If you have any further questions, don’t hesitate to ask in a follow-up message.
If this post helped, please mark it as "Accept as Solution" so others can benefit as well.Best regards,
Sahasra.
6 Replies
Hi jeanL,
As per my understanding:
Your original code set amber as 3 and red as 2, just be sure your color mappings in Power BI reflect this order. Many users use 1 = green, 2 = amber, 3 = red, but your mapping is fine as long as you use it consistently in your color rules.
Please try to use below DAX, if that can helps:
ConditionalFormatting =
VAR SelectedMetricOrder = SELECTEDVALUE(MetricSelection[Metric Order])
VAR SelectedMetricValue =
SWITCH(
SelectedMetricOrder,
0, [First Assigned],
1, [First Reply],
2, [First Resolution],
3, [First Reply w Booking Confirmation],
4, [First Resolution w Booking Confirmation],
BLANK()
)
VAR Target1 = [Band 1 Target]
VAR Target2 = [Band 2 Target]
RETURN
SWITCH(
TRUE(),
ISBLANK(SelectedMetricValue), BLANK(),
SelectedMetricValue < Target1, 1, // green
SelectedMetricValue < Target2, 3, // amber
SelectedMetricValue >= Target2, 2, // red
BLANK()
)
While applying conditional formatting, make sure the rules are like below:1 = Green
2 = Red
3 = Amber
Please let me know if you have further questions.
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X - Maruthi Siva Prasad - (@MaruthiSP) / X
Hi jeanL,
Thankyou maruthisp for your reply on the issue.
Has your issue been resolved? If the response provided by the community member addressed your concern, kindly confirm.
Marking it as Accept Answer and give us Kudos if you found it helpful allows us to ensure that the solutions shared are valuable for the entire community.
If you have any further questions, feel free to reach out!
Thank you for your cooperation!Hi jeanL,
We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.
If you need any further assistance, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!
Hello
Thanks for responding to this.
I believe due to the implicit measures required, this doesnt apply in the field value.
Hi jeanL,
You're absolutely right.
Since you're using Field parameters or dynamic field selection, which your field [SelectedMetricValue] implies, Power BI internally uses implicit measures. This means that [SelectedMetricValue] is not a normal scalar value and cannot be referenced directly in a calculated measure like VAR Value = [SelectedMetricValue], as it results in a syntax error.
To support dynamic metric evaluation using implicit measures, the correct approach is to use SELECTEDMEASURE().
SELECTEDMEASURE() returns the current measure being evaluated in the visual, which is ideal for when you're using field parameter slicers for metric switching. It respects context, unlike trying to hardcode [SelectedMetricValue].
I hope my suggestions provided valuable insights. If you have any further questions, don’t hesitate to ask in a follow-up message.
If this post helped, please mark it as "Accept as Solution" so others can benefit as well.Best regards,
Sahasra.Hi jeanL,
I wanted to check in your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply as Accepted solution and give Kudos that helped you. It would be greatly appreciated by others in the community who may have the same question.
Thank you.
