AI

Power BI RLS and Fabric Data Agent considerations

Posted by Jens Vestergaard on in Community 1 Comment

In the last post I walked through auditing a semantic model before connecting it to an AI tool like Fabric Data Agent. Descriptions, naming, explicit measures, star schema: the things that decide whether a Fabric data agent generates an accurate query or a confident wrong one. I left one thing out on purpose, because it deserved its own post and because I got it wrong the first time I thought about it.

Security.

Here is the setup. I inherited a model. It had row-level security on it. Sales people saw their own region, the finance role saw everything, and the reports had been running like that for two years without a complaint. So when the request came in to connect a data agent to the workspace, I ticked the security box in my head and moved on. RLS was handled.

It was handled for people browsing reports. That is not the same thing as handled for an agent answering questions over the model, and the gap between those two sentences is where the trouble lives.

What I got wrong first

My first assumption was the dramatic one: the agent runs as some service identity, ignores the report layer, and reads everything. That is wrong, and the reason it is wrong is the useful part.

A Fabric data agent honors the security on the underlying data. Microsoft is explicit: the agent honors all user permissions to the data, including row-level security and column-level security, and for a Power BI semantic model the querying user only needs Read permission on the model, not workspace access, as documented in Fabric data agent sharing and permission management. The data agent concepts page repeats it in the limitations section: access through a data agent is governed by Read permission, and RLS and CLS still apply. So the agent is not a backdoor. It queries as the person asking the question, and that person’s RLS role filters the rows.

Good. So where is the problem? The problem is everything that decides what “the person’s RLS role” actually evaluates to. RLS is only as strong as the identity model around it, and that is where I had two real holes.

Hole one: RLS only binds Viewers

This is the one that catches people, and it caught me.

Row-level security only restricts users with the Viewer role in the workspace. It does not apply to Admins, Members, or Contributors. Those three roles carry edit permission on the semantic model, and a user who can edit the model can read all of it. Microsoft says it plainly in Row-level security (RLS) with Power BI: if you want RLS to apply to someone in a workspace, the only role you can give them is Viewer. Even a Viewer with Build permission still gets filtered. A Contributor does not.

Now connect that to the agent. The agent filters by the asking user’s identity. If the people allowed to ask the agent questions sit in the workspace as Contributors, because that is how the workspace was set up back when it was three developers and no governance, then RLS evaluates to “see everything” for every one of them. The agent is behaving correctly. It is honoring RLS. RLS just happens to be off for those identities.

The same rule applies to object-level security. OLS only binds Viewers too, as the object-level security documentation states in its limitations. The Admin, Member, and Contributor roles see the secured tables and columns regardless.

So the report side and the model side diverge exactly here. A Contributor browsing a report might still look safe, because the report only contains the visuals someone built. Point that same Contributor’s identity at the model through an agent that can write arbitrary DAX, and there is no report layer left to hide behind.

Hole two: service principals sit outside RLS entirely

The second hole is worse, because it fails silently in both directions.

Service principals cannot be added to an RLS role. The consequence, in Microsoft’s own words, is that RLS is not applied for apps using a service principal as the final effective identity. That is point three in the RLS considerations and limitations. The XMLA endpoint documentation says the same thing from the other side: service principals do not work with RLS and OLS and cannot be added as model role members.

This matters the moment any part of the chain runs as a service principal rather than a signed-in user. Automation, an embedded app, a fixed-identity Direct Lake model, a pipeline that calls the model. If the effective identity is a service principal, static RLS is simply not applied, and dynamic RLS is worse than not applied: USERPRINCIPALNAME() and USERNAME() return the application ID or an empty string for a service principal, so a filter written as [Region] = USERPRINCIPALNAME() matches nothing, or matches the wrong thing, depending on how the model was built. You either leak everything or you hand back silently incorrect slices, and neither failure announces itself.

So before connecting anything, I now need to know exactly what identity the thing on the other end runs as. “It uses RLS” is not an answer. “It queries as the signed-in user, who is a Viewer, whose role filters to their region” is an answer.

Hiding a column is not securing it

This one is subtle and I want to be careful with it.

When you set a column to hidden in the model, you remove it from the field list. You have not secured it. A hidden column is still queryable. Microsoft makes the equivalent point about perspectives, which are also just a curated view: a user can still query a table or column even when it is not visible to them, so a perspective is a convenience, not a security feature. That note lives in the report consumer security planning guidance. The hidden flag is the same kind of convenience. The agent generating DAX is not limited to your field list. It reads the model metadata, and hidden objects are still part of the model.

The actual controls are object-level security and column-level security. OLS removes the table or column for a role so completely that, to that role, it does not exist, and unlike RLS it also hides the object name and metadata, so the field cannot even be discovered. Column-level security does the same at the column grain by setting the metadata permission to none, as shown in the Analysis Services object-level security reference. You cannot author OLS in Power BI Desktop today. You set it in Tabular Editor or another external tool by setting the table or column permission to None for the role. And, to close the loop, OLS binds Viewers only, same as RLS.

So if there is a salary column, or a cost column, or anything that should not reach the agent, hiding it does nothing. Securing it with OLS or CLS does, for Viewer identities, and for no one else.

Report-level safe is not model-level safe

If there is one sentence to take from this, it is that one. Reports hide visuals. The model still answers DAX. An agent talks to the model, not to the report, so every assumption that lived in the report layer needs to be re-checked at the model layer before the agent goes live.

The check I run now

Before I connect a semantic model to a data agent, I work through this. It is short on purpose.

  1. List every RLS role and write down, in one line each, exactly what it filters. If you cannot describe a role’s filter in a sentence, you do not yet understand what the agent will return for its members.
  2. List the members of each role, and then list everyone with Admin, Member, or Contributor on the workspace. That second list is the bypass list. For those identities RLS and OLS do not apply at all. Decide whether every name on it should genuinely see all rows.
  3. Identify the agent’s effective identity. If it is a service principal or a fixed identity, RLS is not being applied, and you cannot fix that by adding the principal to a role, because you cannot add it to a role. Reach for a different pattern, such as a model whose fixed identity is itself scoped to safe data, or per-user identity passthrough.
  4. Check sensitive columns for OLS or CLS, not for the hidden flag. The hidden flag is decoration. Confirm the salary-shaped columns are actually secured, and remember that the security only binds Viewers.
  5. Test as the agent’s identity, not as yourself. The Test as role feature evaluates dynamic RLS using your own UPN, so it cannot show you what a service principal or a guest would see. Where it matters, sign in as the real identity, or run the same DAX the agent would run, as that identity, and look at the rows that come back.
  6. Default everyone who only needs to ask questions to Read on the model, nothing more. The agent needs Read, not Build, not a workspace role. Least privilege here is not a nicety. It is the only configuration where RLS actually does its job.

Where I am still unsure

I have not fully mapped how a Fabric data agent resolves identity across every sharing mode it offers. The sharing documentation is clear that the agent honors RLS and CLS and that consumers need Read on the model, and that lines up with the per-user story. What I want to test next is the boundary cases: an agent shared with a consumer who has no workspace role at all, a model on a fixed identity, and an agent invoked from automation rather than a chat window. I expect the identity that reaches the model to differ across those, and the difference is exactly what decides whether RLS holds. When I have run it, that is the next post.

Until then, the rule I am keeping is the unglamorous one. Before the agent, list the roles, list who bypasses them, find out what identity actually hits the model, and test as that identity. Not as me.

Sources

Power BI prep for AI routine

Posted by Jens Vestergaard on in Community 0 Comment

In the Fabric Ontology post from a couple of months ago, the third item on the pre-agent checklist was “build the semantic foundation first.” I moved on from that line pretty quickly. It deserved more than a sentence.

Most Power BI developers who have been in the field for a few years have inherited at least one model they did not design. It has measures with names like Amt_Adj_Net2 and FlagActCust, columns that were added for a specific report in 2021 and never cleaned up, and business logic that someone once explained verbally in a meeting. No descriptions. No documentation. The people who built it have either moved on or are too busy to explain it.

Now you are being asked to connect that model to a Fabric data agent. Or Power BI Copilot. And suddenly the question “what do these fields actually mean” has a hard deadline.

This post is a checklist. It is what I would do before connecting any semantic model to an AI tool, and it works backward from how those tools actually read your model.

How AI agents read your semantic model

When a Fabric data agent gets a question, it does not look at your data. It reads your metadata. The DAX generation tool builds queries from the model schema, descriptions, synonyms, relationships, data types, and the configuration you set in Prep data for AI, then validates and executes the query against the model. Microsoft documents this query flow in the semantic model best practices for data agent guide.

Copilot in Power BI works the same way. The principle generalises: a natural-language-to-query tool sitting on top of a model reads the metadata, not the data.

If your metadata is clear, the AI generates accurate queries. If it is ambiguous or missing, the AI guesses. And when an AI guesses about business logic, the answers look correct but frequently are not.

The audit checklist

1. Explicit measures over implicit aggregation

An implicit measure is what you get when a report visual or Q&A aggregates a numeric column on the fly. The number is calculated by the consuming layer, not defined as a calculation in the model. An explicit measure is a DAX expression you write and store in the model.

Microsoft’s own guidance for the Fabric data agent is direct on this: relying on implicit measures leads to unpredictable results, so you should create explicit DAX measures for the calculations you want users to query, and set the correct default summarization on numeric columns to prevent unintended aggregations. The optimize your semantic model for Copilot page makes the same point: include a set of predefined measures that users are most likely to request.

Go through every numeric column and ask: is there an explicit DAX measure for the business calculation it feeds? If not, and it matters for the questions the AI will receive, create one. This is the highest-priority item on the list. A model that leans on implicit aggregation will produce inconsistent answers from any AI tool regardless of how well everything else is documented.

2. Names that mean something

Go through every table name, column name, and measure name. For each one, ask: could a new developer understand what this is from the name alone, with no other context?

SalesAmount is fine. Amt is not. IsActiveCustomer is fine. Flag1 is not. Date on a calendar table is fine. D is not.

Microsoft lists non-descriptive naming as a common pitfall for the data agent, using exactly these kinds of examples: object names like TR_AMT, F_SLS, or DIM_GEO_01 give the DAX generation tool no context. Use clear, business-friendly names such as Total Revenue, Sales, or Customer Geography.

Rename anything that requires decoding. Yes, this will break existing reports if you rename columns, so do it carefully and test. If you genuinely cannot rename an object, make sure its description and synonyms carry the context instead. But an AI that can’t interpret TrxTypCd will give you wrong answers, and changing the name now is cheaper than debugging wrong outputs later.

3. Descriptions on measures

The description field on a measure is passed directly to the AI as part of its schema context. A measure without a description forces the agent to infer meaning from the DAX expression alone.

For a measure that calculates CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, YEAR)), the DAX is interpretable. For a measure that calls four other measures and has a name like Adj_Rev_Cy, it is not.

Use the TMDL triple-slash syntax if you are working in PBIP format:

/// Net revenue adjusted for returns and currency differences, current year
measure 'Adj Rev CY' = ...

If you are working in Power BI Desktop, the description field is in the properties pane.

Keep the description tight. In the DAX query view grounding path, Microsoft documents that measure descriptions are truncated after the first 200 characters when passed to Copilot as context. Front-load the meaning in the first sentence rather than burying it under a paragraph of caveats.

For a model with fifty or more measures and no descriptions, this is a significant amount of work. Power BI has a built-in Copilot button to generate measure descriptions from the DAX formula, which is a reasonable shortcut for a first pass. In a follow-up post I will cover that workflow and its limitations in more detail.

4. Hide what should not be queryable

Surrogate keys, flag columns, internal technical fields. If a column has no business meaning and is not needed for navigation, hide it. Hidden columns are invisible to AI tools. Fewer columns in the schema means less noise for the AI and fewer opportunities for a wrong-turn query.

A model with 180 visible columns where 120 of them are source system artefacts is harder for an AI to navigate than a model with 60 columns where everything is intentionally exposed.

5. Verify the star schema structure

Fact tables should contain measurements and foreign keys. Dimension tables should describe entities. Microsoft is explicit that DAX is optimised for star schema with clear fact and dimension tables, and lists flat or denormalised structures as a pitfall for the data agent. If you have many-to-many relationships, bidirectional filter behaviour, circular references, or deeply nested snowflake structures, the AI will not interpret them the way you intend.

This does not mean every unusual pattern is wrong. It means you need to know where they are, understand what they do, and either clean them up or document them in the AI instructions (see item 6 below).

Role-playing dimensions deserve specific attention. If your Date table participates in three relationships (order date, ship date, due date) and only one is active, the AI will use the active one by default for every date-related question unless you tell it otherwise. Microsoft’s guidance for ambiguous date fields is to use verified answers and AI instructions to specify which date field to use for each type of question.

6. Add AI instructions to the model

Power BI has a feature called AI instructions. It is free-text guidance that Copilot interprets, authored through the Prep data for AI experience in Power BI Desktop or in the semantic model settings in the Power BI service. The model needs Power BI Q&A turned on before the Prep data for AI tabs become available. Use AI instructions to:

  • Define domain-specific acronyms and abbreviations that appear in your field names
  • Direct the AI toward the correct measure for each type of question
  • Note any calculation quirks around fiscal year, currency conversion, or time intelligence
  • Explain role-playing dimension behaviour

From a product perspective this is metadata you configure, not a file you hand-edit. PBIP and TMDL projects may surface it as a project artifact on disk, but Microsoft documents the feature as AI instructions and the supported authoring path is the Prep data for AI surface. One caveat worth knowing for the data agent specifically: the DAX generation tool only reads the AI instructions you set in Prep data for AI, not instructions added at the data agent level, so keep model-specific guidance in Prep data for AI.

7. Scope the AI data schema

Alongside AI instructions, the Prep data for AI experience has a Simplify data schema tab where you define a focused subset of fields for Copilot to prioritise. If your model has 200 fields but only 50 are relevant for the business questions the AI will receive, scope the schema to those 50. When you include a measure, include its dependent objects too, or the generated DAX can fail.

Synonyms are a separate mechanism. If your users ask about “revenue” but the measure is called “Net Sales Amount”, you map those terms through Q&A linguistic modelling, not through the AI data schema. Both feed the same goal of reducing ambiguity, but they are configured in different places.

8. Mark the model as Approved for Copilot

Once the above steps are complete, mark the semantic model as Approved for Copilot in the Power BI service settings. The setting was previously called “prepped for AI”. Marking it removes the friction warnings that standalone Copilot shows on answers from models that have not been approved, and reports built on that model inherit the approval.

It is also a signal to your organisation that this model has been intentionally prepared for AI use.

What to do with the findings

This audit will produce a list. Some items are quick (renaming a column, hiding a key). Some are not (creating 40 explicit measures for a model that has been running on implicit ones for three years without anyone noticing).

Prioritise by impact. Start with the tables and measures the AI will actually touch. You do not need to fix the entire model before connecting anything to an AI tool. You need to fix the parts that will be queried.

The second thing this audit tends to reveal: there is no one who knows what most of these measures actually calculate. The descriptions you need to write require conversations with people who may no longer be at the company. That is a different problem to solve. But knowing you have it is better than finding out after the AI agent gives the board a wrong number.

When you work out how to describe a measure you did not write, and no one who wrote it is available, the answer is in the next post.

Sources

The Future of BI: Will AI Replace BI Developers?

Posted by Jens Vestergaard on in Programming 0 Comment

I have been asked this question at every conference I have attended in the last two years. Not always directly. Sometimes it arrives as “what do you think about Copilot” or “is there still a point learning DAX properly.” But the underlying question is always the same: is my job going to exist in five years?

It is a fair question. When you watch GitHub Copilot complete a CALCULATE function before you have finished typing the first argument, or paste a business requirement into Claude and get back a working Power Query transformation, it is easy to understand why people are asking it.

Here is my honest take, as someone who has been building BI solutions since 2006 and who has spent the last year testing these tools in real work, not in demos.

—

What These Tools Can Actually Do Today

I have been using ChatGPT, Microsoft Copilot and Claude regularly over the last eighteen months, in actual client work and personal projects. Not in theory. In my editor, on real data models, with real business requirements.

ChatGPT is strong at generating DAX and SQL when you give it enough context. If you describe the table schema, the business logic and the expected output format, the first attempt is often close enough to use with minor adjustments. I have used it to draft measures I would have spent an hour on, in five minutes. It is not always right on the first pass, but it moves fast enough that iteration costs less than starting from scratch.

Microsoft Copilot inside Fabric and Power BI has improved noticeably over the last year. The report creation assistant went from generating generic placeholder visuals in early 2024 to producing layouts I would actually take and refine for production use in 2025. It will not replace design judgment, but it removes the blank canvas problem. For report creation at scale, that matters.

Claude has been the most useful for reasoning about model design. I used it to reverse-engineer a semantic model from an annotated schema diagram and it handled the relationships and measure dependencies better than I expected. I also hit real limits: it had no knowledge of my actual data, made assumptions that were wrong for our domain, and I needed five or six iterations before the output was usable. The hiccups were real. I am not smoothing those over.

The pattern that holds across all three: if the task is well-defined, the context is fully provided, and the output can be verified quickly, these tools are fast and genuinely useful. That describes a significant portion of the day-to-day coding work in a typical BI role.

Where They Still Fall Short

None of these tools know your business.

That sentence sounds simple, but it is where the real gap sits in practice. Generating a CALCULATE function is not difficult once you know the filter context. The difficult part is knowing that in your organization, “active customers” means accounts with at least one transaction in the last 90 days, but only in the consumer segment, and that this definition changed in Q2 2024 and needs to be handled differently across historical and current period comparisons.

That context lives in your head, in Confluence pages nobody reads, in a meeting that happened eighteen months ago, and in an email from a finance analyst who has since left the company. No AI tool picks that up from a prompt. You bring it, or it does not exist in the output.

The same gap shows up in data quality judgment. AI tools will generate a transformation pipeline from a spec without questioning whether the spec is correct. They will not notice that the order date column has 8% null values, or that this matters for the revenue calculation, or that the exceptions exist because of a legacy system migration that your company did in 2019. You notice, because you have seen the data before and know what those patterns mean.

Report creation has the same ceiling. The AI can build a layout, suggest a chart type and write a title. It cannot make the call that this dashboard will be shown on a 40-inch screen in a warehouse and that the font size from the default template will be unreadable from three meters away. That judgment comes from having sat with the people who use the reports.

What This Actually Means, by Role

The impact is not uniform. It depends on how much of your current working week involves mechanical repetition.

For BI developers, the most immediate change is in code generation. Boilerplate DAX, repetitive ETL transformations, standard report templates: these are exactly the tasks AI accelerates most. If this work is a large part of your week, your output volume will increase and expectations around it will rise with it. That is not a threat if you understand it early enough to stay ahead of it.

For data analysts, the change is most visible in exploration speed. Asking Claude or Copilot to produce a first-pass analysis of a dataset, flag anomalies or suggest groupings gets you to a hypothesis faster. The interpretation of that hypothesis, and the judgment about whether it is the right hypothesis for the actual decision being made, remains yours.

For data engineers, pipeline boilerplate generation and schema documentation are straightforward wins. Debugging complex transformation failures where the error is opaque is an area where these tools also provide real value, particularly if you can paste the full stack trace and table definitions into the prompt.

For data architects, the tools work well as thinking partners for structured design questions. Talking through a proposed model, generating documentation drafts, checking naming conventions across a schema. The decisions about what to govern, where domain boundaries sit, and how to design for the organizational reality rather than the theoretical ideal still require judgment that comes from knowing the context.

For data governance specialists, the upside is in documentation and lineage drafting. The real governance work, defining what quality means for a specific data product and making that stick across teams, is still a people and process problem that no AI tool solves for you.

My Perspective as a Practitioner

I am not going to tell you AI will not change BI work. It already has. But the question of whether it replaces BI developers wholesale is the wrong framing.

The more useful question is: which parts of your current work are mechanical enough that AI tools can do them faster and cheaper? Be honest about that list. If it is long, that is information worth having now rather than in two years when the market has already adjusted.

Here is what I have observed across the work I do and the people I talk to at conferences: the work that clients are most anxious, most uncertain, and most willing to pay for is not the part AI is good at. It is the part that requires knowing their business, understanding who trusts what report and why, having the conversation with the finance director who distrusts the new model, and standing next to the operations manager while they explain what the dashboard actually needs to show.

That is still practitioner work. AI does not do it. It accelerates the technical work that happens around it, which gives you more time for the part that matters most.

There is one shift I have started to notice though, and it is worth naming. The finance director on the other side of the table is also using these tools. They arrive at the meeting having already asked Claude to explain the variance, or having had Copilot summarize the dashboard. They come in with a better baseline understanding than they had two years ago, and that makes for a more productive conversation. You spend less time on mechanics and more time on the actual decision. That is a good thing, not a threat.

The hallucination problem is real and it is the most reasonable objection anyone raises to trusting AI output in production work. But it is also improving faster than most people expected a year ago. The gap between what these tools got wrong in early 2024 and what they get wrong now is measurable. I expect that to continue. The sensible approach is to verify outputs where the stakes are high and to track how often you have to correct them over time. That number has been going down in my experience.

The BI developers I have watched get uncomfortable recently are the ones who built careers around syntax knowledge, template maintenance and format conversion. Those jobs are genuinely changing. The developers who are doing well are the ones who understood that the syntax was never the point: the business problem was the point, and tools that help with the syntax give them more time for the problem.

My advice is straightforward: learn the tools, use them on real work rather than tutorial datasets, and find out where they fail on your actual data in your actual domain. That is the only way to know where your judgment matters more than their suggestion.

For me, the answer to whether AI will replace BI developers is: not the ones who are clear about what they are actually being hired to do.

What has your experience been with AI tools in your daily BI work? I would like to know what people are actually finding useful versus what sounds better in a conference session than it works in practice.