topic Re: Calculated measure for conditional formatting returns inconsistent values. in DAX Commands and Tips

Calculated measure for conditional formatting returns inconsistent values.

egorlinus — Tue, 06 Sep 2022 06:52:48 GMT

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(),

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.

Settings for conditional formatting are as under:

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.

Re: Calculated measure for conditional formatting returns inconsistent values.

v-easonf-msft — Wed, 07 Sep 2022 08:21:05 GMT

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

Re: Calculated measure for conditional formatting returns inconsistent values.

egorlinus — Wed, 07 Sep 2022 11:57:21 GMT

Hi,

Thanks for the response.

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

I will share PBIX on PM.

Regards.

Re: Calculated measure for conditional formatting returns inconsistent values.

egorlinus — Wed, 07 Sep 2022 12:03:53 GMT

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,

Re: Calculated measure for conditional formatting returns inconsistent values.

egorlinus — Thu, 15 Sep 2022 07:26:16 GMT

Please find below the link to PBIX file and help.

Regards,

egorlinus

Re: Calculated measure for conditional formatting returns inconsistent values.

egorlinus — Mon, 12 Sep 2022 04:13:58 GMT

I modified the condition and it works fine now.

Re: Calculated measure for conditional formatting returns inconsistent values.

egorlinus — Mon, 12 Sep 2022 08:47:41 GMT

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?

Re: Calculated measure for conditional formatting returns inconsistent values.

v-easonf-msft — Tue, 13 Sep 2022 06:30:06 GMT

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

Re: Calculated measure for conditional formatting returns inconsistent values.

egorlinus — Thu, 15 Sep 2022 07:21:19 GMT

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.