Forum Discussion
Generateseries: unexpected behaviour
I am building a maintable containing a long series of points in time. This maintable looks up / checks values from other tables which are not continuous (so containing gaps in time). To populate this table I use this query:
Now the GENERATESERIES is throwing a weird result: I add 10 seconds to the next step by TIME(0,0,10) (so expect to see only times ending on :00, :10, :20 etc). However after a while the time ends on :09, :19 et! This makes my whole model not working anymore, as I also have data in the other tables on the every-10 seconds schedule.
After 9420 rows this event occurs for one machine, for the other machine after 5418 rows (see the picture below).
Anyone an idea why this happens? At first I had 1/24/360 as timestep in GENERATESERIES , so I thought it had to do with floating point values, but now I am using TIME(0,0,10) and the issue is still here.
Hi ivostammis,
I tried different variations of your table, and I experience the same issue as you. And I can't get it to work properly. Funny thing is that when I create new calculated column like this:
Date time column = DATEVALUE([Value])+TIMEVALUE([Value])it returns the correct values. So that is one possible workaround. Another workaround would be to create the datetime-list with Power Query. Using Power Query is usually beneficial for how Power BI treats your table. Here is a recipe for how to create the table in power query:
http://datacornering.com/how-to-generate-date-and-time-interval-list-using-power-query/
Cheers,
Sturla
3 Replies
i think your floating point hunch is still correct.
Instead of cumulatively adding ten seconds, I would use a fixed datetime starting point and calculate the offsets from there. That way you can avoid the drift.
Hi ivostammis,
I tried different variations of your table, and I experience the same issue as you. And I can't get it to work properly. Funny thing is that when I create new calculated column like this:
Date time column = DATEVALUE([Value])+TIMEVALUE([Value])it returns the correct values. So that is one possible workaround. Another workaround would be to create the datetime-list with Power Query. Using Power Query is usually beneficial for how Power BI treats your table. Here is a recipe for how to create the table in power query:
http://datacornering.com/how-to-generate-date-and-time-interval-list-using-power-query/
Cheers,
SturlaThanks! I ended up generating the table in powerquery, as DAX was already slowing my model down a lot I hope this also contributes to refresh speed 🙂
