Forum Discussion

Tiyani's avatar
Tiyani
Helper I
4 months ago
Solved

Direct query connection not working in Incremental refresh

Hi all, I have set up an incremental refresh as seen in the image below, the data is getting refreshed as expected, my only concern is that theDirect query option is not working as expected. I am using a Premium Work Space.

 

My data updates every 5 minutes, so it must always be up to date with a maximum lagging time of 5 minutes. I always see latest stats on Oracle but not Power BI. The report refreshes every 30 minutes. But I only see latest stats after every refresh instead of seeing the live data beeing loaded inbetween refreshes. 

 

 

Need Help
  • SharmilaBrisca's avatar
    SharmilaBrisca
    4 months ago

    Hi Tiyani,

    Thank you for the detailed update . Based on what you've shared, the issue is not your configuration - it's a known limitation of how Hybrid tables behave in Power BI Service.

    Here's what's actually happening:

    The core problem — Hybrid table DirectQuery partition only covers "recent" data

    When you enable incremental refresh with the "Get latest data in real time" option, Power BI splits your table into two partitions:

    • Import partitions → historical data, refreshed on schedule
    • DirectQuery partition → covers only the most recent period (e.g., today or last N days based on your RangeStart/RangeEnd settings)

    The DirectQuery partition will only serve live data if the query being executed by the report hits that recent partition. If your visuals are aggregating or filtering across a wider date range, Power BI may be pulling from the import partitions entirely — and the DirectQuery partition never gets queried at all. This is the most common reason users see no live refresh despite correct setup.

    What to check

    1. Narrow your report filter to only the DirectQuery window — temporarily filter your report to show only today's or the last few hours' data. If the live data suddenly appears, this confirms your visuals were pulling from import partitions, not the DirectQuery one.
    2. Verify your DirectQuery partition boundary in SSMS or DAX Studio — connect to your Power BI dataset using XMLA endpoint and run:
    dax
    SELECT * FROM $System.TMSCHEMA_PARTITIONSWHERE [TableID] = <your table>

    Check the QueryDefinition of the DirectQuery partition — confirm the date boundary matches what you expect (e.g., last 1 day).

    1. Change Detection requires a dedicated Change Detection table — Change Detection does not work on your main hybrid table directly. It needs a separate, small table (like a LastUpdated timestamp table) that Power BI polls. Since your change detection table works but your main table doesn't, the polling is working — but the main table visuals aren't re-querying. This points back to point 1 above.
    2. Check Premium capacity settings — In your Premium workspace, go to Admin Portal → Capacity Settings → your capacity → Power BI workloads and verify that DirectQuery connections are not throttled. Some Premium capacities have DirectQuery query limits that silently fall back to cached data.
    3. Set Automatic Page Refresh to fixed interval (not Change Detection) first — Set APR to a fixed 5-minute interval on your report page that has the real-time visuals. Make sure the visuals on that page are only querying the recent date range. This is the most reliable way to confirm whether DirectQuery is actually firing.

    Quick test:

    Create a simple card visual showing MAX(YourDateColumn) filtered to today only, on a page with APR set to 2 minutes. Watch if it updates between scheduled refreshes. If it does — your DirectQuery is working and the issue is your other visuals querying across import partitions. If it doesn't — the DirectQuery partition boundary is the problem.

    Please refer the below links for more information

    🔗Hybrid table behavior in Power BI
    🔗Automatic Page Refresh setup
    🔗XMLA endpoint for partition inspection

    Hope this helps resolve it — let us know what the partition boundary check shows!

    Thanks and Regards
    GainInsights Solutions
    Trusted Microsoft Partner: https://gain-insights.com/partnerships/microsoft/
    www.gain-insights.com

4 Replies

  • SharmilaBrisca's avatar
    SharmilaBrisca
    Resolver I
    4 months ago

    Hi Tiyani 

    Even though you enabled “Get the latest data in real time with DirectQuery”, Power BI will NOT automatically query live data every few minutes unless:

    1. The report is actively querying that partition
    2. There’s user interaction or automatic page refresh (APR) configured
    3. The DirectQuery partition is correctly defined (based on your date column)

    Right now, your report behaves like:

    • Import mode → updates only every 30 mins (scheduled refresh)
    • DirectQuery part → not being triggered


    Please Try to Use Automatic Page Refresh Option. Attaching the link for your reference
    https://learn.microsoft.com/en-gb/power-bi/create-reports/desktop-automatic-page-refresh
    Also Please find the attached link to 
    https://learn.microsoft.com/en-gb/power-bi/connect-data/incremental-refresh-overview

    Hope i answered your question

    Thanks and Regards
    GainInsights Solutions
    Trusted Microsoft Partner: https://gain-insights.com/partnerships/microsoft/
    www.gain-insights.com

  • Tiyani's avatar
    Tiyani
    Helper I
    4 months ago

    SharmilaBrisca 

    Power BI will NOT automatically query live data every few minutes unless:

    1. The report is actively querying that partition
    2. There’s user interaction or automatic page refresh (APR) configured
    3. The DirectQuery partition is correctly defined (based on your date column)

    I am confident that the direct query is correctly defined which can be seen in the image I shared on main post.

    I tried reading through the matrials you shared but I still couldn't get it working.

     

    I also tried Change detection which also doesnt work on my table, only the direct query table that I added for testing. Even when I try to interact with the data its not working. My table is import on Desktop(With incremental refresh) and appears as Hybrid on Service.

     

    This screenshot(Service) below shows my Incremental table time stamp at the top and direct query table below, both timestamps are the same on Oracle DB but the incremental refresh always lags and only updates with scheduled refresh.

     

    • SharmilaBrisca's avatar
      SharmilaBrisca
      Resolver I
      4 months ago

      Hi Tiyani,

      Thank you for the detailed update . Based on what you've shared, the issue is not your configuration - it's a known limitation of how Hybrid tables behave in Power BI Service.

      Here's what's actually happening:

      The core problem — Hybrid table DirectQuery partition only covers "recent" data

      When you enable incremental refresh with the "Get latest data in real time" option, Power BI splits your table into two partitions:

      • Import partitions → historical data, refreshed on schedule
      • DirectQuery partition → covers only the most recent period (e.g., today or last N days based on your RangeStart/RangeEnd settings)

      The DirectQuery partition will only serve live data if the query being executed by the report hits that recent partition. If your visuals are aggregating or filtering across a wider date range, Power BI may be pulling from the import partitions entirely — and the DirectQuery partition never gets queried at all. This is the most common reason users see no live refresh despite correct setup.

      What to check

      1. Narrow your report filter to only the DirectQuery window — temporarily filter your report to show only today's or the last few hours' data. If the live data suddenly appears, this confirms your visuals were pulling from import partitions, not the DirectQuery one.
      2. Verify your DirectQuery partition boundary in SSMS or DAX Studio — connect to your Power BI dataset using XMLA endpoint and run:
      dax
      SELECT * FROM $System.TMSCHEMA_PARTITIONSWHERE [TableID] = <your table>

      Check the QueryDefinition of the DirectQuery partition — confirm the date boundary matches what you expect (e.g., last 1 day).

      1. Change Detection requires a dedicated Change Detection table — Change Detection does not work on your main hybrid table directly. It needs a separate, small table (like a LastUpdated timestamp table) that Power BI polls. Since your change detection table works but your main table doesn't, the polling is working — but the main table visuals aren't re-querying. This points back to point 1 above.
      2. Check Premium capacity settings — In your Premium workspace, go to Admin Portal → Capacity Settings → your capacity → Power BI workloads and verify that DirectQuery connections are not throttled. Some Premium capacities have DirectQuery query limits that silently fall back to cached data.
      3. Set Automatic Page Refresh to fixed interval (not Change Detection) first — Set APR to a fixed 5-minute interval on your report page that has the real-time visuals. Make sure the visuals on that page are only querying the recent date range. This is the most reliable way to confirm whether DirectQuery is actually firing.

      Quick test:

      Create a simple card visual showing MAX(YourDateColumn) filtered to today only, on a page with APR set to 2 minutes. Watch if it updates between scheduled refreshes. If it does — your DirectQuery is working and the issue is your other visuals querying across import partitions. If it doesn't — the DirectQuery partition boundary is the problem.

      Please refer the below links for more information

      🔗Hybrid table behavior in Power BI
      🔗Automatic Page Refresh setup
      🔗XMLA endpoint for partition inspection

      Hope this helps resolve it — let us know what the partition boundary check shows!

      Thanks and Regards
      GainInsights Solutions
      Trusted Microsoft Partner: https://gain-insights.com/partnerships/microsoft/
      www.gain-insights.com

      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        4 months ago

        Hi Tiyani 

        Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.