From Demo to Reusable Framework: Translytical Write-Back in Power BI

Co-Authors — Praful Potphode & Natarajan Manivasagan

Most Power BI write-back demos look simple. Select a row, enter a comment, click a button and watch the data appear in a database.

My first version worked like that too. The difficult part came later, when I tried to make the same experience work across a real semantic model with several metrics, time levels, dimensions and filter combinations. At that point, the button was no longer the challenge. The challenge was making sure every comment was saved against the right business context.

I wanted the solution to do three things well: identify the selected analytical grain, keep every change auditable and avoid hard-coding the report’s dimensions into the write-back service. That led to the reusable pattern described in this article.

The solution brings together Power BI, a translytical task flow, Microsoft Fabric User Data Functions and a SQL-based data store. Power BI provides the analytical context. The task flow captures the user’s action. The function validates and saves it. SQL keeps the history and makes the latest state available to the report.

What does translytical mean?

Translytical combines transactional and analytical experiences.

In a traditional BI report, users analyze information and then leave the report to take action. They might send an email, update a spreadsheet, open a ticket or enter the same information in another application. The analytical context that led to the decision is often copied manually or lost altogether.

A translytical flow lets the user act from within the report. In Power BI, a translytical task flow can collect an input and call a Fabric User Data Function. The report context, such as the selected metric, period, company, region, customer or product, travels with that action.

For this commentary pattern, the flow is:

1. The user selects one analytical cell in Power BI.

2. DAX converts the current context into a deterministic grain key and checks whether the selection is safe to write.

3. The task flow collects a comment, status, approval decision or other input.

4. The task flow passes the input and its context to a Fabric User Data Function.

5. The function validates the request and writes a new version to SQL.

6. Power BI reads the latest state back into the report.

The important difference is that the action remains attached to the context that produced it. Microsoft explains the Power BI capability in its translytical task flow documentation.

Press enter or click to view image in full size

What is a Fabric User Data Function?

Here, UDF means Fabric User Data Function, not a traditional scalar function inside a SQL database.

A Fabric UDF is reusable server-side Python code hosted in Microsoft Fabric. A Power BI task flow can call it without exposing database credentials, connection strings or transaction logic in the report.

In this solution, the UDF is the secure boundary between Power BI and the database. It receives a small payload, validates the required fields, records the user’s identity, opens the configured connection, applies the versioning rules, executes the transaction and returns a simple response to Power BI.

This separation has practical benefits:

• Security: credentials and secrets stay on the server.

• Consistency: every report follows the same validation and persistence rules.

• Reuse: the same function can support different reports, metrics and business grains.

• Auditability: every action can be linked to a user, timestamp and version.

• Maintainability: connection or SQL changes can be handled without redesigning the report.

I keep one rule in mind: Power BI decides what the user selected; the UDF decides whether and how the transaction is saved. This keeps database logic out of DAX and report buttons. The Fabric User Data Functions overview provides more detail about the service.

Where can this pattern be used?

Commentary is a useful starting point, but the same design works for many small, intentional actions taken during analysis:

• Variance explanations: record why revenue, cost, margin, headcount or another KPI changed.

• Planning and forecasting: submit adjustments, assumptions, overrides or scenario inputs.

• Approvals and attestations: approve, reject, certify or sign off a period, account, exception or business unit.

• Exception management: assign an owner, priority, status, due date or remediation note to an outlier.

• Data stewardship: propose a corrected classification, mapping or master-data attribute.

• Operational updates: record progress, risks, blockers and next steps against a project, location, supplier or customer.

• AI feedback: accept, reject, rate or annotate an AI-generated insight and retain the feedback with its context.

UDFs can also support validation, lookups, enrichment, lightweight orchestration and API integration. They are not a replacement for data pipelines or bulk transformation. They are best suited to focused transactions initiated by a user or application.

The central idea: give every comment a stable identity

Every saved comment must answer one question: which analytical cell does this comment belong to?

In this pattern, the identity comes from the selected source, time level, time value, metric and active business dimensions. DAX normalizes those values and combines them into a deterministic grain key.

I deliberately kept the key readable. When something does not match, a developer or support analyst can inspect it and quickly see why. Sorting the dimension names before concatenation ensures that the same context produces the same key, regardless of the visual or filter order.

This key becomes the contract shared by the semantic model, task flow, UDF, and SQL tables. Once the contract is stable, the downstream layers do not need report-specific dimension logic.

Each layer has one job

• Power BI identifies the current grain, validates the selection and builds the payload.

• The translytical task flow collects the user’s input and passes it with the report context.

• The Fabric UDF validates the request, records identity, applies versioning and performs the transaction.

• The SQL database stores every version and exposes views for current-state and historical reporting.

This division is what makes the pattern reusable. The database does not need to understand a Power BI visual. DAX does not need credentials. The task flow does not calculate version numbers.

DAX is the first safety check

The most important DAX in this project is the logic that decides whether the current context represents exactly one writable grain.

The implementation uses ISINSCOPE and ISFILTERED to detect active dimensions. It chooses the finest valid time level, sorts dimension names before building the key and matches complete tokens so that names such as Region and Subregion cannot collide.

It also blocks ambiguous requests. If a required dimension contains multiple selected values, no valid time grain is available or the context does not identify one analytical cell, the function is not called. Consistent sentinel values are used when a dimension is intentionally not applicable.

The UDF should receive a clear business request. It should not have to reconstruct the report context from unrelated parameters.

Keep the UDF contract small

The implementation exposes three actions: upsert a comment, soft-delete a comment and retrieve the latest relevant record. Parameters use a consistent camelCase convention, secrets remain server-side and the task flow receives a simple string that Power BI can display.

The function does not contain separate branches for Region, Segment, Product or any future dimension. It accepts the grain key and normalized context, then applies the same transaction rules every time.

Preserve history instead of overwriting it

Updates and deletes do not modify an existing row. Each action inserts a new version for the same grain key:

1. Version 1, insert: the first comment is saved.

2. Version 2, update: the edited comment is stored as a new row.

3. Version 3, delete: a soft-delete row marks the comment as inactive.

4. Version 4, insert: a new comment can reactivate the grain without erasing its earlier history.

The transaction calculates the next version and inserts it atomically. This keeps the audit trail intact and makes concurrent behavior easier to reason about.

Three SQL views serve different needs:

• Latest active for normal report consumption.

• Full history for audit and troubleshooting.

• Latest including deleted for checking the most recent lifecycle state.

Read comments back without complicating the model

Commentary is sparse and can exist at several grains. Forcing every possible combination into the main star schema can create ambiguous relationships or too many bridge tables.

I use a disconnected readback table instead. A measure calculates the grain key for the current visual context and retrieves the matching latest comment. This keeps the semantic model clean while allowing one visual to show commentary at different valid grains.

Adding another supported dimension is mainly a metadata and key-generation change. It does not require a redesign of the relationship model.

Use any suitable SQL-based database

The pattern is storage-agnostic. The repository includes a Databricks Delta implementation because that is where the original model’s data lived, but Databricks is not required.

The same approach can use Fabric SQL Database, Fabric Warehouse, Azure SQL, SQL Server, PostgreSQL, MySQL, Databricks SQL or another SQL engine that the UDF can reach securely. Connections can use a native Fabric connection, a compatible package from public PyPI or an approved private Python library. Microsoft documents these options in Connect to data sources and Manage libraries.

When changing database engines, adapt the connection code, SQL dialect and data types. Keep the logical schema, the uniqueness rule for grain and version, the soft-delete behavior and the latest-row views. The DAX contract and task-flow payload should remain unchanged.

If I were starting fresh inside Fabric, I would begin with Fabric SQL Database. If the organization already has a governed SQL platform, I would keep it and adapt only the connection and SQL implementation.

What is included in the repository?

• dax/ contains parameters, time-key logic, grain-key generation, validation, readback and debugging measures.

• sql/ contains the append-only table and three consumption views.

• udf/ contains the Python User Data Function and its dependencies.

• tmdl/ contains reusable semantic-model metadata.

• pbix/ contains the Power BI integration checklist.

• docs/ contains setup guidance and the write-back flow presentation.

• assets/ contains the architecture visuals.

The implementation is available in the Translytical Workflow in Power BI repository.

A practical rollout sequence

1. Create the history table, constraints and readback views in the chosen SQL database.

2. Deploy the Fabric UDF and configure its connection, libraries and secrets.

3. Add the DAX parameters, time logic, grain-key measures and validation measures.

4. Configure the task-flow inputs and map them to the UDF parameters.

5. Add disconnected readback and display the latest state in Power BI.

6. Test inserts, updates, deletes, reactivation, invalid multi-selects and concurrent writes.

Test the same analytical cell from different visuals and filter orders. The resulting key should always be identical. Then compare a Region-only key with a Region-and-Segment key and confirm that they remain different.

What I learned from building it

The visible part of a translytical workflow is the button or form. The durable part is the contract underneath it.

A deterministic key gives every transaction a stable identity. DAX prevents ambiguous requests. The UDF provides a secure execution boundary. Append-only SQL preserves history. Disconnected readback keeps the model manageable. Storage portability allows the pattern to move with the organization’s data platform.

Together, these choices turn commentary write-back from a one-report customization into a reusable capability for forecasting, approvals, exception handling, data stewardship and other human-in-the-loop workflows.

If you are building something similar, I would be interested to know which SQL engine you are using and which report grains or translytical use cases have been the most difficult to model.