Gaps in my Power BI Metric

[TimTruax]

I have noticed a gap in several of my Power BI reflex items, all starting in the early morning and lasting approximately five hours. My reports are monitoring sensors that record values every minute, but the current one-hour refresh rate for Power BI reports meets my needs. Can you help me identify any underlying causes that I can fix? (I have verified that the sensor data is streaming through the IoT hub to our Azure database)

[v-sgandrathi]

Hi @TimTruax,

Based on our investigation, it appears that the gaps in the Power BI semantic model, particularly during the early morning hours, are not due to missing or delayed data in the source system, but rather due to the timing of the dataset refresh.

Even though sensor data is reliably arriving in Azure SQL, the Power BI dataset may be refreshing before all data for that time period is fully written or committed. This likely explains why the gaps disappear one or more days later, the data eventually arrives, but wasn’t available during the initial refresh cycle.

The proprietary dashboard queries the database in near real-time and doesn’t seem to experience this timing mismatch, which further supports this conclusion.

As a workaround, I plan to implement a short lag (e.g., skipping the most recent 15–60 minutes of data during each refresh) to ensure, that only complete data is loaded into the Power BI model. I am also considering enabling incremental refresh not due to dataset size, but to improve refresh reliability and avoid unnecessary reprocessing.

These steps should help eliminate the temporary gaps in the Power BI visuals and better support our goal of timely and accurate event monitoring via Data Activator.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you,

Sahasra.

[TimTruax · Accepted solution]

Hi, Sahasra!

Thank you for your effort to solve this problem. If I understand you correctly, you will be making the changes to ensure that only complete data is loaded? ("...I plan to implement a short lag..."). Also, the phrase, "...I am also considering enabling incremental refresh..." leads me to believe that these are changes you can make to my semantic models. If this is correct, then I accept your solution and will make this accordingly.

Thank you, Sahasra.

[TimTruax]

Hi, @v-sgandrathi
Thank you for the follow-up. Responding to each of your suggested steps:

Verify time zones across your sensor data, database, and Power BI visuals to ensure alignment. Sensors are installed on all continents, so "alignment" is not possible. I have set refreshes to occur daily (typically, sometimes more often for critical sensors) at UTC-1 or UTC-2 to target a low activity time of day in North America.

Check Azure database ingestion logs for any delays or write latency during the affected period. Ingestion logs show nothing like a delay or latency.

Review your Power BI refresh schedule and ensure it aligns with the time your data is fully available. Data is streaming via Azure IoT Hub and data is always being written to the Azure SQL database.

If using incremental refresh, confirm that the time window includes recent data. Not currently using incremental refresh, but have models with RangeStart and RangeEnd parameters in case we get to a size/performance limitation (which we hope is a long way off).

So at this point, I do not have a solution. My primary goal is to use Data Activator to notify of sensed events in the aquatic environment, and the 'once per hour' interval for checking reflex trigger conditons is sufficient to provide the advanced warning of events of concern. The gaps in my reports are worrying for any mission-critical sensors used for process control of motors, valves, and other appurtenant devices.

[v-sgandrathi]

Hi @TimTruax,

Thank you for your detailed response and for confirming that:

Your sensors are globally distributed and aligned to UTC for ingestion;

Data is reliably streaming into your Azure SQL database via IoT Hub;

You're not seeing any ingestion latency or failure during the gap periods;

Power BI is not yet using incremental refresh.       

Even if data is present in your database, if any expected timestamps are missing in a dense time series, Power BI may show a visual break. We recommend confirming whether the exact timestamps (e.g., 1:01, 1:02 AM UTC) are returned in the dataset during the gap period.

Please double-check the Power Query and DAX model to ensure no time-based logic is unintentionally filtering out early UTC hours. This is especially important if the model was adapted from earlier time zone–specific use cases.

Consider implementing a small lag (e.g., skip the last 1–2 minutes of data) in your Power BI queries, ensuring only fully ingested data is visualised.

While not used currently, incremental refresh is not just for large datasets — it can improve refresh reliability and ensure only fresh data is queried. This can reduce timing collisions between data write and report read cycles.

We understand your goal is to ensure mission-critical sensors are fully represented in Power BI for timely alerts via Data Activator. These clarifications aim to help isolate if the issue lies in rendering logic rather than the underlying data.

If this post was helpful, please consider marking Accept as solution  and give us Kudos to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

Thank you.

[TimTruax]

The gaps in my reflex item do not existing in my propietary dashboard.

There are no time transformations except to show the local time to the end user instead of UTC time. This is done in the PQ using the M function DateTimeZone.SwitchZone and passing in the UTC date/time as the first parameter and using a local time zone offset parameter as the second argument of the function. And that works beautifully.

The semantic model has gaps in the data for the missing times of the day. 

5/17/2025 11:59:00 PM	186.57
5/18/2025 6:00:00 AM	230.23

I appreciate the support. It is my understanding that the semantic model upon which the published Power BI report is based is refreshed once per hour. The gaps disappear from the Power BI model one or more days after the gap first appears.

[v-sgandrathi]

Hi @TimTruax,

Thank you for reaching out regarding the data gaps observed in your Power BI metrics.

Based on the information provided and the screenshot, the issue appears to occur during early morning hours and may be related to time zone inconsistencies, data ingestion delays, or Power BI refresh timing.

We recommend the following actions:

Verify time zones across your sensor data, database, and Power BI visuals to ensure alignment.

Check Azure database ingestion logs for any delays or write latency during the affected period.

Review your Power BI refresh schedule and ensure it aligns with the time your data is fully available.

If using incremental refresh, confirm that the time window includes recent data.

If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!

Thank you.

[v-sgandrathi]

Hi @TimTruax,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.