DATEDIFF column not updating when dashboard refreshes

[Daveed1973]

I have a Power BI dashboard which conects to a SQL database and contains a date field. I have a calculated column that works out the difference in minutes between the current time and the SLA Deadline date/time...

Mins = DATEDIFF(NOW(), pbi_UCLDepartures[SLA Deadline], minute)

The dashboard itself connects via Direct Query and the browser forces a refresh every 5 minutes. In my mind the Mins column should be updated everytime the browser forces a refresh however it doesn't. The mins column only seems to update everytime the underlying data has a change and the last refreshed time in the dashboard is updated.

I'm trying to create a minutes remaining column that updates everytime the browser forces a refresh but because the Mins field only updates when the underlying data that connects via the direct query updates I cannot rely on this as a countdown.

Anyone got any ideas how to get around this?

[Daveed1973]

It seems that if I manually run a dashboard tile refresh from within the dashboard the minutes remaining column updates. So, is there a way of forcing a dashboard tile refresh without the user having to do this manually. The dashboard itself is intended to be on a big screen and just refresh without any interaction from the users.

[v-shex-msft]

Hi @Daveed1973,

Currently, desktop not contain the auto refresh visual feature, you can post this requirement to ideas.

In addition, you can upload this report to service and the setting the scheduled refresh.

Reference link:

Data refresh in Power BI

Regards,

Xiaoxin Sheng

[Daveed1973]

Hi, thanks for your reply.

Just to confirm, the Power BI dashboard was created in Power BI Desktop but runs in a browser which is set to refresh the page every 5 minutes. However, the minutes remaining column only seems to update when the underlying data is updated and the last refresh time is updated.

[v-shex-msft]

Hi @Daveed1973,

Pls take a look at below table which about power bi refresh, the minimum refresh interval is hourly. If you set the refresh interval less than one hour, it will still refreshed per hour.

Data Refresh	Power BI (free)	Power BI Pro
Datasets scheduled to refresh	Daily	Hourly*
Streaming data in your dashboards and reports using Microsoft Power BI REST API or Microsoft Stream Analytics.	10k rows/hour	1M rows/hour
Live/DirectQuery on-premises data sources with full interactivity via the On-premises Data Gateway
Live cloud data sources with full interactivity (Azure SQL Database, Azure SQL Data Warehouse, Spark on HDInsight)
On-premises data sources requiring a Power BI Gateway - Personal and/or the On-premises Data Gateway.

* Up to eight times (hours) per day.

BTW, if you replace the dataset of the dashboard, it will update the current data at once, then refreshed by setting.

Regards,

Xiaoxin Sheng

[Daveed1973]

So does the hourly data refresh rule apply even though we are connecting directly to the data using direct query?

[v-shex-msft]

Hi @Daveed1973,

In direct query mode, it refresh more quick than scheduled refresh and not contain the "eight times" limit.

For refresh speed, it is according to your data size. (default: 15 minutes)

any direct query report will refresh every 15mins or whenever you interact with the dashboard / reports. The data should be updating then as well.

Regards,

Xiaoxin Sheng