Why Power BI’s relationship auto-detect fails on architecture data

Leaders increasingly want architecture answers in Power BI: which systems are in scope for a regulation, what depends on a platform being retired, who owns the risk. The architect who builds that report is responsible for the numbers in it. Today that usually means loading tables from the repository into Power BI Desktop and accepting the relationships it proposes. By default, Power BI proposes relationships between related tables without being asked. For a sales model that is usually a convenience. For architecture data it is a hazard, because nothing in the interface tells you when it has gone wrong. The report looks finished.

We measured it on the Westbrook Bank reference model on October 2, 2026. Microsoft’s documentation (opens in a new tab) describes autodetect as working from column names and skipping matches it cannot make with confidence. Here is what happened on architecture data.

What we measured

These are figures from the Westbrook Bank reference model, a fictitious model we use for teaching. They illustrate the shape of the problem, not what you should expect from your repository.

Twenty-eight relationships invented from shared values

Two tables got related because both happened to contain a classification value, such as a criticality rating. Nothing in the architecture says those tables are connected. But Power BI treats a relationship as a path for filters, so a selection on one visual can quietly change the numbers on another through a link that means nothing. Architecture repositories reuse small vocabularies of criticality, lifecycle and status across many kinds of element, which gives this failure plenty of material.

The relationships that mattered were not detected

The reason to put architecture data in a BI tool is to ask what depends on what. That needs the relationships that support graph traversal, and auto-detect did not find them at all. This is structural. Architecture data is a graph. A dependency record points at an element twice, as source and as target, and elements point at other elements through hierarchy. Walking those links takes deliberately defined relationships and measures that traverse them, written and checked against the source. A column-matching heuristic is not a substitute.

One correct relationship, silently off

Power BI allows only one active relationship between two tables. When automatic detection meets competing paths, it makes one active and deactivates the rest, and Microsoft’s own documentation notes that the automatic choice can be wrong. On the reference model, a correct relationship was switched off because two paths competed, and nothing flagged it. In architecture data, competing paths are normal: a source and a target, or a direct link and an indirect one, between the same pair of tables.

The usual patch makes it worse

When a visual does not respond to a selection, the common fix is to set the relationship’s cross filter direction to Both. Microsoft’s documentation warns against that on patterns with loops, because the filter then has more than one way to travel and the result is ambiguous. Architecture models are full of loops.

Why it matters

Architecture data feeds decisions: which systems are in scope for a regulation, what is safe to retire, who owns the risk. A wrong relationship does not announce itself as an error. It shows up as a plausible number, which is the kind that gets repeated in a steering committee before anyone checks it.

Spotting it in your own model

A few signs are visible in Model view without any tooling.

• A table with relationship lines to many others for no business reason.
• Two tables joined only through a classification column.
• Dashed lines, which mark inactive relationships you did not make inactive.
• Totals that do not move when you filter, or that move when you did not expect them to.

What to do about it

1. Turn it off. In Power BI Desktop, under Options and Data Load, clear “Autodetect new relationships after data is loaded.”
2. Read every line in Model view. For each relationship, say in one sentence what it means for the architecture. If you cannot, delete it.
3. Set active and inactive on purpose. Decide which of two competing paths should be the default, and write down why.
4. Test against a dependency you know. Pick one application whose downstream dependencies you can trace by hand in EA, and confirm the traversal measure returns the same list. If it returns the right count for the wrong reason, you will not see it in a chart.
5. Build traversal as measures. Plan them, and verify them against the source.

The AI-augmented way: generate the model, then check it

The better route is not to infer the model at all. The relationships in your architecture data are already defined by your modeling language, so a Power BI model can be generated from it, with relationships defined deliberately and traversal measures written and verified against the repository. The architect’s part changes from wiring relationships by hand to checking the generated model: reading Model view, testing a dependency they know, and deciding what to do about the data-quality problems the build reports. Generating the model does not fix data quality. It shows you where the gaps are.

If your organization has Kernaro AI Hub, we can help you use its Power BI connector, the Kernaro Connector, which is the preferred route, and the same checking applies. If not, we can build a reporting database on top of your Sparx EA repository. If you host the repository yourself, the reporting views sit in the same database and you read from them live. If you cannot reach the database, or it is hosted elsewhere, we help you set it up in something like Snowflake or Microsoft Fabric and refresh it from your EA model on the schedule you define. Power BI then reads the model that AI Power Tools for Sparx EA, free from NovoCircle, generates from the reporting database. The reporting and Power BI page sets out how we help and compares the routes.

Open Model view and read it before anyone reads a report.

Questions people ask