Forum Discussion

egorlinus's avatar
egorlinus
Frequent Visitor
3 years ago
Solved

Calculated measure for conditional formatting returns inconsistent values.

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.

 

 

DAX Switch()
  • egorlinus's avatar
    egorlinus
    3 years ago

    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.

8 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    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's avatar
      egorlinus
      Frequent Visitor
      3 years ago

      Hi,

      Thanks for the response.

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

      I will share PBIX on PM.

      Regards.

    • egorlinus's avatar
      egorlinus
      Frequent Visitor
      3 years ago

      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's avatar
      egorlinus
      Frequent Visitor
      3 years ago

      Hi

      Please find below the link to PBIX file and help.

       

       

      Regards,

      egorlinus

      • egorlinus's avatar
        egorlinus
        Frequent Visitor
        3 years ago

        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?