How to use make date the x-axis on a scatter chart without converting it to a number

[EC22]

Hello Power BI Community, 

I am currently working on a project that needs to have the date on the x-axis as some other measurement on the y-axis. There will be multiple points for each date which is why I chose to use a scatter chart, but the limitation on this is that both axes need to be numeric. To bypass this, I just switched the Date type in the power query editor to fixed decimal number. As a result, all my dates are like 40213. It's functional, but I was wondering if there is a way to get pass this, because it doesn't look very pleasing to see a bunch of numbers that I don't know the real value of. 

Thanks in advance!

[Pragati11]

Hi @EC22 ,

Not sure why you chose Scatter chart to visualise your data when you want DATE on x-axis.

I would suggest you use other charts like Line chart, clustered column chart, bar chart, etc.

Scatter chart only allows you to plot 2 numeric values against eachother. Something shown below:

Hope the explanation helps.

[EC22]

Hi @Pragati11 

The reason why I am using a scatter chart is because there will be multiple values per date, and I don't want to aggregate them together using mean, median, max, etc. From what I understand about a line plot, it can only plot one y-value for each x value. So that's why I'm using a scatter chart. The main challenge is that although I can do something like that with a scatter chart, it requires me to make date numeric, which is fine for the functionality, but it doesn't look good on the visualization. 

[Pragati11]

Hi @EC22 ,

I am really not sure what visual you get putting Date on x-axis of a scatter chart.

Do share some details there like screenshots, etc.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Are you doing something like this?

Add more details here.

[EC22]

@Pragati11 Hello, here's just a quick set I made. If I kept date as the Date type, it wouldn't allow me to plot both points for each date. So then I had to convert it to a number. 

[Pragati11]

Hi @EC22 ,

Please send me a complete screenshot like my screenshot in previous reponse , for your scatter plot chart - which shows Fields section for the visualisation as well.

I am able to move a date datatype DATE column on scatter chart as shown in my previous response.

I am attaching the screenshot again here:

[EC22]

Hello @Pragati11 , 

Thank you for your response. I attached another screenshot:

[Pragati11]

HI @EC22 ,

From you screenshot, I can clearly see your DATE column is not of date datatype:

When a column is of date datatype, you have a calendar iocn against it, something like below:

Can you click on your column in Power BI and see on top ribbon it's datatype please?

It should be date, but it looks like TEXT to me in your case.

To check datatype --> click on your column --> on top ribbon:

[EC22]

@Pragati11 

I noticed that the calendar icon was not next to DATE also. But then when I checked the ribbon, it says that it is: 

[Pragati11]

Hi @EC22 ,

This doesn't seem to be right.

Is it possible to share your pbix file with me?

You can share a dropbox link or via WeTransfer.

[EC22]

@Pragati11 This is my first time using a Dropbox link so hopefully it works. Let me know if you need me to send it again. https://www.dropbox.com/s/pskggw8gie4s3fa/Date_x_axis.pbix?dl=0

[Pragati11]

HI @EC22 ,

I reloaded your data by using the sample that you provided in your earlier response.

In power query editor I see the right format:

Then I realised something was wrong with your settings. I enabled tthe following option on your pbix file:

And now I see the calendar icon against date column in your data:

Scatter chart also works now:

So, the issue was because of the settings option that was disabled on your pbix file for Auto date/time.

Once this is enabled everything looks good.

[EC22]

@Pragati11 . I see, it's super close. I think the thing is that I want both points for each date to show up. Currently, I believe it is the value of the y-axis is being summarized. I tried to use don't summarize, but then the same error popped up. Is it able to work on your end?

[Pragati11]

Hi @EC22 ,

You can't move non-summarised values in the scatter chart to the y-axis section.

Try some other chart in that case.

[EC22]

Okay, I'll try to look around. Thank you for all your help. 

[Pragati11]

Hi @EC22 ,

Can you modify the datatype of your date column to DATE please?

Because I am able to use my date column as x-axis in the scatter chart.

If it still doesn't work for you, share some sample data as it is working at my end.

[EC22]

@Pragati11 

This is the error message I get once I switch it back to DATE. 

Here is an example of the kind of data I would like to plot. I would like there to be two points for each date, with Date on the x-axis and Strength on the y-axis. 

DATE	STRENGTH
6/15/2018	200
6/15/2018	173
7/20/2019	124
7/20/2019	132
12/19/2019	159
12/19/2019	204

[Pragati11]

Hi @EC22 ,

Are you selecting the following option for the date field?

[EC22]

@Pragati11 

I don't think I have an option to do that on the visualizations tab. But I did go into Power Query Editor to change it to the Date data type.