Visual changes resolution of data points at will

[MeinerEiner]

[UPDATE]

PowerBi works like a charm. The issue was related to the raw data in Excel. Excel does weird things with specific date time format while extending a column by mark and drag/pull down. I don't know why this happens but it is no issue as the data created via Excel were only intended to test PowerBI and work out a model. The data used later on is generated automatically and will not contain the weird millisecond-issue as seen in Excel with the test data.

red - Initial data

green - extended data by marking and dragging down the first 4 rows

blue - how the datetime looks, if milliseconds are not displayed

black - format used in Excel

[ORIGINAL POST]
Hi,

I do have an issue with visualization of plot data and think one of you might have encountered this issue.

Goal:

-Plot Temperature data over time

-Plot a status (like on/off) on the secondary axis parallel to the temperature plot

Issue:

-The status plot does lack some data points which are definetily there

Further Information:

- It doesn't matter if the status plot is primary or secondary y-axis

- The temperature plot is fine (1 second resolution)

- The status plot starts to lower resolution when the temperature plot "ends"

- There is a DateTime-table with which the plot are related to (The format is always DateTime General)

Do you have an idea why this is happening? Many thanks in advance.

DateTimeTemperature plotStatus plotRelations

[Anonymous]

Hi @MeinerEiner 

Is your problem solved, if so, please mark the corresponding reply as a solution, which will help users with the same problem to better solve their problem.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MeinerEiner · Accepted solution]

Hey. I updated the initial post, hope this is sufficient for you. The issue is not with PowerBI but with Excel.

[MeinerEiner]

The issue is pinned down to the DateTime-Format in PowerBI. On the surface it looks like timestamps do match but taking a closer look shows that they are not and this is the issue.

-18:40:59 is fine
-18:41:00 is not, only at the surface

 

 

 

[AllisonKennedy]

@MeinerEiner  Thanks for this! You've just give hard data to confirm what I've known for years, but not fully understood. Use Power Query to add a datetime key to your table and that should fix things:

DateTime.ToText( [DateTime], "yyyyMMddHHmmss" )

[MeinerEiner]

[Deleted as my assumption was wrong]

[AllisonKennedy]

Glad you have it working @MeinerEiner - just to clarify, have you created a new dimension table for date time with the values you want on the x-axis and then connected each of your three tables to that table? 

Or are you using the Date only?

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

https://excelwithallison.blogspot.com/2023/04/power-bi-dimtime-table.html 

Datetime has a high granularity, so keeping those combined in one table can often lead to large models and difficulty later, so just wanting to learn from you too and see what works 🙂

Thanks for the updates!

[MeinerEiner]

Hey @AllisonKennedy

Yes, you are right. "X-Axis_Full..." is a new table containg the MIN/MAX Datetime created out of off all tables I want to plot. I got this from a user at stackoverflow. The new DateTime-table is created in Power Query via advanced editor.

datetime - PowerBI Create Table with date AND time with 1 second increment - Stack Overflow

For whatever reason some data gets lost during transformation. I don't know why but it seems that something went wrong there. While merging the data some data is lost. It seems as if PowerBI can't match the DateTime properly even if it seems to be the same format. If I use the merge query-feature the result is a table over the full range of DateTime with all temperature data but for the second half of the X-Axis PowerBI does not write all status data into the new table rather than only the few data points that are shown in the plot. I checked the DateTime-format and it seems fine...

[AllisonKennedy]

@MeinerEiner 

Can you provide a bit more info on why you have three tables? The both cross-filter direction for relationships can introduce ambiguity into your model that might cause issues.

As for your plot, if you look closely at the top right of the visual, do you see an info 'i' icon? I'm guessing you would have the error 'too many data points' or 'showing representative sample' and that it is only picking some data points to display.

I have encountered this issue before, one way to solve it is to use a date table and reduce the granularity of your data (for example every hour only instead of every minute, or every day if possible).

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

If you need that full granularity, there's not too much you can do to solve it, except try using a measure and turning off the data points if you can. There should also be some options related to 'high density sampling' that help ensure the max / min values are at least plotted: 

https://excelwithallison.blogspot.com/2021/09/visual-review-area-chart.html

[MeinerEiner]

[Deleted as the solution is added to the initial post]