I have this chart in Excel where I plot dates on the X and hours on the Y axis. Each dot is an event. This helps visualize busy times.
I'm trying to create the same chart in Power BI Desktop, but am failing miserably. The Y axis shows a count of times and the X axis shows a count of dates. I have created an Index column and dragged it to the details, the model shows the correct date format, but I still see only one data point in the middle of the chart. No date hierarchy. The only option is to switch to distinct count.
Is it possible to
seems the issue is that Power BI doesn't like using dates and times on the Axes. I tested the following and it worked.
Use an integer to represent the Day 1, 2, 3 etc
Use a decimal number to represent the time 12.5, 14.75 etc
Add a unique ID column (can be A, B, C or what ever)
Set the default aggreagation for the first 2 columns to be SUM and the third to be DO NOT Aggregate
Put the first 2 on your axes, and the third on details.
Not perfect, but it may get you going. I assume R can do this, but this looks non-trivial to get started (doable, but not trivial).
@MattAllington has nailed it. I tried responding last night, but the forum was eating my replies.
Here's a sample workbook implementing that method (I use dates and an explicit measure rather than a numeric date column, but the effect is the same). This is because the axes of a scatter plot seem to only take a measure, and cannot display literal field values.
The Scatter charts are designed for measures on x and y axis that is why when you add a text member is will turn this into a count - this happens in all visuals when you put a dimension member into a values field.
Edit: You can use date on the play axis.
Ive just run into this same problem. Wow. Power BI can't handle dates on scatter plots. In 2016. For info, Tableau handles dates perfectly.
Disclaimer: I don't work for Tableau
I agree it's a big gap, along with not having a Duration data type in DAX (though it exists in Power Query).
Despite their Known Limitations, you can do date-time 'scatterplots' with simple R visuals - e.g. http://stackoverflow.com/questions/7160565/how-to-create-a-time-scatterplot-with-r Though formatting, and converting to POSIXct, can be a bit fiddly.
Here's the code I used to plot Call Times by Weekday for the visual shown below:
library(ggplot2) # Needed for ggplot library(ggthemes) # has a clean theme for ggplot2 library(scales) # Needed for scale_x_datetime #Convert the time format, and account for timezone (so X axis starts at midnight not 08:00!) dataset$`Call Time`<- as.POSIXct(dataset$`Call Time`,format="%Y-%m-%dT%H:%M:%OS", tz="GMT") # Avoid having to reference dataset$ each time in function calls attach(dataset) #Plot the dataset by Week Day (re-ordered from alphabetic) and Call Time ggplot(dataset, aes(x=reorder(`Week Day`,`Week Day Number`), y=`Call Time`)) + #Show points with 90% transparency to cater for overtyping, and ignore NA results in the dataset geom_point(alpha=0.1, size=4, na.rm=TRUE) + #Set the X axis to hourly breaks scale_y_datetime(breaks=date_breaks("2 hours"), labels=date_format("%H:%M")) + #Now some formatting #Apply tidy Tufte theme with no axis ticks theme_tufte(base_family="Arial", ticks=FALSE) + #Clear axis labels and set Title labs(x=NULL, y=NULL, title="Calls by weekday & time of day") + #Rotate X axis theme(axis.text.x = element_text(angle = 90)) + #Format Title theme(plot.title=element_text(hjust=0, color="grey", size=12))
I have a similar issue.I want to plot 2 months dates on x axis and time intervals in a span of 24 hours with 15 min intervals break on Y -aixs.So for each day i have 96 Time Stamps.I just need to plot on X and Y with out any aggregation.This is very imp requirement to us.So can anyone pls help.We tried but no luck,everytime trying to drag this field onto visual I am getting the count of Date and Count of time,Which doesnt make anysense.Appreciate quick response and help.
I have similar problem now.I want simple XY scatter plot with date on x axis and times on Y axis.How can I do this using Power BI.looks like you encounterd similar problem a while back.Appreciate your help.Thanks You.
I got it to work correctly. It is not a huge problem, just that the charting works differently. You can get only numeric values on either axis of a scatter chart, and then split the things you are aggregating in th bubbles using details and legend. The closest you can get is datevalues (or monthvalues, or weeknum) - and you want whatever you use to represent an atomic unit in your dataset (no aggregation). Then it works quite beautifully.