Forum Discussion
How to visualise datetime filtered data from huge dataset
Hi all -
Am running into some issues trying to visualise datetime data from a huge time series dataset. I have about 2.5 million rows of data (about one record every 2s over three months of sensor data) in my primary dataset (following structure) :
I have a second data set which consists of event records with a timestamp start and timestamp end with some two related data points with each record. I currently have this plotted with no issues on a line chart. Schema and chart example below
My goal is to select a certain event record (by clicking on the point on the above chart) and use a measure to get the starting and ending timestamp to filter the data being fed into the second chart. I've proven that this works as the measures record the correct value :
I want the Min_TS and Max_TS to be the start and end of the visualisation, but apparently that's a lot harder to do than it seems. I can't force any slicers to work with the time data, as there are only date options. So I have to input it under the formatting tab as functions for start and end for the x-axis.
Here's what the data looks like in the line chart. Only one (or sometimes no) data points. And (I believe) due to the large amounts of data, PowerBI smooths the data so much I only get one data point throughout the entire chart.
The missing data points do exist in my data set as shown here :
The data should look something along the lines of this (from python matplot lib) :
One option I've looked into solving this is by using python visualisations which allow for more data points - however with limited ability to slice or filter a data set using TIME, I'm limited to the first 150,000 data points which means my last 2.35M data points are useless.
So my questions are :
- Is there a way to step outside of the limitation for data visualisations with large data sets when filtering for a smaller data set (between Min_TS and Max_TS)?
- Is there a way to pass a TIME-filtered data set to the Python Visualisation in order to bypass the PowerBI filtering altogether?
Thanks so much for your help!
Hi -
Have found a solution that currently is working. Was worried something like this wouldn't work. Created the following measure :
WithinRange =VAR MinTS = [Min_TS] - TIME(0,0,10)VAR MaxTS = [Max_TS] + TIME(0,0,20)RETURNCOUNTROWS(FILTER(compiled_file,compiled_file[timestamp] > MinTS && compiled_file[timestamp] < MaxTS))Then filter the visual where WithinRange is not blank
3 Replies
- brspencer90Frequent Visitor
Hi -
Have found a solution that currently is working. Was worried something like this wouldn't work. Created the following measure :
WithinRange =VAR MinTS = [Min_TS] - TIME(0,0,10)VAR MaxTS = [Max_TS] + TIME(0,0,20)RETURNCOUNTROWS(FILTER(compiled_file,compiled_file[timestamp] > MinTS && compiled_file[timestamp] < MaxTS))Then filter the visual where WithinRange is not blank- V-lianl-msft
Community Support
Thanks for your sharing
- brspencer90Frequent Visitor
I definitely keep working on a solution after I post these! Glad I found a solution to this one. Hopefully will help someone in the future.