cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JLippens Regular Visitor
Regular Visitor

conditional formatting by field

Hello!  I am relatively new to DAX and need some assistance on an issue....

 

I have a matrix I want to turn the entire column either red or green depending on the following parameters:

If the time is between 4:00 am and 7:00 am - and the total number of items in quantity are 140 or over it should be green

If the time is between 4:00 am and 7:00 am - and the total number of items in quantilty are 139 or below it should be red

Now the kicker - I have three types of items that are connected to the quantity field and are shown in the matrix  - DF, DR and FR and these items are parsed between each user - all will have a quantity, but I want the red/green to show up on the total of the type by user for each hour.   I am thinking I need a DAX measure to get this done, but not sure where to start.

 

This would work if it were only one parameter:

 

ColorKPI =
SWITCH(
TRUE()
,SUM(Journal[Quantity]) <= 139, "#ff0000"
,SUM(Journal[Quantity]) >= 140, "#00ff00"
, "#FFFFFF")

 

 

Thanks in adavance for any help possible!

 

1 ACCEPTED SOLUTION

Accepted Solutions
mnayar Established Member
Established Member

Re: conditional formatting by field

@JLippens I am not sure if I got your first message, but I am providing a solution for your second message.  If you need more help please post a power bi file so its a little easier to understand.

 

Try writing a switch. I am just writing a bit of the code, I am sure you can add the required values.

 

VAR TimeHour = HOUR(DATETIME Field you are using to extract time by hour text)
RETURN SWITCH(TRUE(),
              TimeHour IN {4,5,6,7,8} && Sum(Journal[Quantity]) <= 139, 1,
              TimeHour  = 9 && Sum(Journal[Quantity]) <= 104, 1
              TimeHour IN {10,11} && Sum(Journal[Quantity]) <= 139, 1,,
              2)

 

View solution in original post

4 REPLIES 4
JLippens Regular Visitor
Regular Visitor

Re: conditional formatting by field

After trying some version of the following:  https://community.powerbi.com/t5/Desktop/Conditional-formatting-based-on-multiple-conditions/m-p/464...

 

I have been able to get part of what I need!   Great stuff rajendran - Thanks so much for the insight!

 

The code is a nested if and I am sure there is probably a better more efficient way to do this but here's my code if any one wants to chime in!

 

Condition =
MAXX(Journal,
IF(Journal[TimebyHourText] = "4:00 AM" && Sum(Journal[Quantity])<=139,1,
IF(Journal[TimebyHourText] = "5:00 AM" && Sum(Journal[Quantity])<=139,1,
IF(Journal[TimebyHourText] = "6:00 AM" && Sum(Journal[Quantity])<=139,1,
IF(Journal[TimebyHourText] = "7:00 AM" && Sum(Journal[Quantity])<=139,1,
IF(Journal[TimebyHourText] = "8:00 AM" && Sum(Journal[Quantity])<=139,1,
IF(Journal[TimebyHourText] = "9:00 AM" && Sum(Journal[Quantity])<=104,1,
IF(Journal[TimebyHourText] = "10:00 AM" && Sum(Journal[Quantity])<=139,1,
IF(Journal[TimebyHourText] = "11:00 AM" && Sum(Journal[Quantity])<=139,1,
IF(Journal[TimebyHourText] = "12:00 PM" && Sum(Journal[Quantity])<=69,1,
IF(Journal[TimebyHourText] = "1:00 PM" && Sum(Journal[Quantity])<=139,1,
IF(Journal[TimebyHourText] = "2:00 PM" && Sum(Journal[Quantity])<=104,1,
IF(Journal[TimebyHourText] = "3:00 PM" && Sum(Journal[Quantity])<=139,1,
IF(Journal[TimebyHourText] = "4:00 PM" && Sum(Journal[Quantity])<=139,1,
2))))))))))))))

 

Thanks!!!

 

mnayar Established Member
Established Member

Re: conditional formatting by field

@JLippens I am not sure if I got your first message, but I am providing a solution for your second message.  If you need more help please post a power bi file so its a little easier to understand.

 

Try writing a switch. I am just writing a bit of the code, I am sure you can add the required values.

 

VAR TimeHour = HOUR(DATETIME Field you are using to extract time by hour text)
RETURN SWITCH(TRUE(),
              TimeHour IN {4,5,6,7,8} && Sum(Journal[Quantity]) <= 139, 1,
              TimeHour  = 9 && Sum(Journal[Quantity]) <= 104, 1
              TimeHour IN {10,11} && Sum(Journal[Quantity]) <= 139, 1,,
              2)

 

View solution in original post

JLippens Regular Visitor
Regular Visitor

Re: conditional formatting by field

Thanks so much for the quick reply.   I have been traveling and unable to try this yet....   However, still having an issue with the data being split out in the matrix as a "type".   Should this same logic be applied in that case when the user drills down into this layer?

mnayar Established Member
Established Member

Re: conditional formatting by field

@JLippens I believe so.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 462 members 4,207 guests
Please welcome our newest community members: