DAX - 15 minute interval question

[amulder]

[FONT=&quot]Hi there,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I have a dataset that has a Car ID (Unit ID), the time (and date) the driver logged in and the time (and date) the driver logged out. I am trying to calculate the number to unique units logged on during each 15 minute block of time each day and display it in a table with the date down the left side and the 15 minute block across the top. I keep running into memory errors because the data set is huge (millions of rows). The other issue I have is that I can't figure out how to show which date the 15 minute block occured in if the unit was logged in over night (i.e. logged on at 1900hrs on January 1st and logged out at 0700hrs on January 2nd). [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I have a sample data set I've dumped into an Excel spreadsheet:[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]https://www.dropbox.com/s/ovt0mtcgov66lk3/Sample Data.xlsx?dl=0[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Any help would be greatly appreciated. I tried this solution but this didn't seem to work:[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]https://community.powerbi.com/t5/Desktop/15-Minute-Increments-between-2-Timestamps/m-p/330330[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Thanks,[/FONT]

 

[VBA Geek]

Hi amulder

You are going to need to make some changes to your data before creating your measure.

You need to go from this table:

Excel 2013/2016
	A	B	C
3	ID	Start Time	End Time
4	11	1/1/2018 12:15	1/1/2018 14:00
Sheet3

To this table (replicating each row at 15 mins intervals) and then splitting the column into a Date Part and a Time part:

Excel 2013/2016
	F	G	H
3	ID	Date	Time
4	11	1/1/2018	12:15:00 PM
5	11	1/1/2018	12:30:00 PM
6	11	1/1/2018	12:45:00 PM
7	11	1/1/2018	1:00:00 PM
8	11	1/1/2018	1:15:00 PM
9	11	1/1/2018	1:30:00 PM
10	11	1/1/2018	1:45:00 PM
11	11	1/1/2018	2:00:00 PM
Sheet3

Then you can create your Time dimension table and your Date dimension table.

The time dimension table can be at 15 mins interval like this (until 23:45)

Excel 2013/2016
	K	L
1	Time	TimeBucket
2	12:00:00 AM	00:00 - 00:14
3	12:15:00 AM	00:15 - 00:29
4	12:30:00 AM	00:30 - 00:44
5	12:45:00 AM	00:45 - 00:59
6	1:00:00 AM	01:00 - 01:14
7	1:15:00 AM	01:15 - 01:29
8	1:30:00 AM	01:30 - 01:44
9	1:45:00 AM	01:45 - 01:59
10	2:00:00 AM	02:00 - 02:14
Sheet3

Then you can create your relationships:

And the measure becomes a simple:

= DISTINCTCOUNT( LoggedInData[Unit] )


You can download the pbix file here: https://1drv.ms/u/s!AiiWkkwHZChHjwHSitswrqbLnn3r

 

[amulder]

Thanks for this. I need to take it one step further though and also count the unit as "Logged In" if it logged in during a previous time block and is still logged in. So for example, if unit 101 logged in at 01/01/2017 at 06:00 and logged out at 01/02/2017 at 04:30 it needs to be included in the count of the log in for the following records. How can I do this? Right now the solution only counts it as logged in at the time that it logs in at. So for example if unit 101 logged in at 01/01/2017 at 06:00 that is the only time block it is counted as logged on.

01/01/2017 06:00:00

01/01/2017 06:15:00

01/01/2017 06:30:00

01/01/2017 06:45:00

01/01/2017 07:00:00

01/01/2017 07:15:00

01/01/2017 07:30:00

01/01/2017 07:45:00

01/01/2017 08:00:00

01/01/2017 08:15:00

01/01/2017 08:30:00

01/01/2017 08:45:00

01/01/2017 09:00:00

01/01/2017 09:15:00

01/01/2017 09:30:00

01/01/2017 09:45:00

01/01/2017 10:00:00

01/01/2017 10:15:00

01/01/2017 10:30:00

01/01/2017 10:45:00

01/01/2017 11:00:00

01/01/2017 11:15:00

01/01/2017 11:30:00

01/01/2017 11:45:00

01/01/2017 12:00:00

01/01/2017 12:15:00

01/01/2017 12:30:00

01/01/2017 12:45:00

01/01/2017 13:00:00

01/01/2017 13:15:00

01/01/2017 13:30:00

01/01/2017 13:45:00

01/01/2017 14:00:00

01/01/2017 14:15:00

01/01/2017 14:30:00

01/01/2017 14:45:00

01/01/2017 15:00:00

01/01/2017 15:15:00

01/01/2017 15:30:00

01/01/2017 15:45:00

01/01/2017 16:00:00

01/01/2017 16:15:00

01/01/2017 16:30:00

01/01/2017 16:45:00

01/01/2017 17:00:00

01/01/2017 17:15:00

01/01/2017 17:30:00

01/01/2017 17:45:00

01/01/2017 18:00:00

01/01/2017 18:15:00

01/01/2017 18:30:00

01/01/2017 18:45:00

01/01/2017 19:00:00

01/01/2017 19:15:00

01/01/2017 19:30:00

01/01/2017 19:45:00

01/01/2017 20:00:00

01/01/2017 20:15:00

01/01/2017 20:30:00

01/01/2017 20:45:00

01/01/2017 21:00:00

01/01/2017 21:15:00

01/01/2017 21:30:00

01/01/2017 21:45:00

01/01/2017 22:00:00

01/01/2017 22:15:00

01/01/2017 22:30:00

01/01/2017 22:45:00

01/01/2017 23:00:00

01/01/2017 23:15:00

01/01/2017 23:30:00

01/01/2017 23:45:00

01/02/2017 00:00:00

01/02/2017 00:15:00

01/02/2017 00:30:00

01/02/2017 00:45:00

01/02/2017 01:00:00

01/02/2017 01:15:00

01/02/2017 01:30:00

01/02/2017 01:45:00

01/02/2017 02:00:00

01/02/2017 02:15:00

01/02/2017 02:30:00

01/02/2017 02:45:00

01/02/2017 03:00:00

01/02/2017 03:15:00

01/02/2017 03:30:00

01/02/2017 03:45:00

01/02/2017 04:00:00

01/02/2017 04:15:00

<tbody>
</tbody>

 

[VBA Geek]

The approach I presented earlier should take this into account already.

You can download this Power BI file to check your results: https://1drv.ms/u/s!AiiWkkwHZChHjwHSitswrqbLnn3r

 

[amulder]

Sorry, I missed the step of creating the Logged In Data table. I am trying to create it now but am getting the following error message:

There's not enough memory to complete this operation. Please try again later when there may be more memory available.

The machine has 16gb of memory.

Is there way I can re-write the Logged In Data table statement? How could I add a filter clause to reduce some of the records?

Thanks

 

[VBA Geek]

How many rows of data does your original table have ? I am assuming you are doing it via power bi, are you using the same query I was using ? Yes the data could be previously filtered if you have unwanted records