Conditional Formatting

[MatthewMcD]

Brand new and on the steep learning curve for PowerBI...

I see a lot of people asking for Conditional Formatting. I would like to do something akin to "info graphics" in Excel, where the values "High, Med, and Low" are replaced with red, yellow, green icons.

Then I found this article: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-conditional-table-formatting/

Can anyone explain how to get this to work? The visuals and menu in my PBI Desktop do not exist. It is VERY frustrating to have an article out there that does not match the UI I see in the product. Are there different versions?

[parry2k]

Hey @MatthewMcD

Assuming you have two fields on your table, and make sure you have a column which is numeric and numeric field is not set to "Don't summarize" as shown in the article, once you have aggrgated method (like sum, avg, min or max) applied to you numeric field, you will see "conditional formatting" option. I hope it is helpful. Also assuming you have most updated copy of PowerBI Desktop.

Thanks,

P{

[MatthewMcD]

...and you can't apply formatting to numeric fields unless they are summarized? Looks like it's back to Excel.

[parry2k]

You are absolutely correct.

[MatthewMcD]

I am running version 2.43.4647.541 64-bit (February 2017)

So, conditional formatting only works on numeric fields? Should I convert my text fields to numbers like 1,2,3 and try to format those? This seems so basic, I am struggling to understand why it's so convoluted?

[parry2k]

Yes it works only for numeric field, no text. Add ideas if it doesn't exist.

[MatthewMcD]

?? "Add ideas if it doesn't exist." What do you mean?

[parry2k]

Sorry I meant to go to ideas forum and look if someone already requested this feature, if not then add this an idea:

https://ideas.powerbi.com/forums/265200-power-bi?WT.mc_id=Community

[MatthewMcD]

Thanks, yes several thousand votes for 100s of different flavors of conditional formatting. No need to add mine to the mix. Honestly I am stunned this does not exist. This is Microsoft's 8 or 9th go at a BI product. I assumed this would be at the top of the list, dead simple. Instead I just wasted two days and am headed back to Excel to get the visual in a basic table.

[parry2k]

yes there are tons of ideas on conditional formatting, it will be good to vote and I'm sure all these features will come sooner than later. I'm following/working with this product since its avaialbility, it came long way, but at the same time, lot more required.

I hope there are other features you can use available under this product 🙂

Cheers,

P

[MatthewMcD]

Thanks, though right now I just need "Display the latest Test Name and Result, if the result is failed show a red dot or X or something to draw attention to it." I am updating the report once a day with 28 test results. I am only showing 28 values. Think of it as a KPI report without target values.

[parry2k]

I think it will be achiveable if you can share sample data.

Thanks,

P

[MatthewMcD]

Name,Level

Test1,Warning

Test2,Failed

Test3,Info

Test4,Info

Test5,Failed

...

[parry2k · Accepted solution]

Ok so it is possible, here are the steps:

First upload/host these icons (images) somewhere where these can be accessed publically, I'm calling these 3 files as below

warning.png

failed.png

passed.png

unknown.png

You can give your own name to these file and change formula accordingly.

1. Add new column to you dataset, may be call it "ImageURL" add this formula

ImageURL = 
CONCATENATE("http://mywebsite/path/",
if(myTable[Level] = "Warning", "warning.png"
if(myTable[Level] = "Failed", "failed.png"
if(myTable[Level] = "Info", "passed.png", "unknown.png"
))))

2, On Data modelling tab, set Data Category for ImageURL to Image URL (as shown below)

3. Now add table visual on your canvas and add following fields from your table:

Name

Level

ImageURL

and you will see icons based on the images.

THanks,

Parv

[cuiping]

LOOKS LIKE A NEW WAY TO DESIGN BEYOND THE EXISTING CONDITIONAL FORMATTING, i will mark this article first and try it out later, thank you.

[DolEgon22]

Cool solution, but I can't get formula to work--syntax issues. 

[MatthewMcD]

Wow, you get the award for best Forum support ever! I'll try these steps shortly.

[parry2k]

My pleasure! Thanks for your kind words but best award is when community help each other and issue get resolved 🙂

Cheers,

P

[smithmc3]

I am working on something similar.  We are currently showing green if they have the training and it is current and yellow if they don't have it/its expired.  They want to show Green if they have completed training with date, yellow if the training is about to expire (with date) and red if it has expired (with date) or never occured.  Below is an example of current and requested.  Any body have an idea on how this would work as you can't do conditional formatting with text.

[parry2k]

You can use similar approach what is used int his thread, instead of icons, you can create icon of color boxes and use that as an image.

[smithmc3]

parry2k, any thoughts on how to overlay the date on top of the image?