Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Jessica_17
Helper IV
Helper IV

Sum of timestamp of date column created by measure in table visual

‎03-02-2024 02:57 AM

Hello All, 

I need to convert this sql to DAX , can anyone please help me in this, let me know if anoyone requires sample data and 

:fromDate and :toDate can more than 24hrs gap.

 

SELECT ROUND(SUM(EXTRACT (EPOCH FROM OFT - ON)) / 60) diff,
                                       ROUND(sum(idle_diff) / 60) idle_time_min
                                  FROM (SELECT eq_no, ON, OFT, SUM(EXTRACT (EPOCH FROM AET - AST)) idle_diff
                                          FROM (SELECT g.equip eq_no, GREATEST (g.eg_on, :fromDate) ON, LEAST (g.OFT, :toDate) OFT,
                                                       i.start_time start_time, i.end_time end_time,
   CASE WHEN i.start_time IS NOT NULL THEN GREATEST (i.start_time, g.eg_on, :fromDate) ELSE NULL END AST,
   CASE WHEN i.start_time IS NOT NULL THEN LEAST(COALESCE(i.end_time, NOW()), COALESCE(g.OFT, NOW()), :toDate) ELSE NULL END AET
                                                  FROM ignition g
   LEFT OUTER JOIN
                                                       (SELECT *
                                                          FROM movement m
                                                         WHERE m.tml_id = :tmlId
                                                           AND m.eq_type = :eqType
                                                           AND m.start_time <= :toDate
                                                           AND (m.end_time >= :fromDate OR m.end_time IS NULL)
                                                       ) i
                                                       ON (    g.tml_id = i.tml_id AND g.eq_type = i.eq_type AND g.equip = i.bi_em_eq_no
                                                           AND i.start_time <= COALESCE (g.OFT, NOW())
                                                           AND (i.end_time >= g.eg_on OR i.end_time IS NULL))
                                                 WHERE g.tml_id = :tmlId
                                                   AND g.eq_type = :eqType
                                                   AND g.eg_on <= :toDate
                                                   AND (g.OFT >= :fromDate OR g.OFT IS NULL)
                                               ) m
                                        GROUP BY eq_no, ON, OFT
                                       ) m;

 

 

Message 1 of 10
585 Views
0
9 REPLIES 9
lbendlin
Super User
Super User

‎03-02-2024 10:17 AM

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Message 2 of 10
530 Views
0
Jessica_17
Helper IV
Helper IV
In response to lbendlin

‎03-02-2024 10:03 PM

HI @lbendlin 

Here is a screenshot of similar data

Jessica_17_0-1709445609129.png

 

 

I want output as a single row like this which is sum of hours, minutes and seconds.

Jessica_17_1-1709445681054.png

 

 

Message 3 of 10
518 Views
0
lbendlin
Super User
Super User
In response to Jessica_17

‎03-03-2024 04:51 AM

Sorry, I cannot work with screenshots.

Message 4 of 10
497 Views
0
Jessica_17
Helper IV
Helper IV
In response to lbendlin

‎03-03-2024 06:29 AM

Here is the sample data @lbendlin 

eq_noONOFTstart_timeend_timeASTAET
25809-09-2023 02:1809-09-2023 02:5209-09-2023 02:3909-09-2023 02:4109-09-2023 02:392023-09-09 02:41:12.000 +0530
25809-09-2023 02:1809-09-2023 02:5209-09-2023 02:4209-09-2023 02:4409-09-2023 02:422023-09-09 02:44:11.000 +0530
25809-09-2023 02:1809-09-2023 02:5209-09-2023 02:4409-09-2023 02:4509-09-2023 02:442023-09-09 02:45:41.000 +0530
25809-09-2023 02:1809-09-2023 02:5209-09-2023 02:4509-09-2023 02:4909-09-2023 02:452023-09-09 02:49:55.000 +0530
25809-09-2023 02:1809-09-2023 02:5209-09-2023 02:5009-09-2023 02:5109-09-2023 02:502023-09-09 02:51:12.000 +0530
25809-09-2023 02:1809-09-2023 02:5209-09-2023 02:5109-09-2023 02:5209-09-2023 02:512023-09-09 02:52:32.000 +0530
25809-09-2023 02:1809-09-2023 02:5209-09-2023 02:5209-09-2023 02:5209-09-2023 02:522023-09-09 02:52:45.000 +0530
25809-09-2023 05:2109-09-2023 05:25    
25809-09-2023 05:2609-09-2023 05:32    
25809-09-2023 05:4209-09-2023 05:49    
25809-09-2023 05:5009-09-2023 06:01    
25809-09-2023 06:0209-09-2023 06:3009-09-2023 06:2609-09-2023 06:2809-09-2023 06:262023-09-09 06:28:46.000 +0530
25809-09-2023 06:0209-09-2023 06:3009-09-2023 06:2909-09-2023 06:3009-09-2023 06:292023-09-09 06:30:59.000 +0530
25809-09-2023 06:3109-09-2023 06:34    
25809-09-2023 06:3809-09-2023 06:39    
25809-09-2023 06:4009-09-2023 06:43    
25809-09-2023 06:4509-09-2023 06:50    
25809-09-2023 07:0309-09-2023 07:04    
25809-09-2023 07:4409-09-2023 07:47    
25809-09-2023 08:3509-09-2023 08:37    
25809-09-2023 09:0809-09-2023 09:09    
25809-09-2023 09:2909-09-2023 09:29    
25809-09-2023 09:2909-09-2023 09:34    
25809-09-2023 09:4109-09-2023 09:56    
25809-09-2023 13:0709-09-2023 13:10    
25809-09-2023 13:1509-09-2023 13:15    
25809-09-2023 23:3009-09-2023 23:4209-09-2023 23:3409-09-2023 23:4209-09-2023 23:342023-09-09 23:42:32.000 +0530
25909-09-2023 00:0009-09-2023 03:4908-09-2023 23:5509-09-2023 00:0609-09-2023 00:002023-09-09 00:06:40.177 +0530
25909-09-2023 05:0709-09-2023 09:1009-09-2023 05:0809-09-2023 05:0809-09-2023 05:082023-09-09 05:08:48.000 +0530
25909-09-2023 05:0709-09-2023 09:1009-09-2023 05:1009-09-2023 05:1009-09-2023 05:102023-09-09 05:10:36.000 +0530
25909-09-2023 05:0709-09-2023 09:1009-09-2023 05:1109-09-2023 05:1109-09-2023 05:112023-09-09 05:11:35.000 +0530
25909-09-2023 05:0709-09-2023 09:1009-09-2023 05:1309-09-2023 05:1509-09-2023 05:132023-09-09 05:15:26.000 +0530
25909-09-2023 05:0709-09-2023 09:1009-09-2023 05:1509-09-2023 05:1809-09-2023 05:152023-09-09 05:18:03.000 +0530
25909-09-2023 05:0709-09-2023 09:1009-09-2023 05:1809-09-2023 05:2109-09-2023 05:182023-09-09 05:21:27.000 +0530
25909-09-2023 05:0709-09-2023 09:1009-09-2023 05:2109-09-2023 05:2109-09-2023 05:212023-09-09 05:21:59.000 +0530
25909-09-2023 05:0709-09-2023 09:1009-09-2023 05:2209-09-2023 05:2409-09-2023 05:222023-09-09 05:24:15.000 +0530
25909-09-2023 05:0709-09-2023 09:1009-09-2023 05:2409-09-2023 05:2409-09-2023 05:242023-09-09 05:24:59.000 +0530
25909-09-2023 05:0709-09-2023 09:1009-09-2023 05:2509-09-2023 05:2609-09-2023 05:252023-09-09 05:26:05.000 +0530
25909-09-2023 05:0709-09-2023 09:1009-09-2023 05:2609-09-2023 05:2809-09-2023 05:262023-09-09 05:28:15.000 +0530
Message 5 of 10
492 Views
0
lbendlin
Super User
Super User
In response to Jessica_17

‎03-03-2024 02:43 PM

Thank you for the sample data.  Where is the Movement table?  What is your expected outcome?

Message 6 of 10
473 Views
0
Jessica_17
Helper IV
Helper IV
In response to lbendlin

‎03-03-2024 10:12 PM

Hi @lbendlin 
For "ON" column

I have a calendar table, in which suppose a user selected a range from 08 sep 2022 00:00:00 to 08 sep 2022 23:59:59, then it should call the eg_on column from movement table which contains equip and its respective date columns, in this example we have to assume eg_on and equip, now the according to the selection we should have data of columns as this where condition  
g.eg_on <= :toDate

then this condition should be added

greatest (g.eg_on, :fromDate), after this I should get all the sum of this dates hour , min and sec to second in a single row irrespective of any column being there beside it.

Message 7 of 10
464 Views
0
lbendlin
Super User
Super User
In response to Jessica_17

‎03-04-2024 04:44 AM

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Message 8 of 10
454 Views
0
Jessica_17
Helper IV
Helper IV
In response to lbendlin

‎03-04-2024 12:07 PM

HI @lbendlin 
I wont be able to give you sample data for the other tables, but Can you please help in the ledt join part, I dont know how to perform left join in Power BI using DAX as I will need to use calculatetable() and summarize function().

Message 9 of 10
439 Views
0
lbendlin
Super User
Super User
In response to Jessica_17

‎03-04-2024 12:34 PM

Sorry, cannot help without meaningful sample data. I hope someone else can help you further.

Message 10 of 10
431 Views

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

View All