Forum Discussion

Oded-Dror's avatar
Oded-Dror
Helper III
2 years ago

Time interval

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...
Need Help
AmiraBedh's avatar
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.

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    2 years ago

    But I need the dataset at least.

    • Oded-Dror's avatar
      Oded-Dror
      Helper III
      2 years ago

      Amira,
      Here is the TSQL code I used

      drop table if exists example

      CREATE 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

      • gmsamborn's avatar
        gmsamborn
        Super User
        2 years ago

        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.