Seconds to Duration in Direct Query

[allen3just]

Hello all,

I have a column with seconds. I created another column to convert it to duration. Whenever I create that column and do the conversion, it tells me that this step results in a query that is not supported in DirectQuery mode. Is there a way for me to do this conversion while still in DirectQuery mode? Thanks. 

[Greg_Deckler]

Did you create that column in Power Query M code or in DAX?

[allen3just]

I made the column in power query M code

[Greg_Deckler · Accepted solution]

You might try using a DAX column instead. https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

[allen3just]

Okay, it is working perfectly. One other question. How would I go about getting the sum and average of the dax column. It has told me that it is a string but when i convert it to datetime it still tells me that sum is not supported in direct query. Any suggestions? 

[Greg_Deckler]

Ah yes, that is a downside. What about this update that I wrote a while ago:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/td-p/793639

?

[allen3just]

It worked, thank you for all your help! Sorry to continue this thread but I have one last question. So I've already done some filtering in power query for that data set. I used that measure but found out that it takes into account all the data before the filtering done in power query. Are dax and power query independent of each other as in I would also need to filter out in dax the way I made filters in power query? Once again thanks for all your help. 

[Greg_Deckler]

Yes, Power Query (M code) is executed as data is loaded into the model. DAX is essentially executed after the data is loaded into the model. They are definitely independent of one another.