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/

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    AmazingRandom
    Helper II
    2 years ago

    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 <"