cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

Datediff function seconds and minutes

Hi, 

 

When I calculate a datediff in hours I haven't any problem:

*Countdown (hours) = IF(ISBLANK(RELATED(mysql_mail_log[created])),DATEDIFF(mysql_reports[*NU],mysql_reports[first_report_expected],HOUR))
svanwetswinkel_0-1651781854227.png

 

When I try to calculate the datediff in minutes or seconds, I receive the mesage 'Can't display the visual -> OLE DB or EDBC error: [Expression.Error] We couldn't fold the expression to the data source. 
 
*Countdown (minutes) = IF(ISBLANK(RELATED(mysql_mail_log[created])),DATEDIFF(mysql_reports[*NU],mysql_reports[first_report_expected],MINUTE))
 
svanwetswinkel_1-1651781943368.png

 

 Anyone knows what is wrong?
 
I'm using SQL direct query data. 
 

 

6 REPLIES 6
tamerj1
Super User
Super User

@Anonymous 

Strange that the HOUR works! Creating time related calculated columns is not allowed in direct query. You have to create measures only

Anonymous
Not applicable

how I calculate 'hh:mm' or decimal hours with only measurement and without column? I want a value for every row without a calculation like sum, average, ...

Hi @Anonymous 
This might require further formatting

Countdown (minutes) =
VAR TimeDifference =
    SUMX (
        mysql_reports,
        IF (
            ISBLANK ( RELATED ( mysql_mail_log[created] ) ),
            DATEDIFF ( mysql_reports[*NU], mysql_reports[first_report_expected], MINUTE )
        )
    )
RETURN
    QUOTIENT ( TimeDifference, 60 ) & ":"
        & MOD ( TimeDifference, 60 )

Would be great if you can provide sample file

Anonymous
Not applicable

I don't succeed 😞

Is it possible to mail you the file? 

I hope you can give me the solution. 

@Anonymous 
I sent my email in a private message

@Anonymous 
Even more simple is just to substract and select "Short Time" data type manually.

Countdown (minutes) =
SUMX (
    mysql_reports,
    IF (
        ISBLANK ( RELATED ( mysql_mail_log[created] ) ),
        mysql_reports[first_report_expected] - mysql_reports[*NU]
    )
)

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors