Forum Discussion

AmazingRandom's avatar
AmazingRandom
Helper II
2 years ago
Solved

IF function with conditional formatting

I have been asked to prepare a table where values who surpass a threshold (180, 360, 900 or 1800) or equal to it are to be highlighted, as well as have any value less than 1800 to be represented as <...
  • SamInogic's avatar
    SamInogic
    2 years ago

    Hi AmazingRandom ,

    As per our understanding you have above mentioned data in your table and you want a way for applying background color based on your condition, then you need to create a custom column with below expression,

    Lab result 1 colour = if( CONTAINSSTRING('Table'[Lab Result 1], "<") || CONTAINSSTRING('Table'[Lab Result 1], "-") ,"","Yellow")

    Create individual columns for other 5 Lab Results and you can set the conditional formatting using Field Value as shown in the below screenshot,

     

    After applying same formatting to each column, this will result as below,

    Thanks!

     

    Inogic Professional Services Division

    Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!

    Drop an email at [email protected]

    Services:  http://www.inogic.com/services/

    Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

123abc's avatar
123abc
Community Champion
2 years ago

Create a New Column for Display:

Create a new column that displays the values based on the conditions you provided.

 

DisplayColumn =
IF([Lab Result 1] >= 1800, "<1800",
IF([Lab Result 1] >= 360, "<360",
IF([Lab Result 1] >= 180, "<180",
IF([Lab Result 1] >= 900, "<900", BLANK()))))

 

  1. Repeat this for each Lab Result column.

  2. Conditional Formatting Rules: Use conditional formatting rules to highlight cells based on their values.

    • Select the column you want to format.
    • Go to the "Format" pane.
    • Under the "Conditional formatting" section, select "Background color."
    • Choose "Color scales" and adjust the colors based on your preference.

    For example, set the color scale to go from light green (lowest values) to dark green (highest values). This will visually represent the hierarchy of values.

  3. Apply Conditional Formatting to All Relevant Columns: Apply the conditional formatting rules to all Lab Result columns.

  4. Handle Blank Cells: To keep empty cells blank, you don't need to explicitly mention them in the IF statement. The BLANK() function will handle this for you. The conditional formatting rules will also work with blank cells.

  5. Dynamic Data Refresh: If your data is refreshed, the DAX formulas will recalculate, and the conditional formatting rules will be reapplied automatically.

Here's a generic example for one column. You can adapt it for other columns:

 

DisplayColumnLabResult1 =
IF([Lab Result 1] >= 1800, "<1800",
IF([Lab Result 1] >= 360, "<360",
IF([Lab Result 1] >= 180, "<180",
IF([Lab Result 1] >= 900, "<900", BLANK()))))

 

Remember to adjust the column names and conditions according to your actual column names and requirements.