SQL to DAX: I am hoping someone could help me rewrite this SQL code in DAX.

[willow1985]

I am a beginner in both SQL and DAX. I have been re-creating a Query from Excel into Power BI but writing the calculation for this one column has me stumped and no one at my company knows the program well enough.
Here is the SQL calculation:

ROUND((SELECT SUM(IF(time_resumed = 0, UNIX_TIMESTAMP(), time_resumed) - time_paused) / 86400 FROM erp_workorder_inactive WHERE id_workorder = t1.id_primary))  AS `Paused Days (Total)`

The farthest I have got in DAX is below, but have no idea how to do the rest:

= Table.AddColumn(#"SubTotal Calc", "Paused Days", each ([winnipeg.erp_workorder_inactive.time_resumed]-[winnipeg.erp_workorder_inactive.time_paused])/86400)

Thank you to anyone who can help!

 

[Matt Allington]

DAX is not SQL. The languages are approach to the tools are different. While it might be possible to get a technical match with DAX code against your SQL, it is almost guaranteed that what you will end up with is not what you really need.

how about you post your table structure and explain the problem you have.

 

[willow1985]

I will see if I can try and explain it better. I have only been working with both DAX and SQL for a few days...

so in DAX I wrote:

= Table.AddColumn(#"Removed Columns1", "Paused Days", each Number.RoundUp((([winnipeg.erp_workorder_inactive.time_resumed]-[winnipeg.erp_workorder_inactive.time_paused])/86400),0))

Which works for some of the rows I have but not all and I am not sure why.
I did not write the SQL code, someone else did and it appears it is converting seconds to days and rounding but then I get some weird results for other rows like the picture below (the result should be 43 in the original SQL code but I get -18677:

 

[peter789]

As Matt said a sample of your data and a description of what you want would really help.
You are not using DAX, the sample of code provided is M language used in Power Query. This is totally different to DAX. The calculation you provided is mathematically correct (check it with a calculator). I think you need to group the data in some way to aggregate the time differences. I would perhaps be using M to enter and transform the data before loading into the data model, then you can use a function like SUMX in a DAX measure the find the sum of the difference.

 

[willow1985]

Delete this post, sorry

 

[willow1985]

As Matt said a sample of your data and a description of what you want would really help.
You are not using DAX, the sample of code provided is M language used in Power Query. This is totally different to DAX. The calculation you provided is mathematically correct (check it with a calculator). I think you need to group the data in some way to aggregate the time differences. I would perhaps be using M to enter and transform the data before loading into the data model, then you can use a function like SUMX in a DAX measure the find the sum of the difference.

Ok, I think I understand what you are saying. Like I said I am brand new to power query.
My data is coming from an ERP system. The original Query was written by someone in IT who used SQL language to clean it up and put it into excel.
I wanted to replicate this into power query and have almost done so minus this paused day column.
To help I have put the data in Excel. For each workorder there is time resumed and time paused. Somehow in the original Query made by IT they used this data and calculated how much time the work was paused for in days. Not only am I unsure on the math but I do not know how to replicate this in power query. I hope I have provided what you guys need in order to help. The yellow column is the conclusion IT came to.

Workorders_test.xlsx
	A	B	C	D	E	F
1	W/O Number	Date Received	Date Shipped	Time Paused	Time Resumed	Paused Days (Total)
2	112748	2021-02-04	2021-03-12	1612980622	0	47
3	112747	2021-02-03	2021-02-26	1613665602	0	39
4	112594	2021-01-21	2021-03-10	1612874926	0	48
5	112513	2021-01-15	2021-02-11	1611256475	1611774202	6
6	112475	2021-01-13	2021-02-04	1611162817	1611233545	1
7	112418	2021-01-06	2021-01-28	1610630042	0	74
8	112417	2021-01-06	2021-01-20	1610456383	0	76
9	112398	2021-01-04	2021-01-22	1609938988	1610109950	2
10	112159	2020-12-11	0	1611930477	0	59
11	112107	2020-12-07	2021-01-14	1607440405	1610458993	35
12	111786	2020-11-12	0	1613998777	0	35
13	111785	2020-11-12	2021-03-26	1612371033	0	54
14	111680	2020-11-02	0	1604415707	0	146
15	111679	2020-11-02	0	1604415692	0	146
16	111664	2020-10-28	2021-02-04	1604424076	0	146
17	111550	2020-10-22	2020-12-09	1603719501	0	154
18	111549	2020-10-22	2020-12-09	1603719478	1603734274	0
19	111548	2020-10-22	2020-12-11	1603719447	0	154
20	111547	2020-10-22	2020-12-09	1603719420	0	154
21	111530	2020-10-21	2020-11-26	1603718721	0	154
22	111446	2020-10-15	2021-02-24	1603284994	1603477015	2
23	111289	2020-09-24	2021-03-12	1605011305	0	139
24	110857	2020-08-14	2020-10-26	1600787626	0	188
25	110856	2020-08-14	2020-10-26	1600787613	0	188
26	110854	2020-08-14	2020-10-26	1600787640	0	188
27	110790	2020-08-06	2021-02-19	1602602812	0	167
28	110572	2020-07-13	2020-08-07	1595598964	0	248
29	110521	2020-07-07	0	1600081920	1601315429	196
30	110500	2020-07-06	2020-12-03	1595426209	1597858209	226
31	110478	2020-06-30	2020-10-29	1603906265	0	152
32	110436	2020-06-29	2021-01-12	1600177927	1600345940	2
33	110357	2020-06-22	2020-08-28	1592935779	0	279
34	110356	2020-06-22	2020-08-28	1592938130	0	279
35	110329	2020-06-17	2020-10-23	1593173825	1594060735	260
36	110314	2020-06-16	2021-01-28	1592336249	0	286
37	110184	2020-06-02	2021-03-11	1592235394	0	287
38	109913	2020-05-05	2021-02-11	1592494366	0	284
39	109853	2020-04-27	2020-10-22	1598882182	1603211435	50
40	109846	2020-04-27	0	1588699017	0	328
41	109822	2020-04-21	2020-05-14	1587994166	0	336
42	109468	2020-03-12	0	1584115672	1584368360	374
43	109453	2020-03-12	2020-09-18	1584115631	1584368323	377
44	109177	2020-02-20	2021-02-26	1584381258	0	378
45	109176	2020-02-20	2020-06-05	1584381296	0	378
46	108377	2019-12-20	2020-04-29	1576875831	0	465
47	106559	2019-07-22	2020-04-15	1586437520	0	354
48	106153	2019-06-13	0	1610651328	0	74
49	105721	2019-04-29	2020-02-21	1576683119	1576683249	0
Sheet4

 

[lrobbo314]

Not sure how they are accounting for the ones with zeros for the 'Time Resumed', but here's the basic formula for finding out the number of days.

Book2
	A	B	C	D	E	F	G
1	W/O Number	Date Received	Date Shipped	Time Paused	Time Resumed	Paused Days (Total)	Days Formula
2	112748	2/4/2021	3/12/2021	1612980622	0	47
3	112747	2/3/2021	2/26/2021	1613665602	0	39
4	112594	1/21/2021	3/10/2021	1612874926	0	48
5	112513	1/15/2021	2/11/2021	1611256475	1611774202	6	6
6	112475	1/13/2021	2/4/2021	1611162817	1611233545	1	1
7	112418	1/6/2021	1/28/2021	1610630042	0	74
8	112417	1/6/2021	1/20/2021	1610456383	0	76
9	112398	1/4/2021	1/22/2021	1609938988	1610109950	2	2
10	112159	12/11/2020	0	1611930477	0	59
11	112107	12/7/2020	1/14/2021	1607440405	1610458993	35	35
12	111786	11/12/2020	0	1613998777	0	35
13	111785	11/12/2020	3/26/2021	1612371033	0	54
14	111680	11/2/2020	0	1604415707	0	146
15	111679	11/2/2020	0	1604415692	0	146
16	111664	10/28/2020	2/4/2021	1604424076	0	146
17	111550	10/22/2020	12/9/2020	1603719501	0	154
18	111549	10/22/2020	12/9/2020	1603719478	1603734274	0	0
19	111548	10/22/2020	12/11/2020	1603719447	0	154
20	111547	10/22/2020	12/9/2020	1603719420	0	154
21	111530	10/21/2020	11/26/2020	1603718721	0	154
22	111446	10/15/2020	2/24/2021	1603284994	1603477015	2	2
23	111289	9/24/2020	3/12/2021	1605011305	0	139
24	110857	8/14/2020	10/26/2020	1600787626	0	188
25	110856	8/14/2020	10/26/2020	1600787613	0	188
26	110854	8/14/2020	10/26/2020	1600787640	0	188
27	110790	8/6/2020	2/19/2021	1602602812	0	167
28	110572	7/13/2020	8/7/2020	1595598964	0	248
29	110521	7/7/2020	0	1600081920	1601315429	196	14
30	110500	7/6/2020	12/3/2020	1595426209	1597858209	226	28
31	110478	6/30/2020	10/29/2020	1603906265	0	152
32	110436	6/29/2020	1/12/2021	1600177927	1600345940	2	2
33	110357	6/22/2020	8/28/2020	1592935779	0	279
34	110356	6/22/2020	8/28/2020	1592938130	0	279
35	110329	6/17/2020	10/23/2020	1593173825	1594060735	260	10
36	110314	6/16/2020	1/28/2021	1592336249	0	286
37	110184	6/2/2020	3/11/2021	1592235394	0	287
38	109913	5/5/2020	2/11/2021	1592494366	0	284
39	109853	4/27/2020	10/22/2020	1598882182	1603211435	50	50
40	109846	4/27/2020	0	1588699017	0	328
41	109822	4/21/2020	5/14/2020	1587994166	0	336
42	109468	3/12/2020	0	1584115672	1584368360	374	3
43	109453	3/12/2020	9/18/2020	1584115631	1584368323	377	3
44	109177	2/20/2020	2/26/2021	1584381258	0	378
45	109176	2/20/2020	6/5/2020	1584381296	0	378
46	108377	12/20/2019	4/29/2020	1576875831	0	465
47	106559	7/22/2019	4/15/2020	1586437520	0	354
48	106153	6/13/2019	0	1610651328	0	74
49	105721	4/29/2019	2/21/2020	1576683119	1576683249	0	0
Sheet3

Cell Formulas
Range	Formula
G2:G49	G2	=IF(E2=0,"",ROUND((E2-D2)/(24*60*60),0))

 

[JGordon11]

Book3
	A	B	C	D	E	F	G	H	I	J
1	W/O Number	Date Received	Date Shipped	Time Paused	Time Resumed	Paused Days (Total)			report date	3/30/2021
2	112748	2/4/2021	3/12/2021	1612980622	0	47	47		base date	1/1/1970
3	112747	2/3/2021	2/26/2021	1613665602	0	39	39
4	112594	1/21/2021	3/10/2021	1612874926	0	48	48
5	112513	1/15/2021	2/11/2021	1611256475	1611774202	6	6
6	112475	1/13/2021	2/4/2021	1611162817	1611233545	1	1
7	112418	1/6/2021	1/28/2021	1610630042	0	74	74
8	112417	1/6/2021	1/20/2021	1610456383	0	76	76
9	112398	1/4/2021	1/22/2021	1609938988	1610109950	2	2
10	112159	12/11/2020	0	1611930477	0	59	59
11	112107	12/7/2020	1/14/2021	1607440405	1610458993	35	35
12	111786	11/12/2020	0	1613998777	0	35	35
13	111785	11/12/2020	3/26/2021	1612371033	0	54	54
14	111680	11/2/2020	0	1604415707	0	146	146
15	111679	11/2/2020	0	1604415692	0	146	146
16	111664	10/28/2020	2/4/2021	1604424076	0	146	146
17	111550	10/22/2020	12/9/2020	1603719501	0	154	154
18	111549	10/22/2020	12/9/2020	1603719478	1603734274	0	0
19	111548	10/22/2020	12/11/2020	1603719447	0	154	154
Sheet1

Cell Formulas
Range	Formula
G2:G19	G2	=ROUND(IF(E2=0,(($J$1-$J$2)*3600*24 - D2)/3600/24,(E2-D2)/3600/24),0)

 

[willow1985]

Book3
	A	B	C	D	E	F	G	H	I	J
1	W/O Number	Date Received	Date Shipped	Time Paused	Time Resumed	Paused Days (Total)			report date	3/30/2021
2	112748	2/4/2021	3/12/2021	1612980622	0	47	47		base date	1/1/1970
3	112747	2/3/2021	2/26/2021	1613665602	0	39	39
4	112594	1/21/2021	3/10/2021	1612874926	0	48	48
5	112513	1/15/2021	2/11/2021	1611256475	1611774202	6	6
6	112475	1/13/2021	2/4/2021	1611162817	1611233545	1	1
7	112418	1/6/2021	1/28/2021	1610630042	0	74	74
8	112417	1/6/2021	1/20/2021	1610456383	0	76	76
9	112398	1/4/2021	1/22/2021	1609938988	1610109950	2	2
10	112159	12/11/2020	0	1611930477	0	59	59
11	112107	12/7/2020	1/14/2021	1607440405	1610458993	35	35
12	111786	11/12/2020	0	1613998777	0	35	35
13	111785	11/12/2020	3/26/2021	1612371033	0	54	54
14	111680	11/2/2020	0	1604415707	0	146	146
15	111679	11/2/2020	0	1604415692	0	146	146
16	111664	10/28/2020	2/4/2021	1604424076	0	146	146
17	111550	10/22/2020	12/9/2020	1603719501	0	154	154
18	111549	10/22/2020	12/9/2020	1603719478	1603734274	0	0
19	111548	10/22/2020	12/11/2020	1603719447	0	154	154
Sheet1

Cell Formulas
Range	Formula
G2:G19	G2	=ROUND(IF(E2=0,(($J$1-$J$2)*3600*24 - D2)/3600/24,(E2-D2)/3600/24),0)

JGordon11 This is perfect! but how do I write that in a calculated column in the data module in Power BI?

I tried writing it this way but get an error:

Column = ROUND(IF('winnipeg erp_workorder'[Time Resumed]=0,((Today()-01-01-1979)*3600*24-'winnipeg erp_workorder'[Time Paused]/3600/24+1,('winnipeg erp_workorder'[Time Resumed]-'winnipeg erp_workorder'[Time Paused])/3600/24),0))

I am brand new to Power BI, thank you for your patience!

 

[JGordon11]

You need to access the report date (the date the report was run and the data table created). Once you have that you would create a conditional column essentially following the formula. Leave out the round part and do that to the new column in a second step. Without knowing where to have PQ access the report date the conditional column is not going to be able handle the 0's in the Time Resumed column.