Translytical writeback not updating data real time

Question

I have a setup where a UDF accepts input values from Power BI report users and updates a table in a Fabric SQL database. Several DAX measures then use this updated data to calculate metrics that are displayed in the report. The report includes input slicers and a button that triggers the UDF execution.

The issue I’m facing is latency—after triggering the function, it takes about a minute for the updated data to reflect in the report. I was expecting the update to happen almost instantly, since the connection is DirectQuery.

The semantic model connected to this report uses a mixed storage mode: many tables are in Import mode, while a few are in DirectQuery mode. Is this kind of latency expected in such a setup? Are there any workarounds or design patterns to achieve near real-time updates for the data and dependent DAX measures?

Answer 1

Fabric is not supported in Microsoft Q&A, but questions about Fabric are better asked on https://community.fabric.microsoft.com/.

Answer 2

Yes, this latency is expected with the current design, and it’s primarily due to the mix of Import and DirectQuery plus Power BI’s caching behavior, not the UDF or Fabric SQL itself.

Key points from the documented behavior:

1. DirectQuery doesn’t guarantee instant visual updates
   Even with DirectQuery, visuals only show new data when they re‑query the source. Power BI also caches query results “to improve performance and reduce back-end load,” which means the latest changes might not be reflected until something forces a requery.
2. Hybrid / mixed models introduce additional latency
   When using hybrid or composite models (Import + DirectQuery), only the DirectQuery partitions/tables are truly “live.” Any measures that depend on Import tables will only see changes when the Import portion is refreshed. Hybrid tables with incremental refresh and real-time DirectQuery still rely on:
   • DirectQuery partitions for the latest slice of data.
   • Cached/Import partitions for historical data.
   If relationships or measures cross between Import and DirectQuery tables, the effective “freshness” is constrained by the Import side.
3. Power BI caches query results for DirectQuery
   The documentation explicitly states that Power BI caches query results even for DirectQuery, which can cause visuals not to show the latest data until a refresh mechanism kicks in.
4. Recommended pattern for near real-time is DirectQuery + automatic page refresh
   For self-service near real-time analytics, the recommended approach is:
   • Use a DirectQuery semantic model for the data that must be real-time.
   • Configure automatic page refresh so that visuals re-query the source at a fixed interval (for example, every few seconds) or via change detection (Premium capacity only).
   This is the documented way to keep visuals in sync with frequently changing data.
5. Translytical task flows are designed for immediate analytics, but visuals still follow Power BI refresh rules
   Translytical task flows and user data functions write back to Fabric SQL / warehouses / lakehouses, and the SQL analytics endpoint maintains a near real-time replica in OneLake so that “updates written by task flows are available for immediate analytics and Power BI visuals without additional ETL.”
   However, “available for analytics” means the data is queryable; it does not bypass Power BI’s visual caching and refresh behavior. Visuals still need to re-query the model.

Practical design patterns and workarounds for near real-time:

1. Move the writeback-dependent logic to a fully DirectQuery path
   For any measures that must react immediately to user input via UDF:
   • Store the writeback data in a table that is exposed to Power BI in DirectQuery mode.
   • Ensure that the DAX measures used in the visuals depend only on DirectQuery tables (or on hybrid tables where the relevant partition is DirectQuery) and avoid crossing into Import tables for those specific calculations.
2. Enable automatic page refresh on the report page
   Configure automatic page refresh in the Power BI service:
   • Use a short fixed interval (for example, a few seconds) for the page that shows writeback-dependent metrics.
   • Or, if in Premium capacity, use change detection with a measure that reflects changes in the writeback table, so visuals refresh only when data changes.
   This directly addresses the documented issue: “Report visuals don’t show the latest data” because Power BI caches DirectQuery results.
3. Minimize Import dependencies for real-time scenarios
   For the subset of the model that must be near real-time:
   • Prefer DirectQuery (or hybrid with a DirectQuery latest partition) for those tables.
   • Keep Import tables for historical/slow-changing data that doesn’t need instant updates.
   • Consider splitting the solution into two semantic models if necessary: one optimized for real-time writeback scenarios (DirectQuery-focused) and another for heavy historical analytics (Import-focused).
4. Use hybrid tables and Dual mode correctly if incremental refresh is involved
   If using incremental refresh with real-time data (hybrid tables):
   • Ensure related tables are set to Dual storage mode so they can behave as cached or DirectQuery as needed. This reduces limited relationships and improves query efficiency for the DirectQuery partition.
5. Accept “near” real-time, not instantaneous
   Even with all optimizations, the documented guidance frames this as near real-time. There will always be some latency due to:
   • UDF execution and commit to Fabric SQL.
   • Propagation to the SQL analytics endpoint.
   • Power BI visual refresh interval and query execution time.

Summary of recommended changes for this scenario:

1. Ensure the writeback table used by the UDF is surfaced to Power BI in DirectQuery (or the DirectQuery partition of a hybrid table).
2. Refactor the DAX measures that must be real-time so they depend only on DirectQuery/Hybrid tables, not Import tables.
3. Turn on automatic page refresh (or change detection in Premium) for the page that shows these measures, with a short interval.
4. Use Dual mode for related tables if hybrid/incremental refresh is in play.

These patterns align with the documented behavior of DirectQuery, hybrid tables, and translytical task flows and are the supported way to achieve near real-time updates.

---

References:

• Troubleshoot incremental refresh and real-time data
• DirectQuery in Power BI
• Power BI usage scenarios: Self-service real-time analytics
• Use SQL database as the source for translytical applications
• Understand translytical task flows
• Power BI modeling guidance for Power Platform