Skip to content
← Back to Blog

2026-04-14 11 min read

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"]
  1. INFO.VIEW.RELATIONSHIPS() returns every relationship defined in the model — from/to table, from/to column, cardinality, cross-filter direction, active/inactive status.
  2. INFO.VIEW.COLUMNS() returns all columns with their data types.
  3. A DAX UDF called mermaid_relationships combines both, formats the output as a Mermaid erDiagram code block, and returns a single text string.
  4. 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:

  1. Filter relationships — either all or only active ones, depending on the ShowRelationships parameter.
  2. Map cardinality to connectors — Many-to-One becomes }o--||, One-to-One becomes ||--||, and so on. Inactive relationships get dotted connectors (}o..||).
  3. Sanitise identifiers — Mermaid does not allow spaces or special characters in entity names. The helper function mermaid_safe replaces 20 common special characters with underscores.
  4. Collect column metadata — from INFO.VIEW.COLUMNS(), filtered to key columns or all columns depending on the ShowColumns parameter.
  5. 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.
  6. Build entity blocks — one block per table (including isolated ones), optionally listing columns with their data types.
  7. Build relationship lines — one line per relationship, labelled with the key column names and direction.
  8. 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

  1. Open your .pbix file in Power BI Desktop
  2. Open DAX Query View (View → DAX Query View)
  3. Paste the full DAX query from above and execute it
  4. Right-click the mermaid_relationships function definition → "Add to model"
  5. Create a new measure that calls the function, for example:
ER Diagram =
    mermaid_relationships("Key_Cols", "Active_Rels")
  1. Place the Markdown / Mermaid Renderer visual on a report page
  2. Drag the ER Diagram measure 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.