Many discussions about Microsoft Foundry Agent Service eventually arrive at the same question: should this workload be implemented as a Prompt Agent or a Hosted Agent? While the documentation explains both options well, the architectural decision usually comes down to something much simpler: where do you want the orchestration logic to live?

First, what actually makes something an agent? 

A basic AI assistant generates an answer. An agent can also decide what to do next, call tools, access data, maintain context and complete work across multiple steps. 

At the center of most agents are three building blocks: 

• Model: provides language understanding, generation, and reasoning. 

• Instructions: define the job, boundaries, role, and expected behaviour. 

• Tools: connect the agent to knowledge and actions such as search, APIs, databases, code execution, MCP servers or business systems. 

For enterprise use, that is only the starting point. You also need identity, authorization, network controls, content safety, session management, evaluation, tracing, versioning, rollback, and cost controls. Foundry Agent Service provides the surrounding platform capabilities, while letting you choose how much runtime logic your team owns. 

Where should orchestration logic live and who should own the runtime?

Understanding the Runtime Boundary

When evaluating Foundry Agent Service, many teams focus on models. In practice, models are rarely the architectural differentiator.

Most architecture reviews eventually come down to three questions:

• Who owns orchestration?
• Who owns state?
• Who owns operations?

Foundry Agent Service provides a managed platform for these concerns, but the amount of control retained by engineering teams depends on the selected agent type.

For most teams, the architectural decision usually comes down to one of two operating models

• Prompt agents: declarative agents defined by a model, instructions, and tools, with a managed runtime. 

• Hosted agents: code-based agents that you package and run in Foundry, while the service manages the endpoint, identity, scaling, sessions, and observability. 

Prompt Agents

With a Prompt Agent, engineering teams focus primarily on defining the model, instructions, tools, knowledge sources and identity configuration, while Foundry takes responsibility for the surrounding runtime.

Why teams start here 

Prompt agents are usually the fastest route from an idea to a working, governed agent. They are a good fit when the behaviour can be expressed clearly through instructions and supported tools. 

• You need to deliver quickly. 

• The agent follows a fairly straightforward reasoning and tool-use loop. 

• Foundry-supported tools cover the required integrations. 

• You do not need custom libraries, middleware, or orchestration code. 

• You want Foundry to own compute, scaling, and patching. 

• Reviewers need an agent definition that is easy to inspect. 

Good examples 

Enterprise knowledge assistant. Employees ask about policies, engineering standards, procedures, or product information. The agent retrieves approved content and cites its sources. 

Document review assistant. The agent checks a proposal or design against an approved rubric and returns structured findings, while a human keeps responsibility for the final decision. 

Employee self-service agent. The agent answers questions and performs a small number of tightly scoped actions, such as checking request status or creating a support case. 

A useful warning sign A Prompt agent is probably becoming the wrong fit when the prompt starts looking like application code. Large branching instructions, retry logic written in prose, state-machine behaviour, custom payload handling, framework middleware or real-time media are all signs that runtime logic belongs in code instead. 

Hosted Agents

Hosted Agents move the responsibility boundary. Instead of defining behaviour through configuration alone, engineers deploy an actual application into Foundry Agent Service. Hosted Agents are framework-agnostic. Whether your team builds with Agent Framework, LangGraph, Semantic Kernel, OpenAI Agents SDK, or a custom runtime, Foundry can host the application while managing the surrounding operational services.

When Hosted agents make sense 

• You need a particular agent framework or custom orchestration engine. 

• The flow includes branching, parallel work, fan-out and fan-in, or human approvals. 

• Business rules require a deterministic state machine around model reasoning. 

• You need custom packages, middleware, algorithms, retries, caching, or error handling. 

• The client sends custom payloads or webhooks. 

• The session needs persistent files or custom state. 

• The design includes multi-agent orchestration or real-time voice. 

Good examples

A bank onboarding workflow where uploaded documents must be validated, checked against multiple systems, and routed to a human when confidence drops below a threshold.

A fraud investigation agent that gathers transaction history, enriches data from multiple internal systems, applies bank-specific risk rules, requests additional evidence when required, and generates a recommended outcome for an investigator. The process involves long-running workflows, branching logic and audit requirements that are better suited to code-based orchestration.

A lending workflow that coordinates document collection, credit bureau checks, income verification, affordability assessments, policy exceptions, and approval routing. The process spans multiple systems and often requires deterministic decision paths that extend beyond prompt-driven orchestration.

A security operations agent that aggregates alerts from SIEM platforms, enriches incidents with threat intelligence, executes automated containment actions, opens tickets, requests approvals for high-impact remediation steps, and maintains a complete audit trail of decisions and actions.

The trade-off 

More control also means more ownership. Your team must secure and patch the code and dependencies, test the runtime, manage supply-chain risk, and think about compute sizing, cold starts, session lifecycle, and cost. Hosted agents reduce platform plumbing, but they do not remove application engineering. 

Choosing Prompt Agent/Hosted Agents 

A pattern I see quite often is teams arriving at the solution before they've fully articulated the requirement. The conversation usually starts with "We need a Hosted Agent," but after digging into the workload, the real requirements turn out to be things like persistent state, webhook processing, custom orchestration, background execution, framework-specific capabilities, or human approval workflows.

These are runtime concerns, not agent concerns and they're usually the factors that determine whether a Hosted Agent is necessary. Hosted Agents are valuable because they give engineering teams control over those aspects of execution while still offloading much of the operational infrastructure to Foundry.

This is also where teams most commonly choose the wrong agent type. A frequent assumption is that existing investments in frameworks such as LangGraph or Semantic Kernel automatically imply a Hosted Agent architecture. In practice, many of these workloads are relatively simple orchestration scenarios that can be implemented effectively as Prompt Agents, with lower operational overhead and less infrastructure to manage.

My advice is usually to start by identifying the runtime requirements rather than selecting an agent type. Once those requirements are clear, the right architecture often becomes obvious.

What I would choose today

Hosted agents in Foundry Agent Service - Microsoft Foundry | Microsoft Learn

Windows Task Manager now provides deeper visibility into AI workloads running on a device. Previously, the Processes tab didn't show per-process activity for the neural processing unit (NPU) or graphics processing unit (GPU) neural engine. Now, on some newer devices, this data appears alongside familiar central processing unit (CPU) and standard GPU activity on the Processes tab, while the Performance tab shows overall utilization. Whether you're an IT pro, developer, or power user, this data can help as follows:

• For IT professionals: Troubleshoot and report on performance.
• For developers: Validate whether AI models are landing on the intended NPU or GPU neural engine instead of CPU or standard GPU compute.
• For power users: Understand and improve performance and battery life.

Let's look at these use cases more closely.

Note: In this post, we distinguish standard GPU compute from the newest GPU equipped with dedicated AI matrix engines, called the GPU neural engine. The NPU and GPU neural engine are new pieces of hardware that perform AI math more efficiently than the standard GPU.

Enterprise helpdesk support

As the demand for AI based tooling increases, it's important to have a built-in way to determine what processing resources an app is using.

If you're an IT helpdesk professional, you support hundreds to thousands of corporate devices. When a user reports a problem with an AI-enabled app, you can use the improved Windows Task Manager to troubleshoot.

On supported devices, the Processes and Performance tabs show exactly how background tasks are interacting with processing engines. For example, here is how to tell if a workload designed to reach the dedicated NPU and GPU neural engine is instead falling back to general CPU and GPU compute.

1. In Task Manager, go to the Processes tab.
2. Choose which data to view. Right-click (or select and hold) in the column area and select any (or all) of the following columns: NPU, NPU engine, GPU, and GPU engine.
   
   The Processes tab in Task Manager shows a popup menu of available columns to display. The NPU shows high usage from an AI-enabled application in the top row.
3. Locate the desired software or enterprise agent in the Name column. If your application takes advantage of hardware designed for AI workloads, you can see utilization across the different areas.
   
   

   The GPU Neural engine shows usage from an AI-enabled application in the top row.

4. To visualize usage over time, navigate to the Performance tab.
5. Select NPU or GPU. Alternatively, view the NPU or GPU by right-clicking (or selecting and holding) anywhere in the graph space. Select View, then choose among CPU, Memory, Disk, Network, GPU, or NPU views.
   
   The NPU performance graph shows usage from an AI-enabled application.The GPU performance graph shows usage from an AI-enabled application in the Neural engine.

To evaluate whether an application takes advantage of dedicated hardware appropriately, check if the application offloads to the NPU or the GPU's matrix arrays. If you see that the application mostly uses the CPU or runs on the GPU's rendering pipeline, that application might be misconfigured or it might not have been designed to take advantage of this hardware.

Once you identify whether the workload is running on the NPU, GPU, or CPU, you can spot-check individual devices for further details. For example:

• Is a device's AI workload reaching the NPU? Or is it falling back to the GPU rendering pipeline or general CPU compute?
• Which applications on the device are actually using the NPU or the GPU's neural engine?

Real-time validation for AI-driven app development

When building AI applications with frameworks like ONNX Runtime or Windows DirectML, you write code to target specific hardware execution providers (i.e., CPU, NPU, or GPU). If an application encounters an unsupported data type or driver mismatch, the framework can silently fall back to a higher-overhead compute pipeline to prevent the application from crashing.

To help you identify and correct these issues, as well as to track appropriate AI model performance, we've introduced more granular views in Task Manager specific to the NPU and the GPU neural engine.

Hardware use verification

You can use Task Manager for basic runtime verification to confirm what processing resources your AI workload is actively using.

1. In Task Manager, open the Details tab.
2. Right-click (or select and hold) on any column header and select Select columns.
3. Check any of the following to monitor active execution: NPU, NPU engine, GPU, and/or GPU engine.
4. Look at the active use data to identify any workloads that fall back to the CPU and, therefore, don't use the device's resources appropriately. They show the following symptoms: the NPU column stays at 0%, the NPU engine remains blank, while the GPU engine column shows no activity from its neural engine.
5. (Optional) Enable Dedicated NPU memory, Shared NPU memory, Dedicated GPU memory, or Shared GPU memory to track weight allocations and memory overflow.
   The Task Manager Details tab shows a pop-up menu to select additional columns.

Reasons for unexpected AI model performance

If your local AI model is running unexpectedly slow, the system might be silently falling back to the CPU or the standard GPU compute pipeline. There are two main reasons for this:

• A device might lack a specific driver.
• There might be a data type mismatch (for example, trying to force an FP32 model onto an INT8-optimized NPU or an unsupported operation in the model not compatible with the neural engine or NPU).

Modern silicon architectures integrate dedicated matrix accelerators right inside the graphics processor. Therefore, it's critical to track whether your math lands on the GPU 0 - Neural engine versus the NPU as you debug your execution provider bindings.

Another dimension that can explain variable AI model performance is through the new columns: Shared NPU memory and Dedicated NPU memory. These columns show how much memory an AI workload is using and which pool it comes from.

• Shared NPU memory: The NPU draws from system memory alongside the CPU and GPU. On a system with both Dedicated and Shared NPU memory, watch for rising shared NPU memory usage. It's your indicator that dedicated memory is filled, and allocations are spilling over to shared NPU memory, which can reduce performance.
• Dedicated NPU memory: Memory belonging to the NPU alone. A workload that fits within it doesn't compete with the rest of the system. This column is empty on integrated NPUs, which have no separate pool.

Live tracking

As the previous screenshots show, the Performance tab brings multiple details together into a live tracking view. This means you can run your tuned model, look at Task Manager, and instantly verify:

• The success state: Matrix multiplication executes on the NPU and/or the GPU neural engine as expected.
• The fallback state: If the NPU and the GPU neural engine both show zero usage while CPU or general GPU usage is increasing, the fallback is happening. Proceed to re-quantize your model.

Task Manager now makes execution provider fallback visible at a glance, in real time, alongside what the rest of the system is doing.

Power profile insights for power users

If it's not your job to build or troubleshoot AI applications, the enhanced Task Manager experience still offers workload transparency and proactive battery management.

AI workloads are computationally heavy. When a local application runs a background blur, a video upscale, or an image manipulation model, it has two main ways to execute these tasks.

• The CPU and standard GPU route: Power-hungry workloads kick the fans into overdrive and drain your battery in about two hours.
• The NPU and GPU neural route: Highly efficient workloads sip milliwatts and keep your laptop cool on your lap.

Imagine you're working on battery power and running a suite of creative or collaboration tools. They don't drain your battery as quickly if they're optimized to use modern hardware acceleration pipelines. And you no longer have to guess how your applications are impacting your system resources. To verify feature optimization using Task Manager:

1. In Task Manager, open the Processes tab.
2. Select an active application.
3. Compare the data in the GPU engine, NPU, and NPU engine columns. That's where you can see exactly which execution engine the app is using in real time.
   
   The Task Manager Details tab shows the NPU engine and dedicated and shared NPU data for Python.

An application can be optimized to use both the NPU and the GPU, but it might not be optimized to use either.

• If an application is optimized to use the NPU, you'll see either NPU - Compute or NPU - Neural under the NPU engine column. Depending on your processor and driver version, Task Manager labels active NPU workloads as NPU – Compute or NPU – Neural. Both indicate that on-device AI acceleration is actively running on the NPU.
• Similarly, if an application is optimized to use the GPU's hardware, the GPU engine column lists GPU – Neural.
• Apps that aren't optimized for either of these dedicated resources show one of the following symptoms:
  • The CPU column shows high usage while the NPU column is idle.
  • The GPU engine column doesn't show GPU - Neural.

More explicitly, Power usage and Power usage trends reflect and can confirm the impact.

Task Manager gives you instant operational awareness. It allows you to adjust your behavior constructively. For example, you can pause heavy background tasks until you're connected to a power outlet. Or, you can continue using apps that are already fully native for your unplugged, on-the-go workflows.

Visibility drives optimization

As AI workloads become more common on Windows devices, visibility into NPU and GPU neural engine utilization can help you make better informed decisions. With the latest Task Manager improvements, you can monitor AI processing activity alongside CPU, memory, storage, and networking data from a familiar interface.

Explore the new metrics in Task Manager to evaluate application performance, troubleshoot resource bottlenecks, and better understand how your devices use AI-capable hardware. Importantly, these growing capabilities today provide a foundation for future hardware innovation across the ecosystem.

Continue the conversation. Find best practices. Bookmark the Windows Tech Community. Looking for support? Visit Windows on Microsoft Q&A.

While working on Text Grab I found my self wanting to visualize my clipboard content… so I built an app to do it! Sometimes text content takes multiple formats so being able to understand how the clipboard works makes it easy to integrate clipboard capabilities into my other apps.

This app is fully open source on GitHub here: https://github.com/TheJoeFin/clipboard-inspector

Releases are signed MSIX for x64 and Arm64 here: https://github.com/TheJoeFin/clipboard-inspector/releases

Give it a shot and let me know what you think!

Joe

A new release of the Microsoft build of Go is now available for download. For more information about this release and the changes included, see the table below:

As of this release, Go 1.25 is no longer supported, per the Go release policy.

Changes in the Microsoft build of Go 1.27

The 1.27.0-1 release is a major version update.

As always, please let us know if you encounter a problem or have a question by filing an issue. If you have access to Microsoft internal sites, you can alternatively use one of the channels listed in our internal support documentation such as the Golang Friends group in Teams.

The following is a summary of the Microsoft build of Go 1.27 release notes, emphasizing important changes. To see the canonical release notes doc, visit the full go1.27 release notes Markdown file.

Systemcrypto

See the Go 1.27 changelog in the FIPS documentation for more information.

Systemcrypto is no longer a GOEXPERIMENT setting

systemcrypto is still enabled by default on supported platforms, as it has been since Go 1.25 (Linux and Windows) and Go 1.26 (macOS). What’s new in 1.27 is that it is now selected automatically by the toolchain instead of being configured through GOEXPERIMENT:

• Setting the environment variable GOEXPERIMENT to systemcrypto or nosystemcrypto is now rejected with an error. To disable systemcrypto, set the environment variable MS_GO_NOSYSTEMCRYPTO to 1 instead.
• systemcrypto no longer appears in go env GOEXPERIMENT, goexperiment.Flags, or other GOEXPERIMENT-derived output.

The per-platform GOEXPERIMENTs opensslcrypto, cngcrypto, and darwincrypto have also been removed and now cause a build error.

The goexperiment.systemcrypto build tag is still emitted when systemcrypto is enabled. That tag, and the build tags for the removed per-platform experiments, remain supported for source compatibility, with no change in behavior.

New platform and backend support

systemcrypto is now supported on FreeBSD (amd64 and arm64), using the same OpenSSL backend as on Linux.

On Linux, systemcrypto now supports CGO_ENABLED=0 on architectures that have a cgo-less OpenSSL implementation. This replaces the Go 1.26 GOEXPERIMENT=ms_nocgo_opensslcrypto experiment, which has been removed.

New algorithms

The systemcrypto backends now implement the following ML-DSA parameter sets:

• ML-DSA-44
• ML-DSA-65
• ML-DSA-87

FIPS 140

GODEBUG=fips140=only has been added. It acts like fips140=on, but also panics if a non-FIPS-approved algorithm is used.

GODEBUG=fips140=off now explicitly disables FIPS mode and skips the platform-specific FIPS detection (such as the Linux kernel FIPS flag at /proc/sys/crypto/fips_enabled).

The GOFIPS and GOLANG_FIPS environment variable checks now match their intended behavior: only the value 1 enables FIPS mode, and any other value (including 0 and the empty string) is treated as if the variable were unset.

The post Go 1.27.0-1 Microsoft build now available appeared first on Microsoft for Go Developers.

A new release of the Microsoft build of Go is now available for download.

These minor releases include a fix to address a breakage affecting unencrypted HTTP/2 (h2c) connections caused by a security patch included in last week’s release. See go.dev/issue/80876 for details.

For more information about this release and the changes included, see the table below:

The post Go 1.26.7-1 and 1.25.14-1 Microsoft builds now available appeared first on Microsoft for Go Developers.

Row Level Security (RLS) in Power BI is the right way to restrict access to data in your reports based on the users' context. It is applied at query time, meaning the data isn't present in the model by the time a user accesses the tables, so there's no chance of data leakage. But this presents challenges when your users need comparative context from data they can't see - for example a global average. This post walks through a pattern using DAX to create Calculated Tables to give restricted users meaningful benchmarks without exposing the underlying data.

The comparative data problem with RLS

Let's start by describing the issue with a simple example. Consider a retail chain with twelve regional managers, each managing ten stores. They have access to a sales report to understand how their stores are performing, but RLS has been used to filter the report data so each manager can only see their own stores sales performance. With RLS in effect, there's no way for them to see how they're performing against the regional or company average. RLS solves one problem (who sees what) but introduces another (comparison becomes invisible).

We've seen this scenario show up many times and in many ways over the years. We've also seen lots of workarounds - duplicating reports for each audience, manual exports of aggregated data, or ad-hoc report requests from the BI team. None of these solve the problem properly - they add maintenance overhead, fragment the analysis and result in potentially stale data.

But, there is a cleaner pattern that solves the problem without the need for duplicating reports or business logic. We can use DAX Calculated Tables to pre-aggregate data at refresh time to a level that is inherently safe to share with all users, regardless of their RLS role. The sensitive detail never surfaces, but the comparative context becomes available to everyone.

Why common approaches fall short

Before we look at calculated tables, let's first understand why other approaches break down.

Using ALL() or ALLEXCEPT() in measures

Often people look for DAX measure-level fixes. The instinct is to write a measure using ALL() or ALLEXCEPT() to remove the RLS filter context.

However, this approach does not work - RLS filters are applied at the storage engine level, before DAX executes. By the time your measure runs, the filter is already in place. ALL() can only remove filters that DAX itself added (from visuals, slicers, or CALCULATE context). It has no access to security filters and cannot remove them.

Beware - confusion typically surfaces during development as report authors test measures in Power BI Desktop, where RLS is not enforced (unless using the View As functionality to test RLS rules), and ALL() appears to work correctly. When the same report is viewed by a restricted user with RLS, the measure returns only their filtered data - not the global values they expected.

Separate reports per audience

Another approach is to build separate reports for different audiences at different levels of aggregation - e.g. one for regional managers, another for country managers, another for executives. This works, but the cost is high, and users cannot compare reports side-by-side. Any changes or fixes to DAX logic or report visuals require updates in multiple places.

Hardcoded summary tables

Alternatively, we could load pre-aggregated data directly from the source system, hardcoded at load time. This might be in a data processing pipeline outside of Power BI, or it might be within Power Query transformations. But this is a brittle approach - the moment the underlying measure logic changes (someone fixes a measure definition or corrects historical data), the hardcoded summary becomes stale and needs re-calculating.

The recommended pattern: DAX calculated tables

A DAX Calculated Table is a table defined in the Power BI data model using DAX expressions. It is computed once, at model refresh time, and stored in the model. Visuals and measures then reference it like any other table.

The key advantage here is timing - because the calculated table is computed at refresh time, not at query time, RLS filters do not apply to it. Every user sees the same calculated table. But if we design the table to contain only aggregated data (averages, totals at a safe hierarchy level, counts), we expose nothing that should be confidential.

The pattern works like this:

1. We identify the hierarchy levels in our data that are safe to share across all users (region, global etc.).
2. We define a calculated table that aggregates measures to those safe levels only. The sensitive detail (individual stores, individual transactions) never appears in the table.
3. We write measures that reference this table for comparison. These measures are used in visuals alongside the standard RLS-filtered data.
4. The result is a report where each user sees their own data in full, plus comparative benchmarks that are appropriate for their role.

This helps to unlock comparative analysis without compromising security. The business logic lives in the existing measures, so the model stays maintainable, and the aggregation is refreshed automatically with the rest of the model.

An example: Sales by store, region, and global

Let's make this concrete using the same retail scenario.

The data model

We have a Sales fact table with:

• StoreID, RegionID, SalesAmount, Date

And a Store dimension with:

• StoreID, StoreName, RegionID, RegionName

The RLS role "Region Manager" applies a filter rule to the Store table: RegionID = [UserRegion], where UserRegion is a parameter passed from the application context.

We also have a Total Sales measure, defined as:

Total Sales = SUM(Sales[SalesAmount])

The calculated table

Now we define a calculated table called AggregatedBenchmarks. This table will contain regional averages and a global average, computed at refresh time:

AggregatedBenchmarks = 
UNION(
    -- Regional averages
    SELECTCOLUMNS(
        VALUES(Store[RegionName]),
        "Level", "Region",
        "Label", Store[RegionName],
        "Average Sales", CALCULATE(
            AVERAGEX(VALUES(Store[StoreID]), [Total Sales]),
            ALLEXCEPT(Store, Store[RegionName])
        )
    ),
    -- Global average
    ROW(
        "Level", "Global",
        "Label", "All Regions",
        "Average Sales", CALCULATE(
            AVERAGEX(VALUES(Store[StoreID]), [Total Sales]),
            ALL(Store)
        )
    )
)

This table is computed once at model refresh time. Because RLS filters do not apply at refresh time, ALL() and ALLEXCEPT() work as expected - they can traverse the full dataset to produce correct aggregates for every region and the global total.

However, AggregatedBenchmarks is not itself protected by RLS. Without additional security, every user can query every row in the table, meaning a regional manager would see the averages for all other regions, not just their own. Whether this is acceptable depends on your requirements.

If regional averages are considered sensitive, apply an RLS filter to AggregatedBenchmarks that restricts each user to their own region's row plus the global row:

[Level] = "Global" || [Label] = LOOKUPVALUE(Store[RegionName], Store[RegionID], [UserRegion])

If regional averages are not considered sensitive (i.e. only store-level transaction detail is), then no additional RLS on the calculated table is needed, and all users can see all benchmark rows. This is often acceptable and is part of what makes the pattern useful: aggregated benchmarks can be shared safely, even when the underlying detail cannot.

The full data model looks like this:

flowchart TD
    subgraph Facts["Fact Tables"]
        Sales["**Sales**<br>StoreID<br>RegionID<br>SalesAmount<br>Date"]
    end

    subgraph Dimensions["Dimension Tables"]
        Store["**Store**<br>StoreID<br>StoreName<br>RegionID<br>RegionName"]
    end

    subgraph Calculated["Calculated Tables — computed at refresh"]
        AggBench["**AggregatedBenchmarks**<br>Level<br>Label<br>Average Sales"]
    end

    RLS1["RLS Filter — Store<br>RegionID = UserRegion"]
    RLS2["RLS Filter — AggregatedBenchmarks<br>Level = 'Global'<br>OR Label = UserRegionName"]

    Sales -->|"many-to-one (StoreID)"| Store
    RLS1 -->|"filters at query time"| Store
    Store -.->|"aggregated at refresh<br>(RLS does not apply)"| AggBench
    RLS2 -->|"filters at query time"| AggBench

The comparison measures

We can now write two simple measures that read directly from AggregatedBenchmarks:

