Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Oded-Dror
Helper III
Helper III

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 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 interval
Final part presenting the resultset

I've tried to translate TSQL to DAX but it produces wrong results
Any help will be much appriciated.

Thanks,
Oded DrorTime Interval.png

20 REPLIES 20
AmiraBedh
Resident Rockstar
Resident Rockstar

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.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Hi Amira,

 

There is recursion in DAX see below 
https://stackoverflow.com/questions/52766022/recursion-in-dax
Thanks,
Oded Dror

But I need the dataset at least.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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

Please check this '789','2017-10-05 10:32:00.000')
('789','2017-10-05 10:47:00.000'
does not fall within the 15 minute interval

Screenshot_2.pngScreenshot_3.png

 

The running total is reseting every 15 min. where is 10:32 (10:32 - 10:12 = 20)

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.

 

Amira,
Thank you for solving this issue, it works! 

Point to remember DAX dateadd func dosen't have Minute interval like SQL dose.
Thanks again

Oded Dror

Amira,

 

This is not accurate if you see in your result 10:26 - 10:12 is 14 min and not 15
And the last record as well

 

Thanks,

Oded Dror

 

Hi @Oded-Dror 

 

I see now that the 10:26 record is wrong.  I'll let you know when I update it.

Hi @Oded-Dror 

 

10:26 DOES belong in the Group 10:15 since that group would span from 10:15 to 10:30.

 

Please double-check your findings.

 

DimTime - 15 minute buckets.pbix

It's still not accurate it sould be 5 records per group 8:00 , 8:15, 10:12, 10:32 , 10:47

 

Hi @Oded-Dror 

OK.  Lets look at the 2nd page.  When you click on a line in the upper table, it will filter the bottom table.

 

For 8:00, the earliest is at 8:00.  There is also one at 8:05 and another at 8:07.

For 8:15, the earliest is at 8:15.  There is also one at 8:25.

etc.

 

DimTime - 15 minute buckets.pbix

 

Please see the TSQL code abouve (I sent to Amira this morning)

I've looked at it and as Amira pointed out, you can't use CTEs.

 

Please explain what is wrong with my version.  It seems to work for me and you still haven't shown an example where it doesn't work.

 

Please refer to the 2nd page of my pbix.

 

See The Excel sheet - it take 4 iterations (Running Total every 15 min) your solution show 6 record per group also 10:26 - 10:12 = 14

Running Total.png

@Oded-Dror 

 

If you are using 15 minute buckets, why isn’t 10:26 highlighted?
10:12 would be in the '10:00 to 10:15' bucket whereas 10:26 would be in the '10:15 to 10:30' bucket.

 

OR do you mean that the bucket start resets based on something?  If you mean a floating 15 minute bucket, the Time dimension's buckets won't help at all.

 

I need more details

Look at the Excel sheet the interval is reset everytime the running total reach 15 min
Remember 10:12 was the cutting point now we are in 0 again

therefore 10:26 - 10:12 = 14 (we need 1 more to get 15) than 10:32 - 10:26 = 6 

6 + 14 = 20 is >= 15 yes so 10:32 is the correct result
In another word is the running total of delta need to reach 15 min and reset again to 0 and start over thats way your windows of 15 min bucket won't produce the expected result it has to be recursive like TSQL

Sorry.  I thought you meant 15 minutes as in a Time dimension.

 

I'll look at doing it without the time dimension but DAX doesn't support recursion.  any use of a CTE is out.

Yes, the Windows function in DAX may help?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.