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
naitiksoni
Regular Visitor

Reference Line on x-axis

I have date on my x-axis of a Line chart.

I need a vertical reference line on a particular (dynamic) date to see if my trend line crossed that date or not.

 

Thanks.

2 ACCEPTED SOLUTIONS
BhaveshPatel
Community Champion
Community Champion

Hi @naitiksoni

 

To draw the Vertical reference line is currently not possible. You can use bit of trick though. Use Shapes to create a static vertical line and it is not dynamic as explained. See the attached screenshot.

 

shapes.PNG

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

v-yulgu-msft
Employee
Employee

Hi @naitiksoni,

 

A vertical reference line is not available now. To work around this requirement, you can try to use a combination chart.

  1. Create a "line and stacked column chart"
  2. Put column [date] into shared axis and put [values] into line values.
  3. Create a measure that evaluates to the maximum value when Date=specific date(otherwise blank), put this measure into column values.

Measures can be:

Reference line =
    IF (
        HASONEVALUE ( Table1[Date] ),
        IF (
            VALUES ( Table1[Date] ) = DATE ( 2016, 3, 1 ),
            MAXX ( ALL ( Table1[Date] ), MAX ( Table1[Sales] ) )
        )
    )

3.PNG

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
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

16 REPLIES 16
sundayDriver
Employee
Employee

v-yulgu-msft
Employee
Employee

Hi @naitiksoni,

 

A vertical reference line is not available now. To work around this requirement, you can try to use a combination chart.

  1. Create a "line and stacked column chart"
  2. Put column [date] into shared axis and put [values] into line values.
  3. Create a measure that evaluates to the maximum value when Date=specific date(otherwise blank), put this measure into column values.

Measures can be:

Reference line =
    IF (
        HASONEVALUE ( Table1[Date] ),
        IF (
            VALUES ( Table1[Date] ) = DATE ( 2016, 3, 1 ),
            MAXX ( ALL ( Table1[Date] ), MAX ( Table1[Sales] ) )
        )
    )

3.PNG

 

Thanks,
Yuliana Gu

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

Is there a way to get multiple vertical reference lines?

 

I tried this,

 

Reference line =
IF (
HASONEVALUE( Query1[datetime] ),
IF (
VALUES ( Query1[datetime] ) = DATESBETWEEN(Query2[DATE], DATE(2018,1,1), DATE(2018,12,31)),
MAXX ( ALL ( Query1[datetime] ), MAX ( Query1[param1] ) )
)
)

I get further error,

 

Error Message:

MdxScript(Model) (7, 43) Calculation error in measure 'Query1'[Reference line]: A table of multiple values was supplied where a single value was expected.

 

Anonymous
Not applicable

curious to see if you ever got this working.

 

I have a list of events that I'd like to reference as points or bars in my graphs.  Keep thinking that this original formula for static ref points 

Reference line = 
    IF (
        HASONEVALUE ( 'Date'[Date] ),
        IF (
            VALUES ( 'Date'[Date] ) = DATE ( 2019, 03, 03),
            MAXX ( ALL ( 'Date'[Date] ), MAX ( WeeklyAnalysis[Actuals_CY] ) )
        )
    )
should work.  I'd like to replace that date with a connection to the Events[startdate] column somehow, but trying with Max or Min on that doesn't seem to like it.

Hi @Anonymous 

 

No, Unfortunately I didnt manage to get it to work

 

Matt

I have a project with Production deployed to date, for this project, there are Incidents associated with it and for each incident, there is created date. My requirement is, I have to plot count of incidents based on their create date on X-Axis. But the challenge here is I have to include a vertical line on X-Axis based on Production deployed to date so that user will quickly identify when the project is deployed on production and he can quickly check how many incidents are raised before one month and after one month. Please help me know how can I achieve this.

requirement.PNG

Not sure if the issue is still open or not, but I recently had to tackle a similar issue. I saw that there was a way to use a combo chart to create the reference line with a measure, but you can't split your line chart into multiple categories with the combo chart, so that didn't meet my needs.

 

The best way I found to have a dynamic reference line was to overlap multiple visuals on top of each other.

  1. Create your line chart like normal with no reference line
  2. Create a measure with the basic equation below (assuming you're plotting by date):
    1. if(datevalue(max(<date on your x-axis>)) = Date(<event date>),1,0)
       
    2. My report required the user to input a desired event date. So I used parameters for Day, Month, and Year:
      <event date> above = Date('Year'[Year Value],'Month'[Month Value],'Day'[Day Value])
  3. Copy and paste your line chart, convert it to a column chart, and put your new measure into the "column values" pill
  4. Move your column chart directly behind your line chart, and make everything on the chart except for your column color white (axis, title, etc.)
     
  5. Now you should have a dynamic reference line! The only problem is that the axes of the two charts are not aligned
  6. To get around the axes being unsynced, I turned off the y-axis completely of both charts, so that differences in their scale would no longer change the aspect ratio of the chart
  7. I then created a 3RD CHART that is yet another copy of the line chart. Here, I made everything white except for the y axis, including the data colors. That way we now have a y-axis that follows our filters, but does not affect the aspect ratio of the charts that is actually show our data and the reference line

The only downfall of this method is that, if you have new categories come into your data later, you will have to go back into your 3rd chart and make those new categories white.

 

Hope this helps!

Thank you for providing this helpful solution to a similar problem I've had!

I implelemented it and it works as expected. Your screen shot, however, shows a very thin line (which I like) while mine is quite thick. I can't seem to find any formatting option that controls the width of the column(s).

 

Capture.PNG

Am I missing something? Can you please point me to this option?

How did you get the vertical line to show on a multi-line chart?

I've seen this example on a combination chart but it only appears to work for multi-bar, single line combination and not the multi-line chart.

Hi

 

I know this is an old but did you manage to fiure our how to make the column thinner? Ive tried the padding but it doesnt change it enough for what I would like

 

Thanks

 

Matt

@pschommer, have you figured out how to make that separation column thinner?

@Sha_Kri if you change your X-Axis - Type to "Categorical" you can set the Inner padding to the maximum, which makes the columns thinner.

Only option I have found to far.

My company has moved on from Power BI to Tableau. I don't recall if I figured out how to make the line thinner. It is of little concern to me right now. Best of luck to you.

@v-yulgu-msft Thank you!

 

 

It's lovely. Thanks Yuliana
Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
BhaveshPatel
Community Champion
Community Champion

Hi @naitiksoni

 

To draw the Vertical reference line is currently not possible. You can use bit of trick though. Use Shapes to create a static vertical line and it is not dynamic as explained. See the attached screenshot.

 

shapes.PNG

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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