conditional formatting by field

[Anonymous]

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!

[Anonymous]

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

[Anonymous · Accepted solution]

@Anonymous 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)

[Anonymous]

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?

[Anonymous]

@Anonymous I believe so.