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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
dtipton
Frequent Visitor

Dynamic date vertical reference line for scatter chart

Hi, I need to add a dynamic, vertical reference line to my scatter chart. My scatter chart has a date (in whole number format) x-axis and a numeric y-axis. Looks like this unfiltered:
scatter chart.png

I need to give my boss a scatter chart that has a vertical reference line that indicates the last date an event occured, this date is in the dataset. It should look something like this: (I overlayed two visuals to illustrate.) 


scatter chart2.png

I can't overlay two visuals as the final solution because if someone uses the slicers on my page the scatter chart axis will change and the line will not adjust properly, I've tried.

Any help is greatly appreciated! 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

That's a very difficult issue @dtipton . Is your line fixed at a specific date or does it vary based upon slicer selection? I'm not sure it matters but want to ask. Bubble charts do not have Analytics enabled so can't go down that route. Only thing I could think of would be to manufacture points for your bubble chart for many values of Y for the same X and then UNION that onto your table or whatever you are using for your bubble chart but that is a long shot and would require specific circumstances. 

 

Sample data and such could help determine if this is possible. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

That's a very difficult issue @dtipton . Is your line fixed at a specific date or does it vary based upon slicer selection? I'm not sure it matters but want to ask. Bubble charts do not have Analytics enabled so can't go down that route. Only thing I could think of would be to manufacture points for your bubble chart for many values of Y for the same X and then UNION that onto your table or whatever you are using for your bubble chart but that is a long shot and would require specific circumstances. 

 

Sample data and such could help determine if this is possible. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler , thanks so much for your reply! The line is fixed at a specific date. 

I will try your proposed solution and see what happens. I had a similar thought when I created a line in a seperate scatter chart using dummy values. I'll be sure to update this thread with the results and sample data if needed. 

The union suggestion works! I have some complex filter logic applied to this report and the line still appears.  For anyone who has a similar need, here is what I did. 
1. Create a seperate table for generating values for your line. Here is the code I used: 

Line Table =
var tab1 = GENERATESERIES(1,MAX([y-axis variable]),1)
var tab2 = ADDCOLUMNS(tab1,"Reference Date" , [lookup logic for your needed value])
Return
tab2

2. Union Line Table and your main dataset table
      The additional columns your Line Table doesn't share will be Blank(), except if you are using slicers. If you don't need slicers you can stop here. 

3. Slicers logic
     If you want the ability to filter your data and not lose the line, create another table with two columns. The first column contains all true slicer values repeated twice and the second contains true slicer values plus a dummy value (in the example below the dummy value is "Line"). The slicer you add will only show the true values from the first column while still "associating" your line as part of the dataset. 

I found this dataset online. I'll use it as an example: 
sample data for post1.png

Your scatter chart uses this sales data, and you want a line to appear without being impacted by a SalesPerson filter. Create a seperate table like shown below.
sample data for post2.png

Create a relationship between Column 2 and SalesPerson from the main table. Then add a slicer to your report using Column 1.

All Done!


Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors