Building a Generic Power BI Commentary Writeback Framework with Fabric User Data Functions and Databricks in a Composite Model

Co-Authors :

Natarajan Manivasagan

Praful Potphode

Power BI reports are excellent for analysis, but business users often need one more capability: the ability to explain numbers directly inside the report.
For example:

Why did Net Revenue exceed target?
Why did Gross Margin drop for a selected Profit Center?
Why is one Cost Center showing an unusual variance?
What changed for a specific Customer, Material, or GL Account?

Traditionally, these explanations end up in emails, Excel files, Teams messages, or offline trackers. The problem is that those comments are disconnected from the actual reporting context — nobody can tell later exactly which Company, Profit Center, Customer, and period a note like “Q2 was soft” referred to. And there’s no audit trail: no versions, no author, no timestamps. Also, we need to create a PowerPoint to explain these numbers, which is also a huge task for any analyst.

In this post, I’ll walk through a generic commentary writeback framework I built using:

• Power BI

• Microsoft Fabric User Data Functions

• Power BI Translytical task flows

• Databricks Delta tables*

• A versioned latest-comment view for simple

Although I used Databricks for this implementation, the same pattern can also work with Fabric Warehouse, Azure SQL, or any SQL-based database for storing comments.

The design goal was one sentence:

Any selected business grain + selected metric + selected period = one comment grain.

Press enter or click to view image in full size

Note - All names, values, and screenshots shown are illustrative and anonymized for demo purposes.

Why this was needed

In finance and commercial reporting or any other reporting, users analyze data at different grains — and they want to comment at whatever grain they’re currently looking at.
One user might comment at different levels with combinations of different dimensions.

A fixed writeback design would require separate columns, separate parameters, and separate logic for every possible combination. That becomes complex — fast. Every new dimension in the model means touching the storage schema, the function signature, and the report wiring.

So instead of building a hardcoded commentary solution, I used a generic grain-key approach: Power BI describes the current context as a single composite key, and everything downstream just stores and matches that key.

What are Translytical task flows?

Power BI Translytical task flows allow users to take actions directly from a Power BI report, including writeback scenarios.

In this framework, a Power BI button is configured with the Data function action and mapped to a Fabric User Data Function. The button passes the selected report context and user-entered comment to the UDF.

For capturing the comment text, I used the Power BI Input slicer, which works as a user input control for writeback scenarios.

High-level architecture

Press enter or click to view image in full size

Figure. Architecture

Fabric User Data Functions provide a serverless way to host reusable Python logic and invoke it from Fabric items or other applications.

The key design decision: Power BI builds the grain key
Instead of passing every possible dimension as a fixed UDF parameter, Power BI generates a single grain_key from whatever is currently in scope.
Example:

SOURCE=COPA
|PERIOD=2026-03
|METRIC=NET REVENUE
|DIMS=COST_CENTER|PROFIT_CENTER
|COST_CENTER=CC100
|PROFIT_CENTER=PC200

If the user adds Material to the visual, the key automatically becomes more detailed:

SOURCE=COPA
|PERIOD=2026-03
|METRIC=NET REVENUE
|DIMS=COST_CENTER|PROFIT_CENTER|MATERIAL
|COST_CENTER=CC100
|PROFIT_CENTER=PC200
|MATERIAL=M100

This means the UDF does not need to know whether the comment is for Cost Center, Profit Center, Material, Customer, GL Account, or any dimension added in the future.

The UDF simply stores:

• sourceArea

• grainKey

• grainLabel

• contextJson

• fiscalPeriod

• metricName

• commentText

That keeps the solution simple and scalable. Power BI owns context detection; the UDF owns persistence. Neither side needs to change when a new dimension shows up.

Databricks design: append-only versioning

Instead of overwriting comments, every change is stored as a new version.
The main Databricks table is powerbi_comment_versions. Each save creates a new row with INSERT, UPDATE, or DELETE as the action_type.

A simplified table design:

CREATE TABLE powerbi_comment_versions (
    comment_version_id STRING,
    comment_thread_id STRING,
    source_area STRING,
    grain_key STRING,
    grain_hash STRING,
    grain_label STRING,
    context_json STRING,
    fiscal_period STRING,
    metric_name STRING,
    comment_text STRING,
    action_type STRING,
    version_no INT,
    is_deleted BOOLEAN,
    created_utc TIMESTAMP,
    created_by STRING,
    changed_utc TIMESTAMP,
    changed_by STRING,
    invocation_id STRING
)
USING DELTA;

This gives us:

• Full audit trail

• Version history

• Soft delete

• Latest active comment readback

• Reusability across multiple report areas

Latest comment view

To keep Power BI simple, I created a Databricks view that returns only the latest active comment for each grain:

CREATE OR REPLACE VIEW vw_powerbi_comments_latest AS
WITH ranked_comments AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY source_area, grain_key
            ORDER BY version_no DESC, changed_utc DESC
        ) AS rn
    FROM powerbi_comment_versions
)
SELECT *
FROM ranked_comments
WHERE rn = 1
  AND is_deleted = false;

Power BI reads from this latest view.

That means the DAX readback logic does not need to calculate the latest version, the latest timestamp, or the deleted status. Databricks handles that.

UDF design

The main function is upsertPowerBIComment(). It follows this logic:

If no comment exists:
    insert version 1
If comment already exists:
    append new UPDATE version
If latest state was deleted:
    append new INSERT version again

There is also a delete function, deletePowerBIComment(). It does not physically delete the row. It appends a new version with:

action_type = DELETE
is_deleted = true

This keeps the full audit trail intact — a delete is just another event in the thread’s history.

The Power BI report experience

In the report, the user can:

1. Select a Fiscal Period.

2. Select a Metric.

3. Select a row in a matrix or choose slicer values.

4. Enter a comment in the input slicer.

5. Click Save Comment.

6. See the latest comment reflected in Power BI.

Example comment:

🎯 Target achieved at 104.5% — performance is above plan due to strong volume and better customer mix.

The same framework works whether the visual has Cost Center + Profit Center, or Cost Center + Profit Center + Material, or a different grain altogether.

Readback in Power BI

The latest comments table stays disconnected from the business model. No relationships are required.

Power BI matches the current visual context to the latest comment using the generated grain_key:

Latest Comment =
VAR _GrainKey = [Current Comment Grain Key]
VAR _SourceArea = [UDF Source Area]
RETURN
IF (
    ISBLANK ( _GrainKey ),
    BLANK (),
    LOOKUPVALUE (
        'Comments Latest'[comment_text],
        'Comments Latest'[source_area], _SourceArea,
        'Comments Latest'[grain_key], _GrainKey
    )
)

Why not create relationships?

Because the comment can exist at any grain.

A comment may be stored at Company, or Company + Cost Center, or Company + Cost Center + Profit Center + Material. If we create normal relationships to all dimensions, blank or partial dimension values can cause incorrect filtering or missing comments.

The cleaner approach:

• Comments table = disconnected

• Matching logic = grain_key

Demo flow

A simple end-to-end demo sequence:

1. Select a fiscal period.

2. Select a metric like Net Revenue.

3. Select a Cost Center and Profit Center.

4. Enter a comment:
   🚀 Revenue target exceeded at 109.7% — strong execution across the selected cost center and profit center.

5. Click Save Comment.

6. Show the comment icon and latest comment.

7. Update the comment and save again.

8. Show that the version number increased.

9. Delete the comment.

10. Show that it disappears from the latest view but remains in history.

Press enter or click to view image in full size

Figure. End-to-End Flow

Final thoughts

This was a useful experiment in combining analytical reporting with governed writeback.

Power BI remains the front-end experience, Fabric User Data Functions handle the business logic, and Databricks provides a scalable, versioned storage layer. We can use Fabric SQL or Azure SQL or any SQL-based DB for storing comments.

The most important part of the design is the generic grain_key. Once that is in place, the same pattern supports many commentary use cases:

• Finance variance commentary

• Sales performance notes

• Forecast assumptions

• Planning annotations

• GL explanations

• Customer or material-level observations

That’s what makes the framework flexible, auditable, and reusable across enterprise reporting scenarios.

If you found this useful, feel free to connect — I’d love to hear how others are approaching writeback in Power BI and Fabric.

Disclaimer – Data is generated by using Claude AI for healthcare domain.