I have 2 data sets D1 and D2 which have a few parameters A,B,C,D, a couple of indices(which accepts 0/1) which represent abnormal rise/fall in those parameters.
The database is Azure cosmos DB.
D1 : imported into power bi desktop
D2 : not imported into power bi desktop, located in cosmos DB
Now my D1 has the Data in following manner :
Timestamp,Value of those parameters,abnormal instances .
In my D1, if you drill down from year level to the hour level, everything is normal, but when I come to hour level, for a particular hour I have data aggregated per every 120s i.e. for each hour I have 30 values of those parameters/30 data points.
Hence, minute level is the last level in time hierarchy of my data, cannot go down to the seconds level since an aggregation is performed at minute level(for every 2 mins i.e. 120s).
In my dataset D2 ,
I have the data in a manner similar to dataset D1, the only difference is that when you drill down into hour you get data for every minute i.e. 60 data points, further you can drill down into particular minute to see the values at each second for the recorded parameters.
In this dataset user can find the data right from the year to seconds level, the data is raw and unaggregated.
Requirement :
Dataset to be referred : D1 (having aggregated data)
In my report I should create a visual which displays the values of a single parameter with respect to time, the first instance should be the year view and the average of the parameter, like this the visual should allow drill down till the hour level.
Now at the hour level, we have data aggregated for every 120s/2 mins and hence one cannot drill down further and see the second-wise data in the same visual because minute is the last level of hierarchy in dataset D1.
After reaching the minute level(in D1), if I click on a data point(which shows avg value of the parameter over 120s) I should call dynamically the dataset D2(which has raw data;till the seconds level) on the mouse click and display the raw data for the duration of that 120s(based on click) in the same or another visual in the same report.
I am familiar with the process of drilling down in an hierarchy, I am blank about the point after which I need to dynamically call another dataset(D2 located in cosmos DB) and fetch data fromit and display it in my visual
I am curious about it since I haven't encountered a similar implementation/hint in any blog post/forum.
Any suggestions/solutions/workarounds are most welcomed.
Thanks
