The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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
I want output as a single row like this which is sum of hours, minutes and seconds.
Sorry, I cannot work with screenshots.
Here is the sample data @lbendlin
eq_no | ON | OFT | start_time | end_time | AST | AET |
258 | 09-09-2023 02:18 | 09-09-2023 02:52 | 09-09-2023 02:39 | 09-09-2023 02:41 | 09-09-2023 02:39 | 2023-09-09 02:41:12.000 +0530 |
258 | 09-09-2023 02:18 | 09-09-2023 02:52 | 09-09-2023 02:42 | 09-09-2023 02:44 | 09-09-2023 02:42 | 2023-09-09 02:44:11.000 +0530 |
258 | 09-09-2023 02:18 | 09-09-2023 02:52 | 09-09-2023 02:44 | 09-09-2023 02:45 | 09-09-2023 02:44 | 2023-09-09 02:45:41.000 +0530 |
258 | 09-09-2023 02:18 | 09-09-2023 02:52 | 09-09-2023 02:45 | 09-09-2023 02:49 | 09-09-2023 02:45 | 2023-09-09 02:49:55.000 +0530 |
258 | 09-09-2023 02:18 | 09-09-2023 02:52 | 09-09-2023 02:50 | 09-09-2023 02:51 | 09-09-2023 02:50 | 2023-09-09 02:51:12.000 +0530 |
258 | 09-09-2023 02:18 | 09-09-2023 02:52 | 09-09-2023 02:51 | 09-09-2023 02:52 | 09-09-2023 02:51 | 2023-09-09 02:52:32.000 +0530 |
258 | 09-09-2023 02:18 | 09-09-2023 02:52 | 09-09-2023 02:52 | 09-09-2023 02:52 | 09-09-2023 02:52 | 2023-09-09 02:52:45.000 +0530 |
258 | 09-09-2023 05:21 | 09-09-2023 05:25 | ||||
258 | 09-09-2023 05:26 | 09-09-2023 05:32 | ||||
258 | 09-09-2023 05:42 | 09-09-2023 05:49 | ||||
258 | 09-09-2023 05:50 | 09-09-2023 06:01 | ||||
258 | 09-09-2023 06:02 | 09-09-2023 06:30 | 09-09-2023 06:26 | 09-09-2023 06:28 | 09-09-2023 06:26 | 2023-09-09 06:28:46.000 +0530 |
258 | 09-09-2023 06:02 | 09-09-2023 06:30 | 09-09-2023 06:29 | 09-09-2023 06:30 | 09-09-2023 06:29 | 2023-09-09 06:30:59.000 +0530 |
258 | 09-09-2023 06:31 | 09-09-2023 06:34 | ||||
258 | 09-09-2023 06:38 | 09-09-2023 06:39 | ||||
258 | 09-09-2023 06:40 | 09-09-2023 06:43 | ||||
258 | 09-09-2023 06:45 | 09-09-2023 06:50 | ||||
258 | 09-09-2023 07:03 | 09-09-2023 07:04 | ||||
258 | 09-09-2023 07:44 | 09-09-2023 07:47 | ||||
258 | 09-09-2023 08:35 | 09-09-2023 08:37 | ||||
258 | 09-09-2023 09:08 | 09-09-2023 09:09 | ||||
258 | 09-09-2023 09:29 | 09-09-2023 09:29 | ||||
258 | 09-09-2023 09:29 | 09-09-2023 09:34 | ||||
258 | 09-09-2023 09:41 | 09-09-2023 09:56 | ||||
258 | 09-09-2023 13:07 | 09-09-2023 13:10 | ||||
258 | 09-09-2023 13:15 | 09-09-2023 13:15 | ||||
258 | 09-09-2023 23:30 | 09-09-2023 23:42 | 09-09-2023 23:34 | 09-09-2023 23:42 | 09-09-2023 23:34 | 2023-09-09 23:42:32.000 +0530 |
259 | 09-09-2023 00:00 | 09-09-2023 03:49 | 08-09-2023 23:55 | 09-09-2023 00:06 | 09-09-2023 00:00 | 2023-09-09 00:06:40.177 +0530 |
259 | 09-09-2023 05:07 | 09-09-2023 09:10 | 09-09-2023 05:08 | 09-09-2023 05:08 | 09-09-2023 05:08 | 2023-09-09 05:08:48.000 +0530 |
259 | 09-09-2023 05:07 | 09-09-2023 09:10 | 09-09-2023 05:10 | 09-09-2023 05:10 | 09-09-2023 05:10 | 2023-09-09 05:10:36.000 +0530 |
259 | 09-09-2023 05:07 | 09-09-2023 09:10 | 09-09-2023 05:11 | 09-09-2023 05:11 | 09-09-2023 05:11 | 2023-09-09 05:11:35.000 +0530 |
259 | 09-09-2023 05:07 | 09-09-2023 09:10 | 09-09-2023 05:13 | 09-09-2023 05:15 | 09-09-2023 05:13 | 2023-09-09 05:15:26.000 +0530 |
259 | 09-09-2023 05:07 | 09-09-2023 09:10 | 09-09-2023 05:15 | 09-09-2023 05:18 | 09-09-2023 05:15 | 2023-09-09 05:18:03.000 +0530 |
259 | 09-09-2023 05:07 | 09-09-2023 09:10 | 09-09-2023 05:18 | 09-09-2023 05:21 | 09-09-2023 05:18 | 2023-09-09 05:21:27.000 +0530 |
259 | 09-09-2023 05:07 | 09-09-2023 09:10 | 09-09-2023 05:21 | 09-09-2023 05:21 | 09-09-2023 05:21 | 2023-09-09 05:21:59.000 +0530 |
259 | 09-09-2023 05:07 | 09-09-2023 09:10 | 09-09-2023 05:22 | 09-09-2023 05:24 | 09-09-2023 05:22 | 2023-09-09 05:24:15.000 +0530 |
259 | 09-09-2023 05:07 | 09-09-2023 09:10 | 09-09-2023 05:24 | 09-09-2023 05:24 | 09-09-2023 05:24 | 2023-09-09 05:24:59.000 +0530 |
259 | 09-09-2023 05:07 | 09-09-2023 09:10 | 09-09-2023 05:25 | 09-09-2023 05:26 | 09-09-2023 05:25 | 2023-09-09 05:26:05.000 +0530 |
259 | 09-09-2023 05:07 | 09-09-2023 09:10 | 09-09-2023 05:26 | 09-09-2023 05:28 | 09-09-2023 05:26 | 2023-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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
79 | |
71 | |
48 | |
41 |
User | Count |
---|---|
138 | |
108 | |
71 | |
64 | |
58 |