Forum Discussion

Snagalapur's avatar
Snagalapur
Helper IV
1 year ago
Solved

Conditional formatting cards and Table values

Hi All,

Please suggest how to handle blank /NAN/ Infinity values in conditional formatting?

 

Current formula:

% Bud Color =IF('table'[% Bud] < 1, "#FF0000", "#00CC66")

 

Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Snagalapur 

    Based on your description, I've created the following example data to simulate your situation:

    I use divide as a division function instead of /. This enables safe division.

     

    % Bud Clean = 
VAR Denominator = SUM(BudgetPerformance[Denominator])
VAR Numerator = SUM(BudgetPerformance[Numerator])
VAR Ratio = DIVIDE(Numerator, Denominator) // Handles divide-by-zero as blank
RETURN
    IF(
        ISERROR(Ratio) || ISBLANK(Ratio), 
        BLANK(), // Mark invalid values (NaN/Infinity/Blank)
        Ratio
    )

    I've optimized the % Bud Color measure:

    % Bud Color = 
IF(
    ISBLANK([% Bud Clean]), 
    "#808080", // Gray for NaN, Infinity, or Blanks
    IF(
        [% Bud Clean] < 1, 
        "#FF0000", // Red for values < 1
        "#00CC66"  // Green for values >= 1
    )
)

    Here are the results:

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Snagalapur  - you can modify your dax formaule as below:

     

    % Bud Color =
    IF(
    ISBLANK([% Bud]) || [% Bud] = 0 || [% Bud] = NaN(),
    "#808080", -- Gray for blanks/NaN
    IF([% Bud] < 1, "#FF0000", "#00CC66")
    )

     

    Hope this works. please check.

    • Snagalapur's avatar
      Snagalapur
      Helper IV
      1 year ago

      Thank you for details.

      I did try it's not working currently i'm dealing with below issues.

      % Bud Color = IF('Table'[% Bud] < 1, "#FF0000", "#00CC66")

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi, Snagalapur 

        Based on your description, I've created the following example data to simulate your situation:

        I use divide as a division function instead of /. This enables safe division.

         

        % Bud Clean = 
VAR Denominator = SUM(BudgetPerformance[Denominator])
VAR Numerator = SUM(BudgetPerformance[Numerator])
VAR Ratio = DIVIDE(Numerator, Denominator) // Handles divide-by-zero as blank
RETURN
    IF(
        ISERROR(Ratio) || ISBLANK(Ratio), 
        BLANK(), // Mark invalid values (NaN/Infinity/Blank)
        Ratio
    )

        I've optimized the % Bud Color measure:

        % Bud Color = 
IF(
    ISBLANK([% Bud Clean]), 
    "#808080", // Gray for NaN, Infinity, or Blanks
    IF(
        [% Bud Clean] < 1, 
        "#FF0000", // Red for values < 1
        "#00CC66"  // Green for values >= 1
    )
)

        Here are the results:

         

         

        Best Regards

        Jianpeng Li

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.