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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jessica_17
Helper III
Helper III

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

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;

 

 

9 REPLIES 9
lbendlin
Super User
Super User

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...

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

 

 

Sorry, I cannot work with screenshots.

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

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

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.

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

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().

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors