Date hierarchy not working at levels below year

[BeraG]

Hi!

I have a very simple dataset collecting time results for swimming competitions and I would like to show these results in a scatter plot divided by months on the x axis. I have converted the times from the competition results into decimal values, so as to handle tenths and hundredths of seconds. Every result has a corresponding date column for when the competition took place, with additional columns for year only and month only.

I have created a date table connected to my main table with the results.

However, when I create the scatter plot with time results on the y axis and date on the x axis, it appears to work only when years are shown (and all the results line up vertically for any relevant year). If I try to go down to the month level, Power BI tells me the values do not interact nicely with each other and I do not get a visual.

Anyone could help?

[ToddChitt]

You are in the Power Query forum. This probably belongs in the Desktop forum.

Can you supply some sample data from the two tables? Are the dates actually dates, or actually DATETIME column masquerading/formatted as a Date? What relationships do you have in place?

[BeraG]

Hi, sorry for mismatching the forums, and thanks for asking for more information.

My main table looks like this:

The first and last column are in date format (the last one in the yyyy-mm format). The column providing the timing results is a decimal number with 9 decimal positions "Tempo decimale". The "Data" (date) column is the one with which I have created the relationship with the "Date" table ("Date" column), shown below:

 

The scatter chart I manage to visualise is like this:

It would be really great to be able to show the sequence of months on the x-axis, so as to spread the clusters of dots I get now pinpointing the results.

[ToddChitt]

When you build your scatter plot, yoiu are putting the Date column from the Date table on the X axis, correct? When you do that, does it add four levels of a hierarchy? (Year/Quarter/Month/Day)?

Like this?

If so, click the down arrow next to the Date column and select "Date" instead of "Date Hierarchy"

[BeraG]

Thanks once more. It's actually something I had tried at a certain point. After your message, to be on the safe side, I created my date table again from scratch, but even after checking the "Date" option for the x-axis of the scatter chart, I still get this result, which I am starting to hate 🙂

I get this, if I remove the hierarchy as you suggested, but also if I leave it and try to go down one level. I can't understand why Power BI is asking me to summarise the y values for the month level when it is perfectly happy to show them individually at the year level.

[ToddChitt]

We are at the point where I cannot diagnose this without ALL of the information. Is there any chance you can post your model (please remove sensitive data or otherwise obfuscate it) on a public storage site (like Drop Box public) and supply the link.

[BeraG]

It's entirely public data extracted from the Italian Swimming Federation web site, so absolutely no problem. Would it work if I share the .pbix file in anyway?

[ToddChitt]

Yes, the entire PBIX files. That way, I/we can see all data, relationships, visual settings, etc. 

[BeraG]

Here's a link to the file on file.io:

https://file.io/aeCVPb4LB5GX

[BeraG]

If it can be of any help, the scatter chart is also giving me this information: