Complex colouring rows in a table

[LABrowne]

Hi there,

I have a bit of a complicated formula that I need help building.

I have a table on Power BI where I need to colour in rows based on (1) Cancelled orders (fill in cell colour red) and (2) colour cells yellow based on three criteria: >= 100% order saturation, 2 or more products ordered and the order has to be at least 3 & 11 months since the order date. To add another layer of complexity I then have to give priority in colouring cancelled orders over (2). For example if an order matches (2) but it is cancelled then that row in the table must be red and not yellow if that makes sense.

So far I am able to do (1) on its own using the below formula: 

Background Cancellation Colours = IF ( CONTAINSSTRING( MAX (Order[OrderNumber]), "Cancelled") , "Red", BLANK())

This works and I have to apply this formula to every column in the table currently. I need to create a formula for (2) and give priority to (1) over (2) as explained above. I'm guessing for (2) I need to create multiple ifs but not sure?

Please let me know if you need me to expand/explain further.

Kind regards,

Luke

[VBLOT · Accepted solution]

Hello @LABrowne,

You have multiple ways of doing so. You can do everything inside a single measure but you can also split in 3.

First is your red line measure, second is your yellow one and the third is your main measure that you will use inside conditionnal formatting using SWITCH() function or IF statement.

I suggest to test them separately at first and then (if you want to not have 3 separate measure) mix the logic inside a big one with VAR RedLine = ... , VAR YellowLine = ... and RETURN IF(condition = X && condition <> Y, RedLine, YellowLine)

I hope it's clearer.

Vincent BLOT
Did I answer your question? Mark my post as a solution!

[LABrowne]

Thank you, that's what I'm doing step by step. the only part I'm stuck on is I have 2 different date columns - Delivery Date and Expiry Date how do I make a condition that the order has to active e.g. between those 2 dates?

[VBLOT]

Hello,

I'm not sure to really understand, can you share the measure maybe you are working on ?

From what I understand, it is another condition that you have, IF ( Date >= Delivery Date && Date <= Expiry Date, do something, do another thing).

Hope it helps

[LABrowne]

Yes so I guess it would be measure = IF ( TODAY() >= Order[DeliveryDate] && TODAY() <= Order[ExpiryDate] ).

However whenever I try any date formulas it doesnt recommend by two date columns (delivery and expiry date) Im not sure why.