Calculated measure for conditional formatting returns inconsistent values.

[egorlinus]

Hi Experts,

I have a typlical scenario wherein the caluclated measure created for conditional formatting is behaving inconsistently.

Measure is defined as:

Color subtotal =

var _laying         = CALCULATE([Total SES Qty],FILTER(All(Activity), Activity[Activity_ID] = "01L"))

var _main_sor       = CALCULATE([Total SES Qty],FILTER(All(Activity), Activity[Activity_ID] IN {"01L", "02O", "03V", "04R"}))

var _commissioning  = CALCULATE([Total SES Qty],FILTER(All(Activity),Activity[Activity_ID]="05C"))

var _documentation  = CALCULATE([Total SES Qty],FILTER(All(Activity),Activity[Activity_ID]="06D"))

var _safety         = CALCULATE([Total SES Qty],FILTER(All(Activity),Activity[Activity_ID]="07S"))

var _barricading    = CALCULATE([Total SES Qty],FILTER(All(Activity),Activity[Activity_ID]="08B"))

var _selection      = SELECTEDVALUE(Activity[Activity_ID])

return

SWITCH(TRUE(),

    AND(

        (_selection = "05C"), (_commissioning <= _main_sor)

        ), 1,

    AND(

        (_selection = "06D"), (_documentation <= _main_sor)

        ), 1,

   AND(

        (_selection = "07S"), (_safety <= _laying)

        ), 1,

    AND(

        (_selection = "08B"), (_barricading <= 2*_laying)

        ), 1,

    _selection IN {"01L", "02O", "03V", "04R"}, BLANK(),

    2)

Expected result : If quantity for safety is <= that for laying, then measure returns the value 1 and background color should be green. It should be red otherwise. The measure is reutning value 2 for the first row in the below screen shot (marked with arrow) and hence color is red. However it should be green. However the result is correct for the second row formatted in green color.

The details (data) for both the rows is displayed below the matrix. Data for the problem scenario is circled in red.

first row of the matrix has incorrect formatting

Settings for conditional formatting are as under:

Conditional Formatting

I checked the values returned by the measure. It is returning 2 for the problem scenario. I incresed the decimal places to 4 to identify rounding off issues, but that is not the case. Please help me resolve this typical behaviour. 

@SolomonovAnton  - You had helped me with setting up this conditional formatting. You might understand this better. Please help.

[egorlinus]

I modified the condition and it works fine now.

[v-easonf-msft]

Hi， @egorlinus 

Can you share a sample pbix for research?

Please try juxtaposing multiple cases in a switch function using "||".

Color subtotal =
var ..........

RETURN
    SWITCH (
        TRUE (),
        AND ( ( _selection = "05C" ), ( _commissioning <= _main_sor ) )
            || AND ( ( _selection = "06D" ), ( _documentation <= _main_sor ) )
            || AND ( ( _selection = "07S" ), ( _safety <= _laying ) )
            || AND ( ( _selection = "08B" ), ( _barricading <= 2 * _laying ) ), 1,
        _selection IN { "01L", "02O", "03V", "04R" }, BLANK (),
        2
    )

Best Regards,
Community Support Team _ Eason

[egorlinus]

Hi

Please find below the link to PBIX file and help.

Regards,

egorlinus

[egorlinus]

The change resolved the specific scenario but caused issues with other rows. So the issue persists. I am surprised that no one is able to help resolving this. Is it a product bug?

[v-easonf-msft]

Hi, @egorlinus 

I don't seem to have a similar problem here.

Which version of PowerBI are you currently using? Please download the latest version of PowerBI and test it again.

Best Regards,
Community Support Team _ Eason

[egorlinus · Accepted solution]

Hi

I am using the Aug-22 version.

Problem was with the values stored in the variables. I rounded off all the variable values and the issue is finally resolved.

Thanks for your response though as no one else responded.

Regards.

[egorlinus]

Hi Team,

I am unable to send private message to you. Please share your email id so that I can send you the pbix file.

regards,

[egorlinus]

Hi,

Thanks for the response.

I changed the measure as per your suggestion, but the issue persists.

I will share PBIX on PM.

Regards.