Forum Discussion
Plotting multiple dates per item in a scatter plot
The idea is that I would like to have a timeline or start/end dates on one axis and items on another, where each item has multiple dates to plot (i.e. initial release date, actual release date, release date in X country, etc.). I've tried using both Power Bi Scatterplot and Dot Plot by MAQ Software and I've only been able to plot one date per item. Please let me know if there is a way to do this or if there are any tricks I can use to simulate or get close to this solution.
Thank you in advance for your help.
It seems like the best way to handle this is going to be to unpivot your data. Transforming it from 1 row per project to multiple rows per project will allow you to plot this the way you want. I went from this:
Project Initial Release Actual Release Release in US Release in EU ABC 1/1/2019 2/1/2019 3/1/2019 4/1/2019 DEF 1/1/2019 1/1/2019 1/15/2019 3/15/2019 GHI 1/31/2019 3/2/2019 2/2/2019 To this:
Project Release Type Date ABC Initial Release 1/1/2019 ABC Actual Release 2/1/2019 ABC Release in US 3/1/2019 ABC Release in EU 4/1/2019 DEF Initial Release 1/1/2019 DEF Actual Release 1/1/2019 DEF Release in US 1/15/2019 DEF Release in EU 3/15/2019 GHI Initial Release 1/31/2019 GHI Release in US 3/2/2019 GHI Release in EU 2/2/2019 This puts all dates in the same column, while still associating them with a release type and project.
I ended up using the Dot Plot by MAQ Software, since it gives slightly more control over the fields and allows you to jitter the plot points, so that you don't accidentally hide data. The first thing I did was create a numerical project ID, since all the scatter plots don't like categorical Y axes.
ProjectID = RANKX(ALLSELECTED('Project Releases (2)'), 'Project Releases (2)'[Project], SELECTEDVALUE('Project Releases (2)'[Project]), ASC, Dense)The actual ID doesn't matter, just the fact that each project gets a unique numerical number.
From there, I played around with the various options until I got a scatter plot that looks vaguely like the one you described:
Honestly, at the end of the day, this is not a particularly elegant solution. The dates are categorical, you can't color by Project name, there's no easy way to see what the project name is without hovering over a point, among others. The current available scatter plot options just do not play well with data that isn't straight numerical info with a single category.
You're more likely to be able to find success by using one of the calendar or timeline visualizations out there instead of trying to force a scatterplot to do this work for you. Good luck
A mapping table would likely work.
As far as actually using categorical data as an axis, there might be some way with the MAQ dot plot to swap the axes, but it still doesn't treat dates as continuous as far as I can tell.
The other option is to develop your own dot plot visual, but that may be a little bit too much effort for this.
6 Replies
Anonymous you will need to set up a date table and then create relationships to this date table for each of your dates, one will be the primary relationship and you can create that measure easily, but then you have to use your inactive relationship to create the rest, these will be denoted in your relationships by a dotted line. For those measures you will need to create a use relationship measure.
ie.
primary relationship
CALCULATE ( sum(measuredvalue));
inactive relationships;
CALCULATE ( sum(measuredvalue); USERELATIONSHIP ( inactivedatename; 'Date'[Date] ) )more information here
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
and feel free to ask me questions.
vanessafvg Thank you for your help! Unfortunately, I'm looking to plot multiple dates per item, not perform any calculations with a measure column. Here is an example of what I'm trying to simulate:Would I still use something similar to your previous solution?
It seems like the best way to handle this is going to be to unpivot your data. Transforming it from 1 row per project to multiple rows per project will allow you to plot this the way you want. I went from this:
Project Initial Release Actual Release Release in US Release in EU ABC 1/1/2019 2/1/2019 3/1/2019 4/1/2019 DEF 1/1/2019 1/1/2019 1/15/2019 3/15/2019 GHI 1/31/2019 3/2/2019 2/2/2019 To this:
Project Release Type Date ABC Initial Release 1/1/2019 ABC Actual Release 2/1/2019 ABC Release in US 3/1/2019 ABC Release in EU 4/1/2019 DEF Initial Release 1/1/2019 DEF Actual Release 1/1/2019 DEF Release in US 1/15/2019 DEF Release in EU 3/15/2019 GHI Initial Release 1/31/2019 GHI Release in US 3/2/2019 GHI Release in EU 2/2/2019 This puts all dates in the same column, while still associating them with a release type and project.
I ended up using the Dot Plot by MAQ Software, since it gives slightly more control over the fields and allows you to jitter the plot points, so that you don't accidentally hide data. The first thing I did was create a numerical project ID, since all the scatter plots don't like categorical Y axes.
ProjectID = RANKX(ALLSELECTED('Project Releases (2)'), 'Project Releases (2)'[Project], SELECTEDVALUE('Project Releases (2)'[Project]), ASC, Dense)The actual ID doesn't matter, just the fact that each project gets a unique numerical number.
From there, I played around with the various options until I got a scatter plot that looks vaguely like the one you described:
Honestly, at the end of the day, this is not a particularly elegant solution. The dates are categorical, you can't color by Project name, there's no easy way to see what the project name is without hovering over a point, among others. The current available scatter plot options just do not play well with data that isn't straight numerical info with a single category.
You're more likely to be able to find success by using one of the calendar or timeline visualizations out there instead of trying to force a scatterplot to do this work for you. Good luck
