Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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: I need...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    That's a very difficult issue Anonymous . 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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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: 


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.


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

All Done!