Forum Discussion

yjk3140's avatar
yjk3140
Helper I
3 years ago

Calculating difference between two timestamps using DAX

Hi, I need to create a DAX measure which calculates difference in hours between two timestamp columns in a table. the date format for those columns is yyyy-mm-ddThh:mm:ss:00.000+0000 ex) 2022-05-03...
Need Help
yjk3140's avatar
yjk3140
Helper I
3 years ago

Hi Anonymous thanks for the response.

This is a screenshot of data for these two timestamp columns

and the DAX query I wrote is 

duration = DATEDIFF(table1[timestamp1],table1[timestamp2],HOUR)
 
I tried to see the created column through the table visual but I'm getting an error saying "we couldn't fold the expression to the data source. Please try a simple expression. Getting the same error when I'm trying to use it in a filter for other visuals.
 
When I run the SQL query 
select (unix_timestamp(timestamp1)-unix_timestamp(timstamp2))/3600 as hourly_diff from table1, 
then I'm getting the result that I exactly want. 

Is there any solution to get the same result through DAX query in PowerBI?

 

Thank you for your help in advance!

 
 
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi yjk3140 are you using Direct Query connection?  and what is the data source?  It appears that Power BI Data Model does not understand how to convert the DATEDIFF function into Direct Query that you data can understand.  Based on the SQL example you provided, you may have to switch to use a UNIX timefunction like this:

Hour Difference = DIVIDE ( Unixtimestamp1 - Unixtimestamp2 , 3600 )

 

  • yjk3140's avatar
    yjk3140
    Helper I
    3 years ago

    Hi Anonymous , I'm connecting Direct Query to Databricks SQL Endpoint from PowerBI..

    How can I get the Unixtimestmp using DAX? As the timestamps I have are not in the format..

     

    really appreciate your help