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!

 

[willow1985]

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.

Power BI is connected to a live database. I do not know how to access the report date you are talking about....
I am trying to do the calculation in the data model (I think that is what it is called) and not in power query now. I do apologize I am very very new to this and unfortunately I was handed this project with extremely little knowledge of power BI and little time to figure it out

 

[willow1985]

 

[JGordon11]

If you open advanced editor what is the code there you used to provide the data in the earlier posts

 

[JGordon11]

Try this in a custom column

= Table.AddColumn(#"Subtotal Calc", "Custom", each if [Time Resumed] = 0 then ((Number.From(DateTime.LocalNow())*3600*24 - Number.From(#date(1970,1,1))*3600*24) -[Time Paused])/3600/24 else ([Time Resumed] - [Time Paused])/3600/24)

Then change its type to number then round it to zero decimal places

 

[willow1985]

Try this in a custom column

= Table.AddColumn(#"Subtotal Calc", "Custom", each if [Time Resumed] = 0 then ((Number.From(DateTime.LocalNow())*3600*24 - Number.From(#date(1970,1,1))*3600*24) -[Time Paused])/3600/24 else ([Time Resumed] - [Time Paused])/3600/24)

Then change its type to number then round it to zero decimal places

This works perfect!

And this is called M language correct?

 

[JGordon11]

Yes - that's the M language in the power query part of power BI. You should be able to open the Advanced Editor and see the entire script written in M that performs your query. Probably less than about 10 lines of M code including the let and in lines. Every time you perform an action in the power query interface it writes a new line to the M code script and adds a step in the Applied Steps panel. You see the current line of code in the formula bar but you can see the entire script in the advanced editor (which is not actually very advanced, its just a text editor basically).

 

[willow1985]

Awesome! Thank you very much! I have so much to learn but am excited.
Thank you again