What I currently have:
A power BI report linked to an Azure SQL database.
SQL database is updated daily but time of update can vary significantly. (SQL DB includes a log table to inform users when it has been updated).
Power BI report is scheduled to refresh at multiple times throughout the day, this refresh will be successful regardless of whether the SQL DB has been updated or not.
Report users are set up with a subscription which triggers late in the day to ensure time for DB and report refresh.
What I would like to have:
Essentially I want to send an email to report users once the daily DB refresh has passed through to the Power BI report. So I want my report to only refresh when the DB has been refreshed so that I can set my subscription preference to "After data refresh (once daily)".
Possible solutions:
I have thought of a few different ways I might be able to get to the desired state but have not been able to implement any of them - would appreciate some advice on this if anyone has some ideas. My current train of thought is to deliberately cause a Power BI refresh failure if database has not refreshed: Get SQL code to reference log table and force error - I have not been able to get this to work, it's possibe my SQL skills are not up to scratch.
