Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Compare a measure with column values and conditional format

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_ValueHI 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?

 


image.png

1 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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

6 REPLIES 6
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

 

nancmadi_0-1679098481023.png

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

Nanc

Anonymous
Not applicable

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

Smiley Wink



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.