Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have another question,
A want to to count time difference between actual row and next row, when "bit" equals 1, like beneath:
Date/Time | Bit | Time Difference |
2018-01-11T17:03:00 | 1 | 10 |
2018-01-11T17:03:10 | 1 | 10 |
2018-01-11T17:03:20 | 1 | 4 |
2018-01-11T17:03:24 | 1 | 10 |
2018-01-11T17:03:34 | 1 | 10 |
2018-01-11T17:03:44 | 1 | 10 |
2018-01-11T17:03:54 | 1 | 6 |
2018-01-11T17:04:00 | 0 | |
2018-01-11T17:04:10 | 0 | |
2018-01-11T17:04:16 | 0 |
There is no function for next row in table. I've tried different functions but always with error or 0 value in TimeDifference.
I also added two index column. The difference between them is: Index.1=Index + 1. Tried to use it but i have syntax error:
I made this using method from this tutorial:
https://www.youtube.com/watch?v=xN2IRXQ2CvI&app=desktop
But maybe there is another, better way?
What will happen when i delete column used to merge tables, when my data is live streamed from IoT Hub to Azure StreamAnalytics to TableStorage, to Power BI. Would it work for another rows after?
Solved! Go to Solution.
May be a MEASURE instead of a column would handle memory better.
Try this MEASURE
Time Difference Measure = VAR NextRow = CALCULATE ( MIN ( TableName[Date/Time] ), FILTER ( ALL ( TableName ), TableName[Date/Time] > SELECTEDVALUE ( TableName[Date/Time] ) ) ) RETURN IF ( SELECTEDVALUE ( TableName[Bit] ) = 1, DATEDIFF ( SELECTEDVALUE ( TableName[Date/Time] ), NextRow, SECOND ) )
Have a look at this article as it does something very similar. The trick is to use EARLIER.
You may need to use MAX instead of MIN, just depends on which direction you want to go.
Hi @pak
If your Column is formatted as Date/Time, then this calculated column will get you the desired result
Time Difference = VAR NextRow = CALCULATE ( MIN ( TableName[Date/Time] ), FILTER ( ALL ( TableName ), TableName[Date/Time] > EARLIER ( TableName[Date/Time] ) ) ) RETURN IF ( TableName[Bit] = 1, DATEDIFF ( TableName[Date/Time], NextRow, SECOND ) )
Thanks,
Your method works well in small table, but in my case i have for now 22k rows and when i tried to do this i'm getting information:
"There's not enough memory to complete this operation. Please try again later when there may be more memory available."
Should it need so much power to calculate that?
May be a MEASURE instead of a column would handle memory better.
Try this MEASURE
Time Difference Measure = VAR NextRow = CALCULATE ( MIN ( TableName[Date/Time] ), FILTER ( ALL ( TableName ), TableName[Date/Time] > SELECTEDVALUE ( TableName[Date/Time] ) ) ) RETURN IF ( SELECTEDVALUE ( TableName[Bit] ) = 1, DATEDIFF ( SELECTEDVALUE ( TableName[Date/Time] ), NextRow, SECOND ) )
Sadly i don't have any result with this method (all rows are null). I tried to mix it up but with no result.
It works with the sample data you shared
So this is strange, cause in my case it doesn't:
The only difference: i have ";" and you have ",". With "," i get Syntax error.
You are adding it as a calculated column.
PLease add it as a MEASURE
Thanks @Zubair_Muhammad - for your time and patience 🙂
Rookie mistake! 🙂
It would be very helpfull for me in the future!
So did it work???... No memory issues??
With even 1000 of rows it is ok, but when i tried to do this with all data (for now it's over 30000 rows) it was using 100% of my memory for 10minutes with no result. I had to kill the process because i was unable to do anything on my PC.
Maybe it is because we take MIN from all rows from table with every row calculation?
Maybe i'll add INDEX column and next row will be INDEX+1 row, so there is no need to calucate all?
Also with Time Diference Measure i was unable to SUM all time difference, and i have to do so.
Did your mehtod with INDEX work to reduce memory consumption? If yes, how exactly did you realize it?
Thank you very much
You can add MEASURE from the MODELLING tab or by right clicking the Table
Then use a Table Visual as shown in the Picture below
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |