How to Create an Refresh Failed message in the Text Box in Power BI Desktop

[Lexixl]

Hi

I have a report connect to the Azure Sql Database. And I schudle the refresh time in Power BI Service. However, sometimes the scheduled refresh failed and the report can't be refreshed to the most recent data.

For now, our customer wants to see an error message like 'the data is not to date' in the report when the refresh failed. But when the report refresh successfully, this message will be hidden. 

I'm trying to create the refresh time table depends on the local time and want to add an column to show whether the local time is in the refresh time span (From 8:30 am to 12:30 pm) as below: 

But it gives me an error and I'm not sure it's a good way to solve this issue.

Is it possible to do as my customer expect?

Thank you so much for your help.

[TomMartens · Accepted solution]

Hey @Lexixl , from the screenshot you provided it seems you are using DAX function and syntax to calculate the column you want inside Power Query. Either you change to M functions / M syntax (M is the language you can use together with Power Query, or you use DAX to create a calculated column or measure.

Nevertheless, I tend to use a mixture. I use Power Query (M) to create a table that just contains a single row and a single column.

This is the M code to create this table:

let
    Source = Table.FromRecords({[RefreshDateTime = DateTime.LocalNow()]}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RefreshDateTime", type datetime}})
in
    #"Changed Type"

Just Copy/Paste the code to a blank query using the Advanced Editor inside Power Query.

Whenever the data will be refreshed successfully this table contains a datetime value that corresponds to the latest refresh. Please be aware that I name the table "RefreshDateTime"

Then you can use DAX code to create a measure similar to this:

CurrentOrNot = 
var LatestRefreshDateTime = MAX( 'RefreshDateTime'[RefreshDateTime] )
var CurrentDateTime = NOW()
var Difference = DATEDIFF( LatestRefreshDateTime , CurrentDateTime , MINUTE )
return
IF( Difference < 60 , "current" , "not current")

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom

[AllisonKennedy]

@Lexixl I'm a bit confused on what your date table date is representing? Would it be sufficient to calculate the last Refresh time and compare that to current time? You will need to use DAX for this to calculate UTCNOW and compare that to the refresh time in UTC. Display Last Refreshed Date in Power BI - The Excelguru BlogThe Excelguru Blog

Then use DAX to create the error message, or even just display the last refresh time. Would that work?