topic Re: Dynamic date vertical reference line for scatter chart in DAX Commands and Tips

Dynamic date vertical reference line for scatter chart

Anonymous — Mon, 23 Mar 2020 02:28:28 GMT

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

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!

Re: Dynamic date vertical reference line for scatter chart

Greg_Deckler — Mon, 23 Mar 2020 02:34:59 GMT

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

Re: Dynamic date vertical reference line for scatter chart

Anonymous — Mon, 23 Mar 2020 02:44:20 GMT

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.

Re: Dynamic date vertical reference line for scatter chart

Anonymous — Mon, 23 Mar 2020 07:00:27 GMT

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!