Reputation: 1
So, I have a large dataset that I'm importing from a SQL Server database. I have a value column set in Y axis (set to "Don't Summarize) and another column in DATETIME format (YYYY-MM-DD HH:MM:SS) in X Axis. On Plotting the Scatter Chart, all the readings seem to be stuck in the Year Level and the points appear to not show beyond their respective year points in X Axis. For example, all recordings made in the year 2022 are exactly shown to be on the 2022 point in X axis which is incorrect. When I try to drill down (go next level of the hierarchy), I get an error message on my Scatter Chart saying "Your X and Y Axis aren't playing together nicely. Replace X Axis field with a numerical value or change the Y Axis field values to summarizations".
In an attempt to solve this issue, I ensured that PowerQuery Editor inside Transform Option shows the datetime format correctly. I also duplicated that column and separately transformed the two into date and time to check whether this might be the problem, but the issue didn't solve. On, aggregating the Y Axis values (such as Average, Count, etc.), I can drill down to time level without any problem. But, on unaggregated mode, which is what I wanted to visualize, this is not possible.
Update: In the date hierarchy, I tried to only select date number, and it works. But it doesn't work when I try to combine this with other value such as month and year. Year separately works in x axis too. I tried to drag only time in X axis and it didn't work.
Upvotes: 0
Views: 430
Reputation: 1
So, I managed to find why this was happening. Posting my findings here so others might benefit.
Let's say you have a QC table. A sample is taken only once per given time, but for the same sampling time, multiple parameters can be recorded. Hence, for the same material, at the same time, there can be multiple rows signifying multiple parameter values recorded.
For visualization sake, you might use a slicer to only visualize one parameter at a time. This would reduce the number of rows per material and per time unit to 1. But PowerBI logic still wants to aggregate. The explanation of why it happens is below.
If we aggregate the y axis using AVERAGE or MEDIAN and drill down all the way to TIME (since for my table, I had a unique time reading for the same material for the same attribute recorded), I drill up/down without encountering any error. Let's say we used AVERAGE aggregation. But, since we are on the time level of drilling down, there is only one reading per unit time (as long as you set the slicer to only show one attribute reading at a time). So, average of 1 reading will be the reading itself.
Now, let's say you drill up in the visualization to Date level. Now, since each date has multiple readings in it, AVERAGE aggregation will give you average of the readings done for the date. But, if there was only one reading done per date, the average aggregate function will still give out the value itself.
So, you need to be a bit vigilant to set up your slicers and filters properly and drill down to the level you took your readings. In this way, even though the y axis shows the data is aggregated, you should get the exact recorded value.
Apologies if the text is a bit confusing. English isn't my native language.
Upvotes: 0