IF function with conditional formatting

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.

Hi, AmazingRandom

May I ask if this is the expected output you are looking for? Based on your description, I made a visual as follows

Measures:

Measure1 =

VAR _r1 =

    SELECTEDVALUE ( 'Table'[Lab Result 1] )

VAR _r0 =

    SELECTEDVALUE ( 'Table'[Treshold] )

RETURN

    SWITCH ( TRUE (), _r1 > _r0, _r1, _r1 < _r0, "<" & _r0 )



Measure2 =

VAR _r2 =

    SELECTEDVALUE ( 'Table'[Lab Result 2] )

VAR _r0 =

    SELECTEDVALUE ( 'Table'[Treshold] )

RETURN

    SWITCH ( TRUE (), _r2 > _r0, _r2, _r2 < _r0, "<" & _r0 )



Measure3 =

VAR _r3 =

    SELECTEDVALUE ( 'Table'[Lab Result 2] )

VAR _r0 =

    SELECTEDVALUE ( 'Table'[Treshold] )

RETURN

        SWITCH ( TRUE (), _r3 > _r0, _r3, _r3 < _r0, "<" & _r0 )



Measure4 =

VAR _r4 =

    SELECTEDVALUE ( 'Table'[Lab Result 1] )

VAR _r0 =

    SELECTEDVALUE ( 'Table'[Treshold] )

RETURN

    SWITCH ( TRUE (), _r4 > _r0, _r4, _r4 < _r0, "<" & _r0 )



Measure5 =

VAR _r5 =

    SELECTEDVALUE ( 'Table'[Lab Result 2] )

VAR _r0 =

    SELECTEDVALUE ( 'Table'[Treshold] )

RETURN

    SWITCH ( TRUE (), _r5 > _r0, _r5, _r5 < _r0, "<" & _r0 )



Measure6 =

VAR _r6 =

    SELECTEDVALUE ( 'Table'[Lab Result 2] )

VAR _r0 =

    SELECTEDVALUE ( 'Table'[Treshold] )

RETURN

    SWITCH ( TRUE (), _r6 > _r0, _r6, _r6 < _r0, "<" & _r0 )

Set background:

All six columns of measure should have the same setting.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum