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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Conditionnal formatting cells from table

Hi,

 

I am thinking on how to get my cells formatted based on calculations but can't find a solution currently. Don't know whether to use the IF or SWITCH function for it as SWITCH only seem to apply on created measures.

 

The table I have, assume it is called (Table[])

 

Sofien1234_1-1654082265740.png

 

Condittional formatting I want should apply to Delivery Order or Shipment No and colours I want to use are Orange, Green, Red or no colour (Black):

 

If (Table.[IOD Date]) <= (Table.[Arrival Date]) And (Table.[status]) = "IOD" ; Green
If (Table.[Arrival Date]) = Today and (Table.[IOD Date]) = Blank ; Orange
If (Table.[Arrival Date]) < Today And (Table.[Status]) = "Ship" ; Red

If (Table.[IOD Date]) > (Table.[Arrival Date])  And (Table.[Status]) = "IOD" ; Red

 

Else no colour (Black).

Who could help me out? @amitchandak maybe?

 

Thanks in advance!

 

Kind Regards,

 

Sofiën

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @v-luwang-msft ,

 

Solved it by creating a column like below and doing conditionnal formatting based on numbers 1 till 5.

 

_Colour =
IF(AND(G002_KDE_BI_Report[_IODDate] <= G002_KDE_BI_Report[_ArrivalDate], G002_KDE_BI_Report[_Bin] = "IOD received"), 1 ,
IF(AND(G002_KDE_BI_Report[_ArrivalDate] = TODAY(), G002_KDE_BI_Report[_IODDate] = BLANK()), 2,
IF(AND(G002_KDE_BI_Report[_ArrivalDate] < TODAY(), G002_KDE_BI_Report[_Bin] = "Shipped"), 3 ,
IF(AND(G002_KDE_BI_Report[_IODDate] > G002_KDE_BI_Report[_ArrivalDate], G002_KDE_BI_Report[_Bin] = "IOD received"), 4 , 5)
)))
 
Kind Regards,
 
Sofiën

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

Pls refer the below:

 

Rule = SWITCH(MAX('Table'[type]),"A","Green","B","Red","C","Yellow")

 

Then 

vluwangmsft_0-1654594289302.pngvluwangmsft_1-1654594314510.png

Final output:(color according measure rule)

vluwangmsft_2-1654594335057.png

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


Best Regards

Lucien

Anonymous
Not applicable

Hi @v-luwang-msft ,

 

Solved it by creating a column like below and doing conditionnal formatting based on numbers 1 till 5.

 

_Colour =
IF(AND(G002_KDE_BI_Report[_IODDate] <= G002_KDE_BI_Report[_ArrivalDate], G002_KDE_BI_Report[_Bin] = "IOD received"), 1 ,
IF(AND(G002_KDE_BI_Report[_ArrivalDate] = TODAY(), G002_KDE_BI_Report[_IODDate] = BLANK()), 2,
IF(AND(G002_KDE_BI_Report[_ArrivalDate] < TODAY(), G002_KDE_BI_Report[_Bin] = "Shipped"), 3 ,
IF(AND(G002_KDE_BI_Report[_IODDate] > G002_KDE_BI_Report[_ArrivalDate], G002_KDE_BI_Report[_Bin] = "IOD received"), 4 , 5)
)))
 
Kind Regards,
 
Sofiën
AnamPatel
Resolver II
Resolver II

There is an easier way to do Conditional Formatting. You can achieve this without a measure as well. Below are the steps –

Click on the Down Arrow in front of the Column Name under Values Tab and Select Conditional Formatting > Background Color

AnamPatel_0-1654089922105.png

 

 

A new Background Color window will pop up – fill all the details, select the colors you want and click on OK.

 

 

AnamPatel_1-1654089924687.png

 

 

That’s it, the colors will show up on your chart –

AnamPatel_2-1654089920942.png

 

 

Let me know this works for you.

 

If I answered your question, mark my response as a Solution. Appreciate your KUDOS! (To Like, or Kudo a post, click the yellow thumbs up box in the bottom right corner.)
Anonymous
Not applicable

Hi @AnamPatel 

 

I know you can do it like this as well, but this won't work for me as my colours are based on calculations which I mentioned above. In the screen you shared you need to select Field Value after selected Conditionnal Formatting and then within Field Value enter the measure with SWITCH function probably.

 

Kind Regards,

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors