Hi there,See image below.In the left I have SQL Server table called example with two ID's And DTTM columns.I need it to capture 15 running min interval with reseting the loop (every 15 min set to 0 again)In the middle I have a view I created which use TSQL with CTE first part grouping (anchor )Second part the looping and reseting - 15 min time intervalFinal part presenting the resultsetI've tried to translate TSQL to DAX but it produces wrong resultsAny help will be much appriciated.Thanks,Oded Dror

To help you better, I would need to understand the logic you've implemented in T-SQL and see the DAX formula you've attempted. Unfortunately, the text in the image is not clear enough to read the details of the code. If you could type out the T-SQL query and the DAX formula you've tried, I could then provide more specific advice on how to correct the DAX formula.Remember, in DAX, there is no direct equivalent of a CTE. Instead, you would use calculated columns, measures, or variables to achieve similar results. Moreover, the concept of "looping" in DAX doesn't work the same way as in T-SQL. You would typically use DAX time intelligence functions, filters, and iterators like CALCULATE, FILTER, and the X functions (like SUMX, AVERAGEX...) to perform row context and filter context operations over the data.

But I need the dataset at least.

Hi Amira, There is recursion in DAX see below https://stackoverflow.com/questions/52766022/recursion-in-daxThanks,Oded Dror

Amira,Here is the TSQL code I useddrop table if exists exampleCREATE TABLE example([ID] [varchar](9) NULL,[DTTM] [datetime] NULL) ON [PRIMARY]Insert into [example] (ID,DTTM) values('123456789','2017-10-05 08:00:00.000'), -- Expected Result('123456789','2017-10-05 08:05:00.000'),('123456789','2017-10-05 08:07:00.000'),('123456789','2017-10-05 08:15:00.000'), -- Expected Result('123456789','2017-10-05 08:25:00.000'),('123456789','2017-10-05 10:12:00.000') , -- Expected Result('123456789','2017-10-05 10:26:00.000'),('123456789','2017-10-05 10:32:00.000'), -- Expected Result('123456789','2017-10-05 10:33:00.000'),('123456789','2017-10-05 10:34:00.000'),('123456789','2017-10-05 10:35:00.000'),('123456789','2017-10-05 10:36:00.000'),('123456789','2017-10-05 10:37:00.000'),('123456789','2017-10-05 10:38:00.000'),('123456789','2017-10-05 10:39:00.000'),('123456789','2017-10-05 10:40:00.000'),('123456789','2017-10-05 10:41:00.000'),('123456789','2017-10-05 10:42:00.000'),('123456789','2017-10-05 10:43:00.000'),('123456789','2017-10-05 10:44:00.000'),('123456789','2017-10-05 10:45:00.000'),('123456789','2017-10-05 10:46:00.000'),('123456789','2017-10-05 10:47:00.000'), -- Expected Result('123456789','2017-10-05 10:48:00.000')Insert into [example] (ID,DTTM) values('789','2017-10-05 08:00:00.000'), -- Expected Result('789','2017-10-05 08:05:00.000'),('789','2017-10-05 08:07:00.000'),('789','2017-10-05 08:15:00.000'), -- Expected Result('789','2017-10-05 08:25:00.000'),('789','2017-10-05 10:12:00.000') , -- Expected Result('789','2017-10-05 10:26:00.000'),('789','2017-10-05 10:32:00.000'), -- Expected Result('789','2017-10-05 10:33:00.000'),('789','2017-10-05 10:34:00.000'),('789','2017-10-05 10:35:00.000'),('789','2017-10-05 10:36:00.000'),('789','2017-10-05 10:37:00.000'),('789','2017-10-05 10:38:00.000'),('789','2017-10-05 10:39:00.000'),('789','2017-10-05 10:40:00.000'),('789','2017-10-05 10:41:00.000'),('789','2017-10-05 10:42:00.000'),('789','2017-10-05 10:43:00.000'),('789','2017-10-05 10:44:00.000'),('789','2017-10-05 10:45:00.000'),('789','2017-10-05 10:46:00.000'),('789','2017-10-05 10:47:00.000'), -- Expected Result('789','2017-10-05 10:48:00.000') Thanks,Oded Dror

Hi Oded-Dror I added a time dimension with 1 row per minute. It also has a 15-minute bucket. (I added a DimDate table.) After adding a couple of relationships and measures, my model is below. DimTime - 15 minute buckets.pbix Let me know if you have any questions.

Time interval | Microsoft Fabric Community

20 Replies

AmiraBedh
Super User
2 years ago
To help you better, I would need to understand the logic you've implemented in T-SQL and see the DAX formula you've attempted. Unfortunately, the text in the image is not clear enough to read the details of the code. If you could type out the T-SQL query and the DAX formula you've tried, I could then provide more specific advice on how to correct the DAX formula.
Remember, in DAX, there is no direct equivalent of a CTE. Instead, you would use calculated columns, measures, or variables to achieve similar results. Moreover, the concept of "looping" in DAX doesn't work the same way as in T-SQL. You would typically use DAX time intelligence functions, filters, and iterators like CALCULATE, FILTER, and the X functions (like SUMX, AVERAGEX...) to perform row context and filter context operations over the data.
- Oded-Dror
  Helper III
  2 years ago
  Hi Amira,
  
  There is recursion in DAX see below
  https://stackoverflow.com/questions/52766022/recursion-in-dax
  Thanks,
  Oded Dror
  - AmiraBedh
    Super User
    2 years ago
    But I need the dataset at least.

Forum Discussion

Time interval

20 Replies

Related content

Scheduling a Microsoft Fabric Pipeline with Specific Time Intervals

Time Interval Grouping

TIme Interval Calculation

Overlapping Time Intervals

Run a pipeline at time intervals within a specific time range