DAX for Rolling Count of Records over the time window, with results on a record level

[Gosia_Gr]

Hi, I'd need some help with DAX that will return Rolling Count of Records over +/-15min time window, including current record with results on a record level.
I’m working in DirectQuery mode. I am not sure which would work better regarding performance - a new Measure or a new Table.
I simplified the table to a minimum.
Let’s call it FactTable. It has a column with IDs: RecordId, they are unique and not in any order. Each record has its Date Time Stamp: column DateTime.
DAX should calculate Rolling Count of Record IDs in a way that when I pull Table visual with RecordID, DateTime and Rolling count - all record level information from FactTable is retained:

Not sure if needed here, the original model has Date and Time dimension tables linked by DateKey and TimeKey to the FactTable.
Time dimension table has: TimeKey, Time, Minute, Hour. Date dimension table has: DateKey, Date, Month, ect.

Thanks in advance.

[Anonymous · Accepted solution]

Thanks for the reply from SachinNandanwar , please allow me to provide another insight:

Hi, @Gosia_Gr 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the measure I've created for your needs:

Rolling count =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        DATEDIFF ( 'Table'[DateTime], MAX ( 'Table'[DateTime] ), MINUTE ) <= 15
    )
)

3.Here's my final result, which I hope meets your requirements.

If you have any questions, please refer to the attached PBIX file. It is important to note that although my data connection mode is set to Import, it has already been tested in DirectQuery. Since DirectQuery requires a connection to the data source, I uploaded it using the Import method.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gosia_Gr]

Thank you Leroy Lu
Unfortunately, the query reaches the memory limit on my model (1024MB). A View might be my option.

I'd have the last question – anyone knows if a similar result can be achieved by writing DAX as a Visual Calculation? The visual could be filtered to one day only, understand it will be the rolling count would be limited to that day only.

[Anonymous · Accepted solution]

Hi, @Gosia_Gr 

Thank you for your prompt response.

1.Firstly, regarding the query limitation issue, you might consider adjusting the query limits and optimising the queries and data model.

For further details, please refer to:

Set visual query limits in Power BI Desktop - Power BI | Microsoft Learn
Chris Webb's BI Blog: The “Visual Has Exceeded The Available Resources” Error In Power BI
 

2.Secondly, about replicating the DAX I provided within Visual Calculation, based on my practical tests, it is currently not feasible due to the need for filtering. I have not found an effective solution for this yet.

For further details, please refer to:

Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

3.Finally, for limiting to just one day, you can directly use the built-in date slicer:

 
Create a relative time slicer or filter in Power BI - Power BI | Microsoft Learn

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gosia_Gr]

Hi Leroy Lu, thank you for the comprehensive response and the links to relevant sources with additional info.

Gosia

[SachinNandanwar]

Can you explain how you have a count of 15 & 16 for the first two rows ?

[Gosia_Gr]

Hi Sachin

Sorry, I forgot to mention. There are more records on 24/11/2024, the table it just a snapshot - the calculation in 1st and 2nd row is counting records before 22/11/2024 22:50.

The whole model has roughly 350 records per day, starting from 2019.

DAX calculation should be continuously rolling, over midnight records as well, i.e. not starting count for each day.

Thanks for your help

Gosia

[Anonymous · Accepted solution]

Thanks for the reply from SachinNandanwar , please allow me to provide another insight:

Hi, @Gosia_Gr 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the measure I've created for your needs:

Rolling count =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        DATEDIFF ( 'Table'[DateTime], MAX ( 'Table'[DateTime] ), MINUTE ) <= 15
    )
)

3.Here's my final result, which I hope meets your requirements.

If you have any questions, please refer to the attached PBIX file. It is important to note that although my data connection mode is set to Import, it has already been tested in DirectQuery. Since DirectQuery requires a connection to the data source, I uploaded it using the Import method.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.