Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi Anonymous,

     

    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())

     

     

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

     

    Best Regards,

    Cherry

15 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi Anonymous,

     

    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())

     

     

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

     

    Best Regards,

    Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

       

      works like a charm. thanks mate!

      • SDheeraj's avatar
        SDheeraj
        Frequent Visitor
        8 years ago

        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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks Piga, will try it out!

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      For getting this to work you need the x axis to be cathegorical.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Interesting solution !
      Do you think that this can be adapted to change the line color point by point ? (instead of the entire line ?)

       

  • gateskt's avatar
    gateskt
    Microsoft Employee
    6 years ago

    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).

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

     

     

    Hope this helps...