I have a real world experience building TTFs. I wrote up a white paper on the process. Enjoy, I hope this helps.
Translytical Task Flows in Microsoft Fabric:
Architecture, Patterns, and Pitfalls
Field-tested insights from a production write-back implementation
By Timothy Gallagher
linkedin.com/in/tpg0220
A practical guide for Power BI developers evaluating translytical task flows or seeking native SQL write-back, drawn from a production rollout at a Fortune 500 beverage company. Version 1.0 • Living document
Most Power BI reports are read-only by design: they pull from an analytical store and never write back. That separation is clean until the business needs the report to capture decisions, not just display them. The usual answer bolts a separate data-entry app onto the side, and the data then crawls back to the report through a chain of intermediate systems and a scheduled refresh. The result is slow, brittle, and opaque to the people using it.
A translytical task flow (TTF) removes the chain. A button inside the report invokes a function that writes directly to a database, and the report reads the result back in seconds. This paper describes how one team replaced a round-trip data-entry relay with a single-click native write-back in Microsoft Fabric, the architecture and design patterns that made it work, the platform constraints that shaped it, and the pitfalls that cost real time to find. The goal is to let the next team start ahead of where this one did.
The legacy pattern for capturing decisions in a Power BI context tends to look the same everywhere it appears. To the user it looks like a single tool: they open the report, where a low-code data-entry control has been embedded directly into the page. Behind that surface, the decision takes a long road. The embedded control writes the value into a document-library list, a dataflow then ingests that list, the dataflow feeds a semantic model, and only on the model's next scheduled refresh does the decision reappear in the report the user was looking at the whole time.
That architecture has a specific weak point: writing straight into a document-library list as the transactional target. A list of that kind is a forgiving document store, not a database, so a write can appear to succeed and still fail or land inconsistently, with no transaction to guarantee it. Layer the ingestion lag on top, where the reviewer who records a decision in the morning cannot see it reflected until the dataflow runs and the model refreshes, and every hop becomes a place a value can get stuck with nothing in the chain able to confirm to the user that the write actually landed.
Three costs compounded. Latency: the gap between action and visibility was hours to a day, so the report could never be trusted as a live picture. Fragility: every hop meant another integration point, another schema to keep aligned, and another way for a silent failure to hide. Opacity: because users could not see their own writes, they re-entered work, second-guessed the tool, and filed support tickets that amounted to "did my save work?" The maintenance burden of keeping the relay glued together quietly consumed more engineering time than the reporting itself.
The business did not want a faster relay. It wanted the relay gone.
"Translytical" fuses transactional and analytical. A translytical task flow adds a write path to an otherwise analytical report: a visual on the report invokes a function that performs a transactional write against a database, and the same report reads the result back. One surface both analyzes and mutates the data.
In Microsoft Fabric the pattern assembles from four parts. A Power BI report supplies button visuals whose action type is a data function. Fabric User Data Functions (UDFs) are small, sandboxed Python workers that hold a database connection and run parameterized SQL. A Fabric SQL Database serves as the system of record for the write-back. And DAX measures assemble the function's parameters from the current selection and read the live results back into the report.
The division of labor is the whole idea: the report gathers intent — which rows, which action, who is acting — the function enforces the rules and writes, and the database persists. Everything downstream is detail hanging off that spine.
The production design holds two representations of the write-back table in the model on purpose, and conflating them is the single most expensive mistake available in this pattern. An import copy backs the heavy matrix visual; it is a periodic snapshot and does not reflect a live write until the next dataset refresh. A DirectQuery copy backs the live counters; it reads the database in real time and shows a write within seconds. Every measure that must reflect "what just happened" reads the DirectQuery table; everything that can tolerate a snapshot reads the import.
Power BI Report
|-- Import : facts, dimensions, the matrix row spine (snapshot)
|-- DirectQuery : write-back table, live read for the counters |-- Buttons : Action / Reverse / Clear (action type = Data function) v Fabric User Data Functions (one connection alias)
+-- set_state_batch(keys, action, note, userPrincipal) +-- set_rule(group, year, min, max, active, userPrincipal) [separate item] v Fabric SQL Database
+-- dbo.StateTable (one row per entity-period)
+-- dbo.RuleTable (thresholds for the automated pass)
^ one-time seed / migration via Dataflow Gen2 from legacy lists
Python notebook --> applies rules, stamps the automated flag (idempotent)
The buttons pass the current selection and the acting user into the UDF; the UDF writes to the SQL database; the DirectQuery table reflects it. A separate nightly Python notebook applies threshold rules and stamps an automated-decision flag that the write-back function respects. A one-time Dataflow Gen2 seeds the tables from the legacy lists during cutover.
Back heavy visuals with an import snapshot for performance, and back anything that must be live with a DirectQuery table over the same write-back database. Route every live measure to the DirectQuery table from the first day. The most common early symptom of getting this wrong is live counters that read blank or stale while the data and logic are both fine — the measures were simply pointed at the snapshot.
Give the write-back function an action parameter so a single function serves several buttons. A toggle is the wrong primitive for a multi-row action because the result depends on each row's prior state. Instead, set the whole selection to the target state, so a multi-row click lands the entire selection deterministically. Provide an explicit Clear (return to baseline) as the recovery for an accidental action; do not overload the opposite action to serve as undo, because the opposite action is its own deliberate state.
def set_state_batch(sqlDB, keys, action, note, userPrincipal): # action in {"Apply", "Reverse", "Clear"} -- validated against a fixed list
# split keys "period-id;period-id;..." into pairs
# MERGE ... WITH (HOLDLOCK), guarding the automated-flag on status changes
# populate the single join key on every INSERT path
# stamp UPN + UTC time; return a human-readable summary string
Power BI cannot form a relationship on a two-column pair. If the natural key is composite (an identifier plus a period, say), synthesize a single concatenated key column and populate it on every insert path in the function. A write path that leaves the join key null produces a row the database holds but the report cannot see — a bug that hides until someone notices a first-time entry never appears in the matrix.
The live counters need no person slicer. Each measure filters on the current user's principal name, and the write-back function stamps that same principal on every row it writes. Because the write side and the read side reference the same identity, they always line up, and every viewer sees only their own activity. One report personalizes itself for the entire audience with no per-user setup.
User Principal = USERPRINCIPALNAME()
My Actions Today =
VAR _u = USERPRINCIPALNAME ()
VAR _d = UTCTODAY ()
RETURN
CALCULATE ( COUNTROWS ( 'StateTable-DQ' ),
'StateTable-DQ'[Status] = 1,
'StateTable-DQ'[ActedBy] = _u,
'StateTable-DQ'[ActedAt] >= _d && 'StateTable-DQ'[ActedAt] < _d + 1 ) + 0
Two details earn their keep. The identity functions are measure-only; building one as a calculated column or table throws an error. And a row count returns blank, not zero, for an empty result, so the trailing + 0 makes a genuine zero render as 0 on the card instead of reading as broken.
Two kinds of enforcement are tempting to handle in the report and wrong to leave there. The first is a decision lock: rows decided by the automated pass should resist manual change. Enforce it inside the function — a pre-read that returns a clear error, plus a guard condition on the write itself so the lock holds even under concurrent clicks. The second is access control: hiding the navigation to an admin page is convenience, not security, because anyone with build permission on the dataset can still invoke the function directly. The real gate is an allowlist of permitted principals inside the function. Hidden buttons are user experience; the function is the boundary.
A slider or numeric input cannot transmit null. To express "no value," send an agreed sentinel and translate it to SQL NULL inside the function. This is not pedantry: a "default blank to zero" fallback once turned a rule's intentional "no upper limit" into a zero ceiling, silently inverting its meaning. Choose a sentinel, translate it in one place, and never let an empty input mean zero by accident.
When part of a report is a live DirectQuery surface and part is a periodic import snapshot, users will assume everything on screen is current unless told otherwise. A visible refresh descriptor — stating when the snapshot last refreshed and which surfaces are live — prevents an entire class of "the numbers are wrong" misreads that are really just "the snapshot has not refreshed yet." Set the expectation on the page, not in a hallway.
The write-back target matters, and Fabric offers more than one SQL-shaped item. A native Fabric SQL Database behaves much like Azure SQL: it supports enforced primary keys, DEFAULT constraints, and the full string types. A Fabric Warehouse does not — and a warehouse can be created under a name that reads like a database. Identify the item by its GUID and confirm the engine before writing any DDL. A warehouse rejects DEFAULT in CREATE TABLE and inline primary keys, treats every key constraint as a non-enforced optimizer hint, and is inconsistent with the wide string type. Discovering this after the schema is written is an expensive way to learn it.
Two governance realities also shape the build. First, a tenant policy may block user-initiated DDL outright, independent of any workspace role. The workaround that reliably unblocks provisioning is to create tables through a Dataflow Gen2, which writes under a service identity the policy does not gate. That is fine for seeding, but a Replace-mode dataflow drops and recreates its destination, so it must never point at a live write-back table; at cutover, seed in Append mode after clearing test rows.
Second, a Fabric SQL Database exposes two endpoints on the same database: a live transactional endpoint and a read-only analytics mirror that trails it by seconds to minutes. Write-back writes to the transactional endpoint. If the live report reads the mirror, a freshly saved record reads as missing for a beat — exactly the support ticket the whole design set out to eliminate. The database name is reconstructable from its display name and item ID, but the server address is per-database and must be copied from the portal; the test server will not work in production.
These are the traps that produced silent failures or vanished hours. None of them announce themselves.
- A function edit is not live until you publish it. The old published version keeps running while you debug the new one, so you can spend an afternoon chasing a bug in code that is not executing. Publish, then test, after every change.
- Duplicate or orphaned items shadow the real one. A second copy of a function item can absorb your edits while the live item runs untouched. Confirm by GUID that you are editing the item the report actually calls, and remove the ghost.
- A sandboxed function cannot trigger a notebook. The function runs without an inherited auth token, so it cannot fire a notebook through the job-scheduler API. Trigger the automated pass another way: a schedule, an external service principal, or a separate automation step. Do not design a button that assumes it can run a notebook synchronously.
- A disconnected slicer filters nothing on its own. The slicer is only half the mechanism; a status-filter measure must be added as a visual-level filter for the slicer to steer the visual. Without that step the slicer looks broken when it is merely unwired.
- You cannot hide a button with a measure. There is no hidden-by-expression property. Conditionally hide a button by returning an empty string for its text, a transparent fill, and a destination that returns blank so the click is inert for the wrong audience.
- Conditional navigation must match the target page name exactly. One stray character and the button silently does nothing.
- Live-connected reports lag the model's metadata. A new measure does not appear in a live-connected report until it re-reads metadata. It looks unsaved when it is not; refresh metadata before concluding anything broke.
- Text-versus-number comparisons fail silently in Power Query. A value stored as the string "1" does not equal the number 1, so a filter returns nothing without erroring. Coerce types explicitly before comparing, and force identifier keys to text to preserve leading zeros across a merge.
- Row-level dedupe is not key-level dedupe. A distinct operation that compares whole rows leaves near-duplicates that differ in one column, which then fan out a downstream join. Sort, buffer to lock the order, then dedupe on the key columns only.
- Schema parity between environments is a promotion checklist item. A source column renamed between test and production breaks name-based, case-sensitive query logic. Confirm column names match before promoting a dataflow.
The write-back path scales well because each click is a bounded, parameterized database operation rather than a refresh of anything. Three considerations keep it healthy at volume.
Bound the batch. A multi-row action should cap the number of rows per invocation (a few hundred is a sensible ceiling) so no single click issues an unbounded write. Users rarely need more in one action, and the cap protects the database from an accidental select-all.
Make writes concurrency-safe. Multiple reviewers act at once. An upsert that takes a hold lock on the merge target keeps concurrent writes to the same row race-safe, and an idempotent automated pass — one that does not duplicate rows or flip already-correct flags on re-run — keeps the nightly job safe to repeat.
Spend DirectQuery deliberately. DirectQuery is what makes the experience feel instant, but every live visual is a query against the database. Keep the live surface small and focused — a handful of personalized counters — and let the heavy, wide visuals run on the import snapshot. The split keeps the database load proportional to genuine interactivity rather than to every rendered cell.
TTFs fit a specific shape of problem: structured decisions captured in the analytical context where they are made, by users who are already living in the report, with a need to see the result immediately. Validation and review workflows, lightweight reference- or rule-table administration, status and disposition capture, and approval gating all fit well. The defining trait is that the act of deciding and the act of analyzing are the same motion.
They are not the right tool for everything. A rich, multi-step data-entry experience with complex client-side validation is still better served by a purpose-built application. High-volume operational transaction processing belongs in an operational system, not behind a report button. Free-form document or content management is out of scope. And if the consumers genuinely never need to see their write reflected live, a simpler scheduled pipeline may be enough.
Weighed against the alternatives, the TTF earns its place by collapsing the systems count. A bolt-on low-code app reintroduces the very relay this pattern removes. A separate database front-end fractures the user experience across two tools. The TTF keeps analysis and action in one surface, on native platform components, with the database as the single system of record — which is precisely what the legacy relay could never offer.
Replacing a round-trip data-entry relay with a single-click native write-back removed hours of latency, eliminated a class of silent integration failures, and gave users the one thing the old design never could: immediate proof that their work registered. The patterns that made it durable were not exotic — separate the live read from the snapshot, set the state rather than toggle it, relate on one key, personalize with the caller's identity, and put the real rules inside the function. The constraints that shaped it were mostly about knowing the platform: confirm the engine, identify items by GUID, respect the governance gates, and match the live endpoint.
None of the costly lessons here were in the documentation; all of them were learnable only by shipping. Written down, they turn a multi-week first attempt into a much shorter second one. That is the entire purpose of this paper.
This is a living document intended to improve with each implementation. Patterns, counter-examples, and corrections from other production rollouts are welcome.
Microsoft Employee