Forum Discussion

jeanL's avatar
jeanL
Helper I
1 year ago
Solved

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...
  • v-sgandrathi's avatar
    v-sgandrathi
    1 year ago

    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.

maruthisp's avatar
maruthisp
Super User
1 year ago

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: