IF function with conditional formatting

[AmazingRandom]

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 <1800 while still keeping empty blank cells blank.

From my understanding I need to use a IF funtcion to get that to work? Or would I need to also mention the empty cells? And how would that affect the IF function? As the data would be refreshed. Below is a table of how it should look like. How would the highlighting work as well? I also attached a screenshot of how the highlight should be like. 

Treshold	Lab Result 1	Lab Result 2	Lab Result 3	Lab Result 4	Lab Result 5	Lab Result 6
1800	<1800	29000	<1800	14000	<1800	<1800
360	<360	<360	<360	2000	<360	<360
180	52000	7900	4600	<180	<180	<180
900	35000	120000	74000	1100	<900	<900
-	-	-	-	-	-	-
360	3300	600	1700	620	<360	<360

[Anonymous]

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

[AmazingRandom]

Hi! Thanks for your help, it seems to work but for some reason I'm getting all values formatted with the background colour, even with the condition being "does not contain <"

[SamInogic · Accepted solution]

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 crm@inogic.com

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

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

[AmazingRandom]

Hi! Thanks for your help! One question in case the client, if they were to prefer blank values over the "-" how would I edit the formula? I tried doing that but to no success

[SamInogic]

Hi @AmazingRandom ,

For null values you can simply update expression as below,

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

Thanks!

Inogic Professional Services Division

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

Drop an email at crm@inogic.com

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

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

[123abc]

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.