Regional Average Sales = 
CALCULATE(
    MAX(AggregatedBenchmarks[Average Sales]),
    AggregatedBenchmarks[Level] = "Region"
)

Global Average Sales = 
CALCULATE(
    MAX(AggregatedBenchmarks[Average Sales]),
    AggregatedBenchmarks[Level] = "Global"
)

Both measures read from the pre-computed table rather than recalculating from the underlying data - this is the critical distinction.

N.B. For Regional Average Sales, the measure depends on the RLS filter on AggregatedBenchmarks being in place. With that filter applied, each user sees only their own region's row at Level = "Region", so MAX returns the correct single value. Without it, the measure would return the highest regional average across all regions rather than the current user's. If you chose not to apply RLS to AggregatedBenchmarks, this measure would need to be rewritten to identify the user's region explicitly (for example, using LOOKUPVALUE to match on the user's region). For Global Average Sales, the Level = "Global" filter targets the single pre-computed global row, which is always visible regardless of the user's RLS role.

The final report

A complete working example of this pattern is available to download from GitHub.

endjin/pbi-rls-calculated-tables

The report is in the Power BI project format (.pbip) and can be opened directly in Power BI Desktop (November 2023 or later). It uses hardcoded sample data - nine stores across three regions (North, South, and East), so no data source connection is required.

Three RLS roles are defined, one per region:

• North Region Manager — restricts to Store A, Store B, and Store C
• South Region Manager — restricts to Store D, Store E, and Store F
• East Region Manager — restricts to Store G, Store H, and Store I

Each role also applies the corresponding filter to AggregatedBenchmarks, so users see only their own regional average alongside the global average.

To explore the report, open it in Power BI Desktop and use Modelling → View as to switch between roles. Without any role applied, all nine stores are visible and all three regional averages appear in the benchmark table - this represents the admin or unrestricted view.

Switching to the South Region Manager role restricts the store table to the three South stores. The Regional Average Sales card updates to reflect the South average (£40,000), while the Global Average card remains unchanged (£50,000). The benchmark table shows only the South row and the global row - the North and East regional averages are filtered out.

The North and East roles behave identically, scoped to their respective stores and regional averages.

The store table and benchmark cards are driven by entirely separate parts of the model - the former by RLS-filtered data from Sales and Store, the latter by the pre-computed AggregatedBenchmarks table. This separation is what makes the pattern work.

When to apply this pattern

This pattern works well when:

• Your hierarchy has clear safe levels. There is a well-defined aggregation level (region, country, global) where data can be shared across users without exposing sensitive detail.
• Your measures are stable and worth reusing. Business logic lives in one place. If the Total Sales definition changes, the calculated table automatically reflects the updated logic at next refresh.
• Different users have genuinely different data rights. The pattern is most valuable when RLS creates a real visibility gap - users who need comparative context but cannot see the underlying detail.
• Real-time comparison is not a requirement. Benchmarks are computed at model refresh. Daily or weekly refresh cycles are typically sufficient for this kind of analysis.
• Your hierarchy is likely to grow. Adding a new level (business unit, product category) requires only a small addition to the UNION() call. The pattern extends naturally without restructuring the model.
• You need benchmarks for multiple metrics. Additional aggregated measures are added by including new columns in the ADDCOLUMNS() calls. All benchmarks live in a single table, keeping the model coherent and the benchmark logic easy to find.

This pattern is less suitable when:

• Your hierarchy is highly dynamic. If new levels or branches are added frequently, maintaining the calculated table definition becomes overhead.
• Your data model is already very large. Every aggregated row is stored in the model. In high-cardinality, multi-dimensional hierarchies, the calculated table can grow quickly.
• The aggregation logic diverges from your measures. If the benchmarks require substantially different calculation logic from your existing measures, the reuse benefit is lost and the pattern adds complexity without a clear return.

Summary

Row Level Security in Power BI is essential for controlling who sees what. But security constraints often come at the cost of comparative insight. Users locked into their own data cannot easily benchmark themselves against the wider dataset.

DAX Calculated Tables offer a clean solution. By pre-aggregating data to safe hierarchy levels at refresh time, we expose only what we intend to share, and we reuse existing business logic to do it. The pattern is straightforward to implement, maintainable, and scales well to typical organisational structures.