Compare a measure with column values and conditional format

[Anonymous]

Hi all,

Below is a view that I bring in to a 'table view' in PBI. 'Current_Value' (Table A) is a measure and 'HI HI(red)' (Table B) is a columnar value. I want a logic that compares If Current_Value > HI HI(red), then Current_Value should be color coded to red, else green.

I am guessing I have to use the summarize function or values, but can't seem to get the syntax right. Any thoughts?

[TomMartens]

Hey,

can you please prepare a pbix wiht some sample data, upload the pbix to onedrive or dropbox and share the link.

But in the meantime this might also help: https://community.powerbi.com/t5/Desktop/Using-IF-Statement-for-Date-to-Separate-Dollar-Amounts-in-o...

Regards,

Tom

[Anonymous]

Hi Tom,

ConditionalTest.pbix

Please find the link attached.

Just to show what I am trying, here is a DAX that is giving me syntax error:

Conditional_Value = If(HASONEVALUE('Table A'[NAME]),

SWITCH(VALUES('Table A'[NAME]),

[Current_Value]>SUM('Table B'[HI HI (red)]), "#FF0000",

[Current_Value]>SUM('Table B'[HI HI (red)]),"#008000"

)

)

[TomMartens · Accepted solution]

Hey,

from my understanding you should rewrite the measure like so:

Conditional_Value = If(HASONEVALUE('Table A'[NAME]),
IF(
    [Current_Value]>SUM('Table B'[HI HI (red)])
    , "#FF0000"
    , "#008000"
)
)

Regards,

Tom

[Anonymous]

OMG.. thank you so much @TomMartens. I was trying to develop a flag so I could use the Cell elements, conditional formatting and it worked!!! However, I changed the color hex # w/text  "1 & 2 ",  I have been searching for weeks...  
Can this be a nested for a # 3 if I have blank cells I would like no icon or color???

RATE_WP = IF(HASONEVALUE('table'[TRGT]),

IF(

    [measure]>SUM('table'[TRGT])

    ,"1"

    ,"2"

)

)

And this resolved my issues, I cannot thank you enough...

Nanc

[Anonymous]

My god! That was literally my first attempt, but I was formatting it incorrectly!! Thank you so much!!

[TomMartens]