Background

I recently migrated a Power App + SharePoint writeback workflow to a fully native Microsoft Fabric Translytical Task Flow solution. The use case: store managers validating planogram fixture set data for hundreds of wholesale accounts, with business rules driving automatic pre-validation and a human review layer on top.

This post covers the patterns that were non-obvious and the gotchas worth knowing before you start. If you are building any TTF writeback and want to handle multi-row selection, NULL thresholds, auto-validation locking, or a second admin TTF from the same report, this is for you.

Architecture Overview

• Fabric SQL Database as the writeback target (one table: dbo.SFSValidation)
• Two separate UDF items -- one for user-facing validation actions, one for admin rule editing. They write different tables. Keep them separate so a bug in one never takes down the other
• Import + DirectQuery hybrid: the main matrix uses Import for performance; a parallel DQ copy of the same table (SFSValidation-DQ) feeds a live per-user count panel
• Hidden admin page gated by USERPRINCIPALNAME() with a nav button whose destination measure returns null for non-admins

Pattern 1: Multi-Row Batch Selection

Power BI TTF buttons pass one value per parameter. If you want to act on multiple selected rows in one click, you need to encode the selection into a single string.

DAX side

The Selected Keys measure encodes the matrix selection as a semicolon-delimited string of Year-TDLinx7 pairs:

Selected Keys =

VAR _sel = SUMMARIZE('Same Stores Table',

'Same Stores Table'[Activity_Year],

'Same Stores Table'[TDLinx7])

RETURN CONCATENATEX(_sel, [Activity_Year] & "-" & [TDLinx7], ";")

The UDF receives this string, splits on semicolons, validates and deduplicates each token, and processes the entire batch in one SQL transaction. Cap the batch size with a constant (_MAX_BATCH = 500) so the WHERE clause in the pre-read never gets pathological.

UDF side (Python)

The _parse_keys helper enforces the format: 4-digit year first, 7-digit zero-padded TDLinx second, separated by a hyphen. A reversed key fails the length check and raises a UserThrownError instead of silently writing garbage. Leading zeros on TDLinx are preserved because the column is text, not int.

Pattern 2: The [Key] Join Column

Power BI cannot relate two tables on a composite key (TDLinx7 + Year as a pair). The solution is a single [Key] column equal to Year-TDLinx7 that the semantic model uses for the relationship. Every INSERT path in the UDF must stamp this column. If you omit it on a fresh insert the new row is unjoinable and invisible in the matrix -- it exists in the warehouse but Power BI will never show it.

The UPDATE path deliberately never touches [Key]. An existing row already carries it and it is the live join key. Changing it under a live relationship would break the join.

Pattern 3: Auto-Validation Lock

Some rows are pre-validated by a scheduled notebook that evaluates business rules. These rows should be locked from manual override. The pattern has two layers:

• Pre-read: before the write loop, fetch AutoValidated for the whole selection in one round trip. Rows where it is 1 are skipped for status changes but still receive comments
• SQL guard: every status MERGE includes AND target.AutoValidated <> 1 on the MATCHED branch, so even a race condition between the pre-read and the write cannot corrupt a locked row

The _flag_is_one helper normalises the flag value regardless of whether the warehouse column is int, bool, or text -- because Fabric Warehouse column types are not always what you expect when a dataflow created the table.

Pattern 4: NULL Sentinel for Optional Sliders

The Wholesaler Rules admin page uses numeric range sliders for Min and Max thresholds. Some BUs intentionally have no upper threshold (CENTRAL BU runs with a NULL Max). The problem: a blank slider in Power BI passes its default value, not null. If you default to 0, you silently overwrite an intentional NULL with a hard zero.

The sentinel pattern: set the slider range to -100 to 100 with a step of 1, and set the SELECTEDVALUE fallback to -999. Since -999 is outside the slider range, it is unreachable by hand. The UDF checks for the sentinel and stores NULL. This way blank means no threshold and 0 means an explicit zero floor.

Min Threshold Send = SELECTEDVALUE('Min Threshold'[Value], -999)

# In the UDF:

min_val = None if minThreshold == -999 else int(minThreshold)

Pattern 5: Admin-Only Nav Gate

The rules admin page is hidden in the report tab strip, but hidden is not locked. The real gate is a navigation button whose destination measure returns null for non-admins:

Is Rules Admin =

IF(USERPRINCIPALNAME() IN {"[email protected]", "[email protected]"}, 1, 0)

Rules Admin Destination =

IF([Is Rules Admin] = 1, "Wholesaler Rules Admin")

Bind the button destination to Rules Admin Destination with Field value. For non-admins the measure returns blank, so the button does nothing. The button text can also be fx-bound to blank for non-admins so they do not even see it.

Gotchas Worth Knowing

• Draft vs published: the UDF test pane runs the draft. The report button calls the last published build. Always publish before testing from the report, or you will debug a problem that does not exist in production
• DirectQuery from a Fabric Warehouse reads the SQL analytics endpoint by default, which has a sync lag. Repoint to the operational .database.fabric.microsoft.com endpoint in Power Query for instant post-write refresh
• Dataflow-created tables cannot be altered via DDL. Schema changes go through the dataflow (add columns as typed nulls in M, set destination to Replace, run once)
• USERPRINCIPALNAME() only works in measures. It errors in calculated columns and tables
• The 2-minute publish cooldown on UDF items is real. If your second publish seems to have no effect, wait the cooldown and publish again
• Fabric Warehouse does not honor DEFAULT in CREATE TABLE. Every INSERT path in your UDF must explicitly set all non-nullable columns

Result

The entire workflow moved from Power App + SharePoint + manual import to a single Fabric environment. Writes land in under a second. Multi-row selection went from impossible to trivial. The rules admin TTF gave the business direct control over auto-validation thresholds without ever leaving the report. The confirmation message echoes the exact stores updated and the comment saved, so every click has a clear receipt.