ER Diagrams from DAX — Document Your Power BI Data Model with a Single Measure
How to generate a live entity-relationship diagram for any Power BI Semantic Model using a DAX user-defined function and the free Markdown / Mermaid Renderer visual.
published on 2026-04-14
In a previous article, I introduced the Markdown / Mermaid Renderer — a free, open-source Power BI visual that renders Markdown content and Mermaid diagrams directly inside a report. This article builds on that foundation with a concrete use case: automatic ER diagrams for Power BI data models.
Power BI Desktop does have a built-in relationship view: the Model View. But anyone who has worked with it on a larger model knows its limitations — it is not configurable, becomes slow with many tables, and quickly turns into an unreadable tangle of lines. There is no way to filter by active relationships, hide columns, or embed the result in a report for others to see.
This article presents an alternative: a single DAX user-defined function that queries the model metadata, produces a Mermaid ER diagram, and renders it live inside the report — configurable, lightweight, and always up to date.
The Idea
Since April 2024, Power BI Desktop ships with INFO.VIEW.* functions that expose model metadata at query time — tables, columns, measures, relationships. Combined with DAX user-defined functions (UDFs), introduced with compatibility level 1604, this metadata can be transformed into structured text directly inside the model.
The approach:
flowchart LR
A["INFO.VIEW.RELATIONSHIPS()"] --> B["DAX UDF: mermaid_relationships"]
C["INFO.VIEW.COLUMNS()"] --> B
B --> D["Mermaid erDiagram Code"]
D --> E["Markdown / Mermaid Renderer"]
E --> F["Live ER Diagram in Report"]INFO.VIEW.RELATIONSHIPS()returns every relationship defined in the model — from/to table, from/to column, cardinality, cross-filter direction, active/inactive status.INFO.VIEW.COLUMNS()returns all columns with their data types.- A DAX UDF called
mermaid_relationshipscombines both, formats the output as a MermaiderDiagramcode block, and returns a single text string. - The Markdown / Mermaid Renderer visual renders that string as an interactive SVG diagram.
The result: a live, always-up-to-date entity-relationship diagram inside the Power BI report — one that updates automatically whenever the model changes.
What You Get
The generated diagram shows:
- Every table in the model — including isolated tables that have no relationships
- Columns used as relationship keys (or optionally all columns, with data types)
- Cardinality: many-to-one (
}o--||), one-to-one (||--||), many-to-many (}o--o{) - Active vs. inactive relationships: solid lines (active) vs. dotted lines (inactive)
- Cross-filter direction: bidirectional relationships are marked with
⇄
A small example of the rendered output:
erDiagram
Calendar {
DateTime Date
}
Products {
Int64 ProductKey
}
Sales {
DateTime OrderDate
Int64 ProductKey
}
Sales }o--|| Calendar : "OrderDate → Date"
Sales }o--|| Products : "ProductKey → ProductKey"With the Markdown / Mermaid Renderer visual, this can be explored interactively: fullscreen mode with zoom and pan is available for complex models.
The DAX Code
The UDF mermaid_relationships accepts two parameters:
| Parameter | Options | Effect |
|---|---|---|
ShowColumns |
"No_Cols" |
Entity names only — clean, minimal diagram |
"Key_Cols" |
Show columns used as relationship keys (with data types) | |
"All_Cols" |
Show every column of every table | |
ShowRelationships |
"All_Rels" |
Include active and inactive relationships |
"Active_Rels" |
Only active (solid-line) relationships |
Full DAX Query
Copy the entire block below into DAX Query View in Power BI Desktop and execute it. The result is a single cell containing the Mermaid code block.
After execution, right-click the measure definition in the query and select "Add to model" to persist it as a measure.
DEFINE
// Helper: replace Mermaid-unsafe characters with underscores.
FUNCTION mermaid_safe = (txt) =>
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
txt,
" ", "_"), "(", "_"), ")", "_"), "-", "_"), ".", "_"),
",", "_"), "'", "_"), "/", "_"), "\", "_"), ":", "_"),
";", "_"), "[", "_"), "]", "_"), "+", "_"), "=", "_"),
"%", "_"), "&", "_"), "#", "_"), "@", "_"), "!", "_")
FUNCTION mermaid_relationships =
(ShowColumns, ShowRelationships) =>
// ShowColumns : "No_Cols" | "Key_Cols" | "All_Cols"
// ShowRelationships: "All_Rels" | "Active_Rels"
VAR _NL = UNICHAR(10)
// ------------------------------------------------------------------
// Guard: validate both input parameters.
// ------------------------------------------------------------------
VAR _ValidInput =
(ShowColumns = "No_Cols" || ShowColumns = "Key_Cols" || ShowColumns = "All_Cols")
&& (ShowRelationships = "All_Rels" || ShowRelationships = "Active_Rels")
// ------------------------------------------------------------------
// Base relationships — filtered by ShowRelationships.
// ------------------------------------------------------------------
VAR _BaseRels =
FILTER(
INFO.VIEW.RELATIONSHIPS(),
ShowRelationships = "All_Rels" || [IsActive] = TRUE()
)
// ------------------------------------------------------------------
// Step 1: Add derived columns to each relationship row.
// ------------------------------------------------------------------
VAR _Rels =
ADDCOLUMNS(
_BaseRels,
// ER connector (active, solid line)
"Connector",
SWITCH(
TRUE(),
[FromCardinality] = "Many" && [ToCardinality] = "Many", "}o--o{",
[FromCardinality] = "One" && [ToCardinality] = "One", "||--||",
"}o--||" // Many-to-One or One-to-Many
),
"IsActiveBool", [IsActive] = TRUE()
)
VAR _RelsWithConnector =
ADDCOLUMNS(
_Rels,
"FinalConnector",
IF(
[IsActiveBool],
[Connector],
SUBSTITUTE([Connector], "--", "..")
),
// Label: optional "⇄ " prefix + "FromColumn → ToColumn"
"Label",
VAR _arrow = UNICHAR(8594) // →
VAR _bidir = UNICHAR(8644) // ⇄
VAR _base = [FromColumn] & " " & _arrow & " " & [ToColumn]
RETURN
IF(
[CrossFilteringBehavior] = "BothDirections",
_bidir & " " & _base,
_base
)
)
// ------------------------------------------------------------------
// Step 2: Add Mermaid-safe node IDs for [FromTable] and [ToTable].
// ------------------------------------------------------------------
VAR _RelsWithSafeIDs =
ADDCOLUMNS(
_RelsWithConnector,
"FromSafe", mermaid_safe([FromTable]),
"ToSafe", mermaid_safe([ToTable])
)
// ------------------------------------------------------------------
// Step 3: Column data from INFO.VIEW.COLUMNS() (only when needed).
// ------------------------------------------------------------------
VAR _KeyColPairs =
DISTINCT(
UNION(
SELECTCOLUMNS(_BaseRels, "TN", [FromTable], "CN", [FromColumn]),
SELECTCOLUMNS(_BaseRels, "TN", [ToTable], "CN", [ToColumn])
)
)
VAR _AllViewCols =
SELECTCOLUMNS(
INFO.VIEW.COLUMNS(),
"TN", [Table],
"CN", [Name],
"CT", [DataType]
)
VAR _ColsToShow =
FILTER(
_AllViewCols,
ShowColumns = "All_Cols" || CONTAINSROW(_KeyColPairs, [TN], [CN])
)
// ------------------------------------------------------------------
// Step 3b: Isolated tables — tables that appear in
// INFO.VIEW.COLUMNS() but are not part of any (filtered)
// relationship. Rendered as standalone entity blocks.
// ------------------------------------------------------------------
VAR _RelTableNames =
DISTINCT(
UNION(
SELECTCOLUMNS(_BaseRels, "TN", [FromTable]),
SELECTCOLUMNS(_BaseRels, "TN", [ToTable])
)
)
VAR _AllTableNames =
DISTINCT(SELECTCOLUMNS(_AllViewCols, "TN", [TN]))
VAR _IsolatedNodes =
SELECTCOLUMNS(
EXCEPT(_AllTableNames, _RelTableNames),
"NodeSafe", mermaid_safe([TN]),
"NodeLabel", [TN]
)
// ------------------------------------------------------------------
// Step 4: Node table — safe ID + original name, one row per table.
// Includes tables from relationships AND isolated tables.
// ------------------------------------------------------------------
VAR _NodeTable =
DISTINCT(
UNION(
SELECTCOLUMNS(_RelsWithSafeIDs, "NodeSafe", [FromSafe], "NodeLabel", [FromTable]),
SELECTCOLUMNS(_RelsWithSafeIDs, "NodeSafe", [ToSafe], "NodeLabel", [ToTable]),
_IsolatedNodes
)
)
// ------------------------------------------------------------------
// Step 5: Node block lines.
// ------------------------------------------------------------------
VAR _NodeLines =
CONCATENATEX(
_NodeTable,
VAR _tSafe = [NodeSafe]
VAR _tLabel = [NodeLabel]
VAR _colLines =
CONCATENATEX(
FILTER(_ColsToShow, [TN] = _tLabel),
" " & mermaid_safe([CT]) & " " & mermaid_safe([CN]),
_NL,
[CN]
)
RETURN
IF(
ShowColumns = "No_Cols" || ISBLANK(_colLines),
" " & _tSafe,
" " & _tSafe & " {" & _NL & _colLines & _NL & " }"
),
_NL,
[NodeLabel]
)
// ------------------------------------------------------------------
// Step 6: Edge lines.
// ------------------------------------------------------------------
VAR _EdgeLines =
CONCATENATEX(
_RelsWithSafeIDs,
" " & [FromSafe] & " " & [FinalConnector] & " " & [ToSafe] & " : """ & [Label] & """",
_NL
)
// ------------------------------------------------------------------
// Step 7: Assemble the fenced Mermaid code block.
// ------------------------------------------------------------------
// Isolated tables get a subtle dashed-border style so they
// visually stand out from tables that participate in relationships.
VAR _IsolatedClassAssignment =
CONCATENATEX(_IsolatedNodes, [NodeSafe], ",", [NodeLabel])
VAR _StyleLines =
IF(
ISBLANK(_IsolatedClassAssignment),
"",
_NL &
" classDef isolated fill:#f5f5f5,stroke:#bbb,color:#666" & _NL &
" class " & _IsolatedClassAssignment & " isolated"
)
RETURN
IF(
NOT _ValidInput,
ERROR("mermaid_relationships: ShowColumns must be ""No_Cols""/""Key_Cols""/""All_Cols"", ShowRelationships must be ""All_Rels""/""Active_Rels"""),
"```mermaid" & _NL &
"erDiagram" & _NL &
_NodeLines & _NL &
_EdgeLines &
_StyleLines & _NL &
"```"
)
// Adjust arguments as needed:
// ShowColumns : "No_Cols" | "Key_Cols" | "All_Cols"
// ShowRelationships: "All_Rels" | "Active_Rels"
EVALUATE
{ mermaid_relationships("Key_Cols", "Active_Rels") }How It Works
The function follows these steps:
- Filter relationships — either all or only active ones, depending on the
ShowRelationshipsparameter. - Map cardinality to connectors —
Many-to-Onebecomes}o--||,One-to-Onebecomes||--||, and so on. Inactive relationships get dotted connectors (}o..||). - Sanitise identifiers — Mermaid does not allow spaces or special characters in entity names. The helper function
mermaid_safereplaces 20 common special characters with underscores. - Collect column metadata — from
INFO.VIEW.COLUMNS(), filtered to key columns or all columns depending on theShowColumnsparameter. - Detect isolated tables — tables that exist in the model but have no relationships (or none matching the current filter). These are included as standalone entity blocks with a subtle visual style.
- Build entity blocks — one block per table (including isolated ones), optionally listing columns with their data types.
- Build relationship lines — one line per relationship, labelled with the key column names and direction.
- Assemble — wraps everything in a fenced Mermaid code block (
```mermaid ... ```), ready for the visual to render. Isolated tables receive a dedicated CSS class for visual distinction.
Setup in Power BI
Prerequisites
- Power BI Desktop (April 2024 or later) — required for
INFO.VIEW.*functions - Compatibility Level 1604 or higher — required for DAX user-defined functions
- Markdown / Mermaid Renderer visual — available for free from AppSource or GitHub
Step-by-Step
- Open your
.pbixfile in Power BI Desktop - Open DAX Query View (View → DAX Query View)
- Paste the full DAX query from above and execute it
- Right-click the
mermaid_relationshipsfunction definition → "Add to model" - Create a new measure that calls the function, for example:
ER Diagram =
mermaid_relationships("Key_Cols", "Active_Rels")- Place the Markdown / Mermaid Renderer visual on a report page
- Drag the
ER Diagrammeasure into the Markdown Content field
The diagram renders immediately. From this point on, it stays in sync with your model — add a table, change a relationship, and the diagram updates on its own.
Choosing the Right Display Mode
Both parameters can be combined to tailor the diagram to the model's size and the level of detail required.
The three column modes serve different purposes:
| Mode | Best for | Diagram complexity |
|---|---|---|
"No_Cols" |
Quick overview, presentations, large models (20+ tables) | Minimal |
"Key_Cols" |
Development & review — see which columns form the joins | Moderate |
"All_Cols" |
Full documentation, small models (< 10 tables) | High |
The relationship filter adds another dimension:
| Filter | Effect |
|---|---|
"Active_Rels" |
Only active relationships — cleaner diagram, especially when inactive relationships add visual noise |
"All_Rels" |
Active (solid lines) + inactive (dotted lines) — complete picture of all defined relationships |
For large models, a good starting point is "No_Cols" with "Active_Rels" — minimal clutter, focus on the core model structure. Use the visual's fullscreen mode (⛶) to zoom and pan. Switch to "Key_Cols" or "All_Rels" when you need to inspect specific join paths or debug inactive relationships.
What Makes This Lightweight
There is no Python script, no external tool, no exported metadata file. The entire workflow lives inside the Power BI model:
- One DAX function reads the model metadata
- One measure calls that function with the desired parameters
- One visual renders the result
The diagram is live. It does not go stale. It does not require someone to remember to re-export a screenshot after a model change. It updates every time the report is opened or refreshed.
This makes it ideal for:
- Ad-hoc documentation during development — quickly see what the model looks like
- Onboarding — hand over a report with a built-in model overview
- Review sessions — discuss relationships and cardinality directly in the report
- Presentations — show a clean, interactive architecture diagram
Going Further: The Markdown / Mermaid Renderer's Flexibility
The ER diagram is just one use case. Because the visual renders arbitrary Markdown and Mermaid, the same approach works for other diagram types driven by DAX:
- Flowcharts — visualise data pipelines or ETL logic
- Sequence diagrams — show refresh dependencies or API call sequences
- Gantt charts — display project timelines from data tables
- Measure documentation — use
INFO.VIEW.MEASURES()to generate a full measure reference (see the companion article)
Multiple measures can be combined in a single visual — place the ER diagram alongside a measure reference, a process description, or KPI definitions. Each measure is rendered as its own section.
When You Need More
The DAX approach is intentionally lightweight. It works well for quick overviews and self-documenting reports. But it has limits:
- No table descriptions or column-level documentation (the metadata functions do not expose them)
- No lineage tracking (where does each column come from?)
- No formatting control beyond what Mermaid offers
- No versioned history or change tracking
For teams that need comprehensive, structured documentation — with descriptions, lineage, calculation logic, data types, and quality checks — Fennibi generates complete Markdown documentation for Power BI Semantic Models. It uses the same Mermaid diagrams for relationships and architecture, displayed through the same visual, but with far more depth and detail.
The DAX function in this article is a great starting point. When the scope grows, Fennibi picks up where it leaves off.
💬 Join the Discussion
Have questions, ideas, or feedback about the Mermaid Power BI Visual or the introduced dax function? I'd love to hear from you — feel free to leave a comment on the LinkedIn post for this article.