Model overview

Everything lives in one table. Each row carries a data type saying what it describes, and the visual reads the columns that matter for that type.

Why one table

An org chart needs several kinds of thing — billets, reporting lines, groups, memberships, people, assignments — and a Power BI visual receives exactly one table. So the source unions them and tags each row with its type.

The practical consequence is worth understanding early: a filter drops rows uniformly. If a slicer removes the GROUP and MEMBER rows that hold a triad together, the triad stops being a box and its billets scatter. That is why scope and organization are tagged on every data type, not just on billets.

Data types

Type	Describes	Key columns
`NODE`	A billet or an organization box	id, label, kind, echelon, uic
`EDGE`	A reporting line in one chain	parentId, childId, chain, authority
`GROUP`	A drawn box: triad, department, staff codes	id, label, role (layout), parentId (anchor), childId (nesting)
`MEMBER`	Which billets sit inside a group	id (group), childId (node), sortOrder
`ASSIGNMENT`	A person holding a billet for a period	parentId (node), childId (person), effectiveFrom, effectiveTo
`ROSTER`	A person who could hold a billet	id, label, kind (personnel_type), code/uic/uicTitle/echelon/officeCode (5 UICs)
`STYLE`	A named colour scheme	id, styleJson
`CHART`	One chart definition: root, spine, bands	id, parentId (root), chain (spine)

Billets and people are separate

A NODE is a billet — a role that exists whether or not anyone fills it. A ROSTER row is a person. An ASSIGNMENT joins them for a period of time.

Keeping them separate is what makes the useful questions answerable: which billets are gapped, how long the current holder has been in post, who held it before, and who is available to fill it.

Billets, tours and people are three separate things joined over time.

Groups: three ways to contain

A group is a drawn box. Billets get inside it, and groups get inside each other, by three different mechanisms — all of which the visual follows:

Mechanism	Set by	Meaning
Membership	`MEMBER` rows	This billet is drawn inside this box
Nesting	Group's `childId`	This box is drawn inside that box
Anchoring	Group's `parentId`	This box hangs beneath that billet

A region's triad is nested in the region; the region's detachments are anchored to the region commander. Both are "inside the region" to a reader, and roll-up totals follow both.

Dates and the as-of view

Edges and assignments carry effectiveFrom and effectiveTo. The visual draws the chart as of a date — today by default. A reorganization that takes effect next quarter can sit in the data now and appear when it happens.

PRD is not an end date. A projected rotation date is a plan, not a fact. Put it in the details column, never in effectiveTo — an assignment whose effectiveTo has passed is over, so filing PRDs there silently empties the chart of everyone whose rotation date has come and gone.

Building the table in Power Query

The usual shape is one query per data type, each producing the same columns, unioned at the end:

let
    Nodes       = Table.AddColumn(SourceBillets,    "dataType", each "NODE"),
    Edges       = Table.AddColumn(SourceReporting,  "dataType", each "EDGE"),
    Groups      = Table.AddColumn(SourceGroups,     "dataType", each "GROUP"),
    Members     = Table.AddColumn(SourceMembership, "dataType", each "MEMBER"),
    Assignments = Table.AddColumn(SourceTours,      "dataType", each "ASSIGNMENT"),
    Roster      = Table.AddColumn(SourcePeople,     "dataType", each "ROSTER"),
    Combined    = Table.Combine({Nodes, Edges, Groups, Members, Assignments, Roster})
in
    Combined

Filter the roster upstream. Power BI hands a visual at most 30,000 rows across all data types. Restrict the roster to the commands a given report covers — that is what the UIC columns are for. See limits.