3.1 Complex Power Fx Formulas and Functions

Power Fx Is Declarative, Not Procedural

Power Fx is the formula language behind every canvas app control property. Unlike C# or JavaScript, most Power Fx expressions are declarative — you describe the desired result and the platform figures out when to recalculate it, similar to a spreadsheet cell. A Label.Text property set to Sum(Filter(Orders, Status = "Open"), Amount) automatically re-evaluates whenever Orders or Status changes; you never write an explicit recalculation loop. The exception is behavior formulas — expressions attached to trigger properties like OnSelect, OnVisible, or OnStart — which execute imperatively, in order, only when the trigger fires, and may include state-changing functions such as Set, Patch, or Navigate that are blocked in pure value properties.

Named Formulas and User-Defined Functions

Two additions to modern Power Fx let developers replace fragile OnStart logic with declarative, testable code:

• Named formulas are app-level calculated values (defined under App > Formulas) that behave like a spreadsheet cell: TaxRate = 0.0825 or ActiveUsers = Filter(Users, Status = "Active"). They recalculate automatically, cannot hold circular references, and eliminate the need to stash static values in global variables at OnStart.
• User-defined functions let a developer write a named, parameterized, reusable function — AddTax(Amount: Number): Number = Amount * (1 + TaxRate); — with an explicit return type, callable from any formula in the app. This is the closest Power Fx comes to a conventional function definition, and is a PL-400-testable improvement over duplicating the same formula logic across multiple controls.

Choosing Between Set, UpdateContext, and Collect

Advanced canvas apps use three different state-holding mechanisms, and picking the wrong one is a common defect the exam probes:

UpdateContext is the only one of the three that cannot be called from App.OnStart (no screen context exists yet), and it cannot be read from a different screen — referencing a context variable across screens returns blank. ClearCollect replaces a collection's entire contents in one call and is the standard pattern for caching a filtered dataset locally for offline access or performance; plain Collect appends rows instead of replacing them.

Delegation and Delegation Limits

Delegation is the mechanism that pushes data operations (filtering, sorting, searching) to the data source's server instead of pulling all rows into the app and processing them locally. It matters because canvas apps only retrieve data in pages, governed by the delegation limit — a setting under App > Advanced Settings capped at 2,000 rows, with a default of 500.

• A formula is delegable when every function and operator inside it has a server-side translation for the connected data source. Dataverse, SharePoint, and SQL Server support delegation for common operators (=, <, >, StartsWith, And, Or) through functions like Filter, Search, Sort, and SortByColumns.
• A delegation warning — the blue dotted underline in the formula bar — appears when Power Fx cannot push part of an expression to the server. Common non-delegable culprits include nested Filter calls with mismatched operators, Sum/Average/CountRows combined with a non-delegable filter, and string functions like Left, Mid, or Concatenate used inside a filter condition.
• When a formula isn't delegable, Power Fx retrieves only the first N rows (per the delegation limit) and evaluates the rest locally — results silently exclude records beyond that limit. Fixing this requires restructuring the formula around delegable operators, adding a server-side calculated/indexed column, or reducing the practical dataset with a pre-filtered view.

Concurrent Evaluation

By default, formulas separated by ; inside a behavior property (like OnSelect) run sequentially, each waiting for the previous one to finish — costly when several independent network calls (a Dataverse lookup, a SharePoint call, a connector request) don't depend on each other. Wrapping those calls in Concurrent(formula1, formula2, formula3) runs them in parallel, and the overall operation completes as fast as the slowest single call rather than the sum of all of them. Concurrent is one of the highest-leverage, lowest-risk canvas performance techniques, and is explicitly called out in the PL-400 blueprint's "optimize canvas app performance" sub-topic.

Complex Table and Error-Handling Functions

Beyond basic Filter/Sort, developers use With({}) to scope a temporary named value inside a single formula without polluting app state, ForAll to iterate and act on every row of a table (frequently paired with Patch for bulk record creation or updates), and AddColumns/DropColumns/ShowColumns/RenameColumns/GroupBy to reshape tables in memory. For resilience, IfError and IsError wrap operations that might fail (a Patch against Dataverse, a connector call) and return a fallback value or trigger Notify instead of letting the app show a raw platform error — a pattern the exam expects developers to apply around any write operation that crosses a network boundary.