topic Re: Identify time intervals with n number of records in DAX Commands and Tips

Identify time intervals with n number of records

Anonymous — Tue, 07 Jan 2020 19:26:40 GMT

Hello All,

I have a requirement to find all the 10 minute time intervals with more than 2 records, from a dataset.

My dataset looks like,

EventTime

01/01/2020 00:01:00

01/01/2020 00:02:00

01/01/2020 00:03:00

01/01/2020 00:12:00

01/01/2020 00:13:00

01/01/2020 00:14:00

01/01/2020 00:15:00

01/01/2020 00:23:00

01/01/2020 00:24:00

01/01/2020 00:48:00

01/01/2020 00:49:00

01/01/2020 00:58:00

I want output like

EventTime Count

01/01/2020 00:01:00 3

01/01/2020 00:02:00 2

01/01/2020 00:03:00 2

01/01/2020 00:12:00 4

01/01/2020 00:13:00 3

01/01/2020 00:14:00 3

01/01/2020 00:19:00 3

01/01/2020 00:23:00 2

01/01/2020 00:24:00 1

01/01/2020 00:48:00 2

01/01/2020 00:49:00 2

01/01/2020 00:58:00 1

My dataset is very large and applying any measure to identify the nth record is not working.

Any help on this issue is greatly appreciated.

Thank you.

Re: Identify time intervals with n number of records

JarroVGIT — Tue, 07 Jan 2020 20:23:44 GMT

Use this as a calculated column:

CalculatedColumn = VAR _curEventTime = Table2[EventTime] VAR _tmpTable = FILTER(Table2, Table2[EventTime]>= _curEventTime && Table2[EventTime] < _curEventTime + 10/(24*60)) RETURN COUNTROWS(_tmpTable)

Result:

If any questions, let me know!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Re: Identify time intervals with n number of records

Anonymous — Wed, 08 Jan 2020 12:53:46 GMT

Thank you djerro123! I tried your solution, but my dataset being very large, Power BI Desktop has been processing for morethan 12 hours now, and is not yet done. Do you know of any alternatives?

Re: Identify time intervals with n number of records

JarroVGIT — Wed, 08 Jan 2020 14:32:02 GMT

Hi Anonymous ,

When you have a massive dataset, you might want to explore other ways to enrich your data. For example, you could use Azure Synapse Analytics to store your data and a Azure Databricks workspace to analyze this on a Spark cluster. In your usecase, where the dataset consist out of millions of rows (billions?), that is highly recommendable and faster.

How big is the dataset anyway? (in terms of Rows and GB?)

Re: Identify time intervals with n number of records

Anonymous — Thu, 09 Jan 2020 16:48:02 GMT

Hello JarroVGIT

My Dataset consist of 3 million records. I will check out the options you mentioned.

Thanks for your consistent support to the Power BI community!