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

Line Chart: Conditional Formatting

Hi All,

 

Im quite new to Power BI. Is there a way to use condtional formatting on a line chart... 

 

I have a line chart with 3 measures, 1 lower limit, 1 upper limit and 1 with the actual measure. 

 

The line has to color red when it exceeds either the lower or upper limits.

 

Thanks for your help fam.

 

Regards,

 

POWERNOOB

1 ACCEPTED SOLUTION
Resident Rockstar
Resident Rockstar

Hi @PowerNoob,

 

Currently, this feature is not supported in Power BI Desktop, you could vote this idea which has been submitted in Power BI ideas forum.

 

For the workaround, I have made a test that you could create two measures to get the result below.

 

sales1 = IF(SUM(Test[Sales])>40,BLANK(),SUM(Test[Sales]))
sales2 = IF(SUM(Test[Sales])>40,SUM(Test[Sales]),BLANK())

 

Capture.PNG

 

The line chart shows red which sales >40 and black which sales<=40.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
Post Patron
Post Patron

Sorry for late

let me join this discussion, I know I'm too late but, got the right solution for future reference.

Please check out this blog. 

https://exceleratorbi.com.au/line-chart-conditional-formatting/

 

ConditionalFormatingTrend.gif

 

 

Download PBIX

 

Thanks

Microsoft
Microsoft

I was unable to get the single line with changing colors that PowerNoob showed in the screen shot following the steps described in the Solved answer. Thought I would share my workaround to make it work.

 

I have a table with date, value, Upper & lower bounds, and is this row an Anomaly.

 

I added two columns looking at the Anomaly for the prior day and for the next day (both are not needed but based on what you pick will determine the color of the line connecting).

gateskt_1-1594935875737.png

 
Earlier checks the value for the day before:
Earlier = CALCULATE(FIRSTNONBLANK(ProPlus[IsAnomaly],1),FILTER(ProPlus,ProPlus[Timestamp] = EARLIER(ProPlus[Timestamp]) - 1))
 
Later is checking the day after:
Later = CALCULATE(FIRSTNONBLANK(ProPlus[IsAnomaly],1),FILTER(ProPlus,ProPlus[Timestamp] = EARLIER(ProPlus[Timestamp]) + 1))
 

 

The line chart has two measures

Black is filtering on all that are currently zero or that are one now but will be zero tomorrow . 

MeasureBlack = If(sum(ProPlus[IsAnomaly]) = 0 || (Sum(ProPlus[IsAnomaly]) = 1 && Sum(ProPlus[Later]) = 0) , Sum(ProPlus[Value]), BLANK())
 
 Red is filtering in all that are currently one or that are zero now but will be one tomorrow 
 
MeasureRed = If(sum(ProPlus[IsAnomaly]) = 1 || (Sum(ProPlus[IsAnomaly]) = 0 && Sum(ProPlus[Later]) = 1) , Sum(ProPlus[Value]), BLANK())
 
when going from black to red days the color connectng will be red. Red days to Black days are is black. If you want the reverse connecting color use Earlier column instead of Later in the two measures.
 

Result below... 

 

 

gateskt_0-1594935759021.png

Hope this helps...

 

Resident Rockstar
Resident Rockstar

Hi @PowerNoob,

 

Currently, this feature is not supported in Power BI Desktop, you could vote this idea which has been submitted in Power BI ideas forum.

 

For the workaround, I have made a test that you could create two measures to get the result below.

 

sales1 = IF(SUM(Test[Sales])>40,BLANK(),SUM(Test[Sales]))
sales2 = IF(SUM(Test[Sales])>40,SUM(Test[Sales]),BLANK())

 

Capture.PNG

 

The line chart shows red which sales >40 and black which sales<=40.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

BI Color.PNG

 

works like a charm. thanks mate!

How to achieve this?  would you kindly to share, please.   thank you

Let me join this thread if I may...

There is a "quick an dirty" way of using conditional format on a Line Chart which is a bit of a hack (a bug maybe), is not documented as far as I'm aware, and is limited in the formatting options available

The method is simple. Start with a bar chart and add your conditional formatting on this chart:

Bar chart.JPG

 

 

Now simply change the visual to a Line Chart by selecting the visual type in the visuals pane:
Line Chart.JPG

 

(I actually came across this by accident while playing around with visuals).

Unfortunately the line colour is unaffected, and you can't play around with the shapes etc... (not really surprising).

However, it is another option in our toolbox.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






KPI.PNG

 

When trying to recreate this code, I cannot get the continous line to go below the specified target, does anyone have a solution?

@PowerNoob 

Kindly help me how to join this line as shown above. I created 2 measures for this, but the lines are not joining.

hi,

 

i am new to Dax and power BI.

 

can share with me how this work? where do you enter the DAX code to make the two line appeared in the visual chart? 

 

how can we create another table to record down all the points that are out of the boundary?

Hi,

 

If you created two measures one with greater than target & lesser than target, how were you able to achieve continous line with Color formating ?

 

Thanks in Advance,

Sai

Thanks Piga, will try it out!

 

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors