Semantic Model

Semantic models: design, build, refresh, review

Guidance for designing, building, refreshing, and reviewing Power BI / Analysis Services tabular models from the terminal. It consolidates model review with modeling best practices, and routes every change to the narrowest capable tool. Depth lives in references/; this file is the operating loop and the routing.

When to use

• Designing or building a model: star schema, relationships, measures, calculation groups, roles, parameters, storage modes, incremental refresh
• Optimizing a model: size / VertiPaq, DAX correctness, Direct Lake, refresh cost
• Reviewing or auditing a model against quality, performance, and best-practice standards
• Preparing a model for Copilot / AI consumption

When NOT to use

• Editing report visuals, pages, or formatting: use the pbir-cli skill (reports plugin)
• Isolated DAX query performance tuning: use the dax skill
• TMDL file syntax mechanics: use the tmdl skill (this skill routes to it for the file-edit fallback)
• The te command surface itself: the te-cli skill (tabular-editor plugin) is the command reference; this skill is the modeling judgment layered on top

Tool cascade (the core operating rule)

Reach for the narrowest capable tool, in order. Most edits never leave step 1.

1. `te` CLI first. One verb per operation, staged in memory until --save, with a save-time DAX + referential-integrity gate. Covers add/set/rm/mv for measures, columns, relationships (Sales[K]->Dim[K] shorthand on te add), roles + RLS filters, calculation groups / items, incremental-refresh policy, te format, te bpa, te vertipaq, te query. Each Bash call is a fresh shell, so pass -m <model> (and -s/-d for remote) on every command, or set TE_SESSION. Read the real object's settable surface first with te get <obj> and te set <obj> -q <prop> (no value). The te-cli skill is the full command reference. 2. TOM, or a model MCP, when `te` cannot reach a property. Some properties are absent from te set -q (for example alternateOf, securityFilteringBehavior, crossFilteringBehavior, KPI sub-objects, linguistic-schema content, calendar objects). Drive these through a te script C# pass (in-process TOM), or the connect-pbid skill (PowerShell + TOM/ADOMD against a live local Desktop instance, and the only route to traces: EVALUATEANDLOG, aggregation-hit events, storage DMVs). The Power BI Modeling MCP server is also available if you prefer an MCP. The local Desktop proxy cannot reach Direct Lake; use a remote XMLA endpoint there. 3. `fab` + direct TMDL last, with the `tmdl` skill. Service- and file-shape operations with no model-edit verb: assigning Entra principals to roles (workspace-side, not in .tmdl), report-to-model binding, Copilot-folder features (AI instructions, AI data schema, verified answers), Lakehouse / Delta reshaping behind Direct Lake, and bulk structural surgery that is cleaner as one TMDL diff than N te calls. Author the TMDL with the tmdl skill, then run te validate.

Ordering gate: add relationships before any measure that uses RELATED() or a cross-table CALCULATE(), or the save gate fails with DAX0002 (no relationship in context).

Lifecycle

Design

Model dimensionally: a star of fact plus conformed dimensions beats snowflakes and fact-to-fact joins. Decide storage mode and refresh strategy before building; both are near one-way doors once published. See references/dimensional-modeling.md, references/storage-modes.md, references/composite-models.md, and references/direct-lake.md.

Build

Make each change through the cascade above. Author measures with full metadata (DisplayFolder, FormatString, Description) in one pass. Validate after every mutation (te validate) and gate on BPA (te bpa run --fail-on error). Renaming or moving any object can silently break downstream reports and models; run the lineage check first, then propagate with pbir-cli / fabric-cli (see references/refactoring-renaming.md). Deep guidance per area: references/relationships.md, references/time-intelligence.md, references/calculation-groups.md, references/parameters.md, references/security.md, references/dax-authoring.md.

Refresh

Configure incremental refresh from the terminal (te incremental-refresh); for Direct Lake, the refresh is the framing. See references/incremental-refresh.md, and the refresh-semantic-model skill for monitoring and troubleshooting.

Review

Audit against the categories below and produce prioritized findings with file locations. Gather context first with scripts/get_model_info.py (storage mode, size, connected reports, endorsement, data sources, refresh schedule). Full checklist in references/review-checklist.md; performance method in references/performance.md.

Review categories (by severity)

• Critical: bidirectional ambiguity, circular dependencies, missing data types, orphaned tables, fail-open RLS, limited relationships that silently drop rows
• Memory & size: high-cardinality dictionaries, auto attribute hierarchies (isAvailableInMDX on hidden / high-cardinality columns), unsplit DateTime, auto date/time tables, wrong data types, calc columns that should be measures, unused objects
• Data reduction: unfiltered fact history (no incremental refresh), unnecessary columns, detail grain not needed for reporting, logic better pushed upstream
• DAX correctness: filtering tables not columns in CALCULATE, unguarded division, context-blind calc columns, variable time-shift bugs (references/dax-authoring.md; for query tuning use the dax skill)
• Measure hygiene: implicit measures, report-scoped measures that belong in the model, ambiguous duplicates
• Documentation & AI: missing descriptions (Copilot truncates after 200 characters), missing display folders, missing synonyms, inconsistent naming (use standardize-naming-conventions)
• Design: star-schema violations, mis-marked date table, many-to-many without a bridge, dead inactive relationships
• Direct Lake: non-unique one-side keys (queries fail at runtime), DirectQuery fallback, calc columns on Direct Lake, Delta guardrail breaches

Related skills

• tmdl: TMDL file authoring (the cascade's step-3 fallback)
• dax: DAX query performance optimization
• connect-pbid: TOM / ADOMD via PowerShell against a live Desktop instance; traces; the TOM / MCP tier
• te-cli: the te command reference
• c-sharp-scripting: TOM C# scripts and macros (te script) for properties te cannot reach
• standardize-naming-conventions: naming audit and remediation
• refresh-semantic-model: refresh monitoring and troubleshooting
• lineage-analysis: artifact lineage (downstream reports and models that consume this model, across workspaces); distinct from intra-model object dependencies
• bpa-rules (tabular-editor): authoring BPA rules; fabric-cli: service / workspace operations

Reference map

references/dimensional-modeling.md:    star schema, SCD2, junk / degenerate dimensions, header-detail, bridges
references/relationships.md:           cardinality, limited relationships, ambiguity, active / inactive, USERELATIONSHIP
references/time-intelligence.md:       classic vs calendar TI, mark-as-date traps, week-based / 4-4-5
references/calculation-groups.md:      precedence, sideways recursion, selection expressions, the variant trap
references/parameters.md:              field parameters, what-if parameters, dynamic titles
references/security.md:                RLS validation + defensive filters, bidirectional + RLS, OLS restrictions
references/query-semantic-model.md:    querying a model with DAX, INFO functions, output formats, probing
references/storage-modes.md:           Import / DirectQuery / Dual / Hybrid decision matrix
references/composite-models.md:        source groups, regular vs limited, Direct-Lake-plus-Import
references/aggregations.md:            user-defined aggregations, grain, te-script AlternateOf
references/direct-lake.md:             OneLake vs SQL, framing, DirectQuery fallback, guardrails
references/incremental-refresh.md:     IR policy, detect data changes, hybrid / real-time, refresh strategy
references/vertipaq-optimization.md:   VPA metrics, value vs hash encoding, splitting high-cardinality keys
references/dax-authoring.md:           variable semantics, DIVIDE, measure vs calc column (gaps vs the dax skill)
references/ai-copilot-readiness.md:    the Copilot grounding contract, synonyms / linguistic schema, descriptions, Q&A retirement
references/metadata-and-organization.md: descriptions, display folders, naming, measure tables, perspectives
references/hierarchies-cultures.md:    user hierarchies, parent-child, KPIs, perspectives, cultures / translations
references/documentation-and-bpa.md:    data dictionary, BPA documentation gate, metadata diffs, intra-model impact analysis (artifact lineage = the lineage-analysis skill)
references/refactoring-renaming.md:    safe rename workflow (lineage check first, then propagate via pbir-cli / fabric-cli)
references/review-checklist.md:        full audit checklist with remediation
references/performance.md:             performance testing, unused-column detection, memory analysis
scripts/get_model_info.py:             model metadata overview (mode, size, reports, endorsement, sources, refresh)

What this skill deliberately leaves out

• GUI-tool walkthroughs and the old review skill's "use whatever tool is available" framing: superseded by the te-cli-first cascade
• Hand-authored Q&A phrasings: Q&A is being retired and Copilot does not read phrasings; invest in synonyms and descriptions
• Perspectives or display folders presented as security: both are queryable by anyone with model access; use RLS / OLS
• The PBIT format: out of scope

User-defined aggregations

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: build the agg table with te-native verbs (te add table, hide columns), then map AlternateOf via te script (TOM) since te set -q does not expose it. Audit the >= 10x grain ratio with te query; confirm cache hits via a trace (connect-pbid / DAX Studio), not te query alone.

The cascade decision rule and the user-defined-aggregation example

A triage rule before any modeling edit, so the agent reaches for the narrowest capable tool instead of defaulting to TMDL text-editing (re-implementing what te does atomically and losing the save-time DAX/RI gate). The full mapping is the Tool cascade in SKILL.md; the operational anchors: most ops have one te verb; ops with no te -q (user-defined aggregations / AlternateOf, OLS metadataPermission if rejected, relationship crossFilteringBehavior/securityFilteringBehavior, KPI sub-objects, calendar objects, linguistic metadata) drop to te script TOM; service/file-shape ops (Entra role membership, report binding, Copilot folder, Lakehouse reshaping, bulk TMDL surgery) go to fab + TMDL.

User-defined aggregations are the textbook te script escape hatch: build the agg table and hide it with te-native verbs, then map columns via TOM (new TOM.AlternateOf { Summarization = TOM.SummarizationType.Sum, BaseColumn = ... }), then te validate. The agg is a hidden coarser-grain pre-aggregate of a large DQ detail table that the engine transparently rewrites qualifying subqueries to; keep it at least ~10x fewer rows than its detail or maintenance outweighs the speedup, and strip high-cardinality attributes from the GroupBy set. Relationship-based aggs (the agg relates to the same dimensions) make GroupBy entries optional except DISTINCTCOUNT, which needs an explicit GroupBy on the key; GroupBy-based aggs (denormalized, no relationships) require them or the agg never hits. RLS must filter both agg and detail or the engine refuses to answer from the agg; detailTable must be DirectQuery and must point at the real detail (chained aggregations are illegal); hybrid tables and Direct Lake (either flavor) do not support user-defined aggregations (pre-aggregate in the Lakehouse instead).

Sources: repo te-cli command-reference / gotchas / SKILL / semantic-modeling-practices; learn.microsoft.com aggregations-advanced / SummarizationType; learn.microsoft.com composite-model-guidance; learn.microsoft.com aggregations-auto

AI and Copilot readiness

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: set descriptions with te set <obj> -q description -i "..." --save, kept under 200 characters (Copilot truncates there). Bulk-audit missing or over-long descriptions with a te script C# pass over Model.AllMeasures / AllColumns; synonyms and linguistic-schema content go through te script (TOM) or TMDL.

The Copilot grounding contract (what metadata actually reaches the LLM)

When a user asks Copilot or the DAX query view a natural-language question, a fixed documented set of metadata is serialized as grounding context. What gets sent: the full schema (tables, columns, measures, relationships, calculation groups ; hidden objects included except on a live connection to a shared model); synonyms from the linguistic schema; per object the DAX expressions, descriptions (truncated after the first 200 characters), data types, format strings (including format-string expressions), and data category; min/max values of columns likely used in the query (actual data points); sometimes the query result echoed back to explain the answer. Excluded: conversation history on Retry.

Implications: the 200-char truncation is a hard budget ; front-load disambiguating keywords, anything past char 200 is human-tooltip only. isHidden does not remove an object from Copilot grounding on import/composite models (only a live connection to a shared model hides them) ; hiding declutters the human field list but does not shrink the AI surface (use an AI data schema for that). Format strings and data category steer answer rendering and intent, not just display (a WebUrl category or currency format changes how Copilot frames the result). Min/max of candidate columns are real data points ; sensitive extremes are a data-exposure consideration. Audit description length and fix truncation-prone descriptions in one model load with a te script C# pass over Model.AllMeasures/AllColumns where Description.Length > 200; set a single description with te set <obj> -q description -i "..." --save. Do not assume hidden = invisible to AI; a measure with a keyworded description grounds far better than one relying on a clear name alone (Copilot leans on description keywords for discovery).

Sources: learn.microsoft.com copilot-semantic-models; learn.microsoft.com tutorial-copilot-power-bi-prepare-model; repo te-cli SKILL property table

Synonyms and the linguistic schema (where they live and how to write them)

Synonyms are alternate words a user might use ("revenue" for Sales Amount, "client" for Customer). They are not a plain property like displayFolder; they live in the model's LinguisticMetadata, attached to a culture, serialized as LSDL (.lsdl.yaml), and in a PBIP project also land in <Name>.SemanticModel/Copilot/schema.json. The classic Q&A linguistic schema additionally carries phrasings (relationship verbs, measurement/dynamic adjectives, noun phrasings). Synonyms are explicitly in the Copilot grounding contract and survive the Q&A deprecation, so they are high-return; phrasings are Q&A-engine constructs largely irrelevant to Copilot, so hand-authoring them is low-return.

te has no first-class synonym verb (no te set -q synonyms); the supported path is a TOM C# script through te script writing to the culture's LinguisticMetadata.Content (a JSON document ; ensure a culture exists, round-trip the content through a JSON parser, inject under Entities -> entity -> Terms, validate, then save ; edit as structured JSON, not string concatenation, or you corrupt it and the save gate may not catch a semantically-broken-but-syntactically-valid doc). connect-pbid reaches the same Culture.LinguisticMetadata object on a live Desktop instance. Direct LSDL/Copilot-folder editing is last: export the .lsdl.yaml (Modeling > Linguistic schema > Export), edit, re-import; or edit Copilot/schema.json. Generated entries carry State: Generated ; on re-import anything Generated is ignored and regenerated, so to keep an edit strip the tag, and to suppress a generated entry set State: Deleted. Synonyms are culture-scoped, so a model with no culture has nowhere to put them; after deploying LSDL/Copilot edits the service needs a refresh to sync (import on deploy; DirectQuery/Direct Lake once per day).

Sources: learn.microsoft.com q-and-a-tooling-advanced / -intro; repo pbip copilot-folder; repo c-sharp-scripting translations

Prep-data-for-AI: four distinct features, where each persists, and deploy gotchas

"Prep data for AI" is an umbrella over four non-interchangeable features; conflating them is the common mistake:

• descriptions ; natural-language context per object, used in DAX queries + Copilot search/discovery; stored as a standard property; only the first 200 chars feed AI
• AI data schema ; selects a subset of fields Copilot prioritizes, the only feature that genuinely shrinks the AI surface (hiding a field does not exclude it from Copilot); stored in Copilot/schema.json; ignored by report-page summaries, "create a report page," search, and DAX-query Copilot (those pull the whole model); requires Q&A enabled or the Prep-data tabs grey out
• verified answers ; map trigger phrases to a specific pinned visual as the canonical answer; stored in Copilot/VerifiedAnswers/... (PBIR)
• AI instructions ; free-text business context steering Copilot (not a guaranteed instruction-follow); stored in Copilot/Instructions/instructions.md; 10,000-char limit; Copilot-only

All four save to the semantic model, never the report. The AI data schema is the only feature that narrows what Copilot reasons over and is frequently confused with hiding; you cannot fix "Copilot uses the wrong column" by hiding it (still grounded) or an AI instruction alone (non-binding) ; the deterministic fix is the AI data schema plus a verified answer. None has a te verb today, so the cascade inverts here: fab + direct folder editing is the practical path (edit the markdown/JSON in the PBIP project, deploy with fab); te-cli still owns the prerequisites (description quality, star-schema/dedup cleanup, unique field names, removing unused objects, resolving ambiguous relationships ; do those first). Deploy gotchas: after a Git/pipeline deploy a service refresh is required to sync (import on deploy; DirectQuery/Direct Lake once per day max); editing the AI data schema needs the Copilot pane closed and reopened to see the effect; "Approved for Copilot" is a separate final toggle that removes the friction-warning banner for the model and reports on it (propagation usually under an hour, up to 24h on models with many reports; force it by saving a trivial report change). Only the model can be approved, never a report/dashboard/app.

Sources: learn.microsoft.com copilot-prepare-data-ai-faq / -data-schema / copilot-prepare-data-ai / tutorial-copilot-power-bi-prepare-model; repo pbip copilot-folder

Q&A is retiring: what to stop investing in vs keep

Microsoft is retiring the Power BI Q&A experience and steering users to Copilot. This re-prioritizes the AI-readiness backlog. Stop or deprioritize: hand-authored Q&A phrasings (Attribute, Name, measurement/dynamic adjectives, Noun), adjective tuning for "long rivers"/"red products" questions, and the Q&A setup-menu and "review questions users asked" loop ; these are Q&A-engine constructs Copilot does not consume. Keep high-value: synonyms (in the Copilot grounding contract, survive deprecation); descriptions (<= 200 char, keyworded ; universal, feed Copilot + DAX-query + search); format strings, data category, data types (part of the contract); the AI data schema, verified answers, AI instructions (the forward-looking Copilot toolset). Caveat: the AI data schema currently requires Q&A enabled on the model, so do not pre-emptively disable Q&A while you still depend on AI-data-schema authoring. Investing in the full Q&A linguistic schema for AI readiness targets a feature being retired; direct those hours at the keep list.

Sources: learn.microsoft.com q-and-a-tooling-intro / -advanced; powerbi.microsoft.com deprecating-power-bi-qa; learn.microsoft.com copilot-prepare-data-ai-data-schema

Calculation groups

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: te add calculationGroup "Time Intelligence" --save, then te add calculationItem .... Read / set group precedence with te set <group> -q precedence; selection expressions, selectionExpressionBehavior, and variant guards that te set does not expose go through te script (TOM).

Calculation group precedence: how items actually combine

When two calc groups have an item in filter context at once, the engine nests them: each item's SELECTEDMEASURE() token is textually replaced by the next-lower-precedence item's DAX, down to the base measure. The group with the highest precedence integer is the outermost wrapper. The output is order-dependent and rarely commutative ; a high-precedence SELECTEDMEASURE()*2 over a lower SELECTEDMEASURE()+2 on measure=10 gives ((10)+2)*2 = 24, not 14. When a higher-precedence item uses CALCULATE/context transition, it rewrites the filter context the inner item sees (Time Intelligence at higher precedence makes YTD wrap both numerator and day-count denominator of an average). Precedence also decides whose dynamic format string wins (only the highest group's applies; a measure's own dynamic format is always lower than any calc group).

Precedence lives on the group (not per-item ordinal, which is only within-group sort order ; do not confuse them). Inspect with te set <group> -q precedence before setting; assign distinct integers when groups co-occur in one visual, or apply order is undefined. If te set cannot reach it, fall to a te script C# pass (CalculationGroupPrecedence) or edit the precedence: line in TMDL last. A calc item only modifies an expression containing a measure reference; with no SELECTEDMEASURE() in scope it is a no-op.

Sources: learn.microsoft.com calculation-groups (precedence); repo SpaceParts Z04CG1 Time Intelligence.tmdl; repo te-cli semantic-modeling-practices

Calculation groups: sideways recursion (the only supported recursion)

A calc item can reference another item in the same group by overriding that group's column inside CALCULATE ; the one recursion form the engine permits. YOY% is built from the YOY and PY items (DIVIDE(CALCULATE(SELECTEDMEASURE(), 'Time Intelligence'[Time Calculation]="YOY"), CALCULATE(SELECTEDMEASURE(), ...="PY"))) rather than re-deriving them; a PY YTD item layers PY on top of the already-defined YTD item. This is composition for calc items: define YTD/PY/YOY once, then build derived items from them, and changing the base propagates. Each DIVIDE branch is a separate CALCULATE that re-enters the group cleanly.

Use the column's quoted full name inside the item DAX with the item name as a string literal ; this is the one place the literal is unavoidable, and the rename-safe ISSELECTEDMEASURE guidance does not apply (it is about measure references). So renaming a base item still requires a find-replace across dependent items. Only sideways recursion works; an item recursing into itself, or applying the same group twice in one CALCULATE, errors or is silently ignored (one item per group in filter context at a time). Nesting two overrides of the same group column in one CALCULATE collapses to the last filter.

Sources: learn.microsoft.com calculation-groups (sideways recursion, single-item-in-filter-context)

Calculation groups: selection expressions and selectionExpressionBehavior

Two optional group-level DAX properties handle non-clean selections: multipleOrEmptySelectionExpression fires on multi-select, a nonexistent item, or a conflict; noSelectionExpression fires when the group is unfiltered. Each carries its own formatStringDefinition. Default when undefined: the group does not filter (base measure passes through); a single valid selection never triggers either. A model-level selectionExpressionBehavior (Automatic = today's nonvisual, or visual) tunes the default; above a future compat level Automatic resolves to visual.

Without multipleOrEmptySelectionExpression, a user multi-selecting calc-group items gets the unfiltered base measure with no signal ; a common "why is the number wrong" ticket. Define it to return a deliberate value (BLANK() with a ---style format) or block ambiguous selections; noSelectionExpression makes a "Current" default. These are group properties (confirm casing first); if te set does not expose them, use te script (MultipleOrEmptySelectionExpression, SelectionExpressionBehavior) or TMDL. Never put normal logic in these (they never fire on a single valid selection); each needs its own formatStringDefinition or it inherits a mismatching default (returning BLANK() but inheriting currency shows a stray symbol); selectionExpressionBehavior=visual changes existing report numbers, so baseline key measures before and after.

Sources: learn.microsoft.com calculation-groups (selection expressions)

Calculation groups: hard limitations and the variant-data-type trap

Adding any calc group flips a model-wide switch. Unsupported alongside calc groups: OLS on the calc-group table; RLS on the calc group itself (use the data tables); Detail Rows Expressions; Smart Narrative; implicit column aggregations (the sigma options appear but cannot apply unless discourageImplicitMeasures=true, which removes them cleanly). In Live Connection, dynamic format strings are not applied to report-level measures.

The variant trap: the moment a calc group exists, Power BI treats every measure as the variant type. This silently breaks any dynamic format string that reuses another measure's value, and breaks visuals when an item runs arithmetic on a non-numeric measure (dynamic titles, text measures): Cannot convert value ... of type Text to type Numeric. Removing all calc groups reverts measures to their real types. This is the most surprising side effect in a review because the failure surfaces in report visuals, not the model. Two fixes, in order: guard arithmetic items with IF(ISNUMERIC(SELECTEDMEASURE()), ...), or coerce a reused format-string measure with FORMAT([Dynamic format string], ""); the preferred long-term fix moves shared format-string logic into a DAX UDF, sidestepping the coercion (dovetailing with the UDF-over-calc-group guidance). When reviewing a model with calc groups, proactively check text/title measures and measure-reusing dynamic format strings.

Sources: learn.microsoft.com calculation-groups (limitations, considerations); learn.microsoft.com desktop-dynamic-format-strings; repo te-cli semantic-modeling-practices

Composite models

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: inspect each table mode and source group with te script (TOM Partitions[0].Mode); promote shared dimensions to Dual to keep cross-table joins regular. Confirm no path went limited with te query -q "EVALUATE INFO.VIEW.RELATIONSHIPS()" after edits.

Composite-model tuning: source groups, regular vs limited, Direct-Lake-plus-Import

A composite partitions tables into source groups (the Import/Direct-Lake cache is one; each DQ source is its own). Performance and even correctness depend on whether a query stays inside one group. The runtime branches four ways, only the first three fast: (1) Import/Dual only -> cache; (2) Dual + DQ from the same source -> few native queries, regular relationships; (3) Dual/Hybrid + DQ same source -> cache for import partitions, native for the rest; (4) anything cross-source-group -> limited relationships everywhere in that path, groupings shipped as materialized subqueries, and rows dropped when keys don't match across groups (an RI gap becomes a silent wrong total).

The single-source regular-relationship rule: many-side Dual needs one-side Dual; many-side Import needs Import or Dual; many-side DirectQuery needs DirectQuery or Dual. Cross-source regular only when both tables are Import; m2m is always limited. The exception worth knowing: a true composite of Direct Lake on OneLake + Import supports regular relationships across the two, unlike classic DQ+Import (limited only) ; so a billion-row Delta fact in Direct Lake plus small Import dimensions avoids the cross-group penalty. Direct Lake on SQL does not support this; convert to OneLake first.

Tuning workflow: enumerate each table's mode + bound source (te script over Partitions[0]), promote dimensions shared with a DQ fact to Dual, re-check relationship types and flag any non-Regular. For DQ source tuning the source side matters more ; ensure warehouse indexes support the emitted joins/filters/groupings (a source-DBA task, not te-cli). Keep caches in sync (the engine will not mask a Dual/import-vs-DQ gap; fix data integrity at the source). Direct Lake on SQL silently falls back to DQ for view-based tables and granular RLS, quietly turning a "Direct Lake" model into a slow DQ one ; OneLake web-modeling composites avoid this.

Sources: learn.microsoft.com composite-model-guidance; learn.microsoft.com aggregations-advanced (regular vs limited); learn.microsoft.com direct-lake-web-modeling; learn.microsoft.com direct-lake-develop; local forums DB (composite-sluggish, Direct-Lake-edit threads)

DAX authoring correctness

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: prove a rewrite by running both versions with te query across contexts and diffing scalars. Find risky patterns with te find "/" --in expressions --paths-only and te find "CALCULATE" --in expressions. Gate every change with te validate and te bpa run --fail-on warning.

DAX: variable evaluation semantics (evaluated once, where defined, lazily)

A VAR is not a macro or a cell reference. It is a named value computed at most once, in the filter+row context at the point of the VAR line, and only if a reachable branch consumes it. Three properties held simultaneously: context-at-definition, not at use (a later CALCULATE wrapping a reference reuses the already-computed scalar, the opposite of a measure which re-evaluates at invocation); computed once (materialized once, reused for every reference and context); lazy (a variable no reachable branch consumes is never evaluated).

This is the most common source of silently-wrong refactors, not slow ones. Two failure modes: (1) time-shift bug ; an agent hoists a measure call into a variable then wraps the reference in SAMEPERIODLASTYEAR/DATEADD, and the shift is a no-op because the value froze at definition. The fix is to keep the measure reference inside the CALCULATE (SQLBI: SalesPY must compute CALCULATE([Sales Amount], SAMEPERIODLASTYEAR(...)), not CALCULATE(SalesCY, ...)). (2) branch-selection bug ; an agent assumes putting two heavy expressions in variables and selecting with IF/SWITCH runs only the chosen one, but variables declared before the IF are outer-scope and both evaluate regardless. For strict per-branch evaluation, declare the VAR inside the branch expression.

Validate by execution, not static inspection: read the body, stage a rewrite, prove equivalence by querying both versions across contexts and diffing scalars, gate with te validate, then save. For branch-vs-scope inspection of intermediate values, drop to connect-pbid and EVALUATEANDLOG; te query returns only the final scalar/table, the one case the TOM/ADOMD trace path beats it. Pitfalls: a variable colliding with a column name shadows the column ; prefix with _; variables are immutable (no running-total-in-a-loop mental model ; restructure as an iterator); "wrap it in a VAR" is not a free optimization when the value is context-dependent and the wrapping CALCULATE meant to change that context.

Sources: SQLBI variables-in-dax / when-are-variables-evaluated / eager-vs-strict-evaluation; dax.guide var; repo dax-patterns (DAX003)

DAX: DIVIDE is the correctness default, bare / is the narrow exception

DIVIDE(n, d) returns BLANK() (or an optional third-arg alternate) when d is zero or blank, instead of the division error bare n / d raises. DIVIDE is the default in a measure body; the bare operator is the deliberate exception used only when the denominator is provably never zero/blank and you are inside a hot iterator. The performance guidance (DAX018: replace DIVIDE with / inside iterators to avoid the FE callback the zero-guard forces) over-generalizes in isolation into "prefer / everywhere," reintroducing divide-by-zero in ordinary scalar measures ; the anti-pattern the review skill flags as critical. The two rules reconcile once the precondition is explicit: outside iterators default to DIVIDE (a scalar runs once, the callback is irrelevant, blank-on-zero is what you want in a visual); inside an iterator switch to / only after guaranteeing a non-zero denominator by pre-filtering the iterated table (CALCULATETABLE('Items', 'Items'[Rate] <> 0)). The guarantee is not optional.

Decide by where the division sits, then validate the zero-path. Find bare-operator divisions with te find "/" --in expressions --paths-only, classify each (scalar vs inside SUMX/AVERAGEX), ensure scalar cases use DIVIDE, and for the iterator case prove the zero row is gone (FILTER('Items', 'Items'[Rate] = 0) returns empty). A BPA rule is the durable enforcement ; gate with te bpa run --fail-on warning so any future bare / in a non-iterator measure surfaces. Pitfalls: DIVIDE(n, 0, alternate) fires the alternate on blank denominators too, not just literal zero; n / 0 returns Infinity/NaN in some surfaces rather than a hard error, poisoning downstream aggregations silently (worse than an error); the blank DIVIDE returns is indistinguishable from a no-data blank if a downstream ISBLANK drives logic, so supply an explicit alternate to disambiguate.

Sources: dax.guide divide; repo dax-patterns (DAX018)

DAX: measure vs calculated column, the context-blindness decision rule

A calculated column is computed at refresh, materialized into VertiPaq, evaluated in row context only ; it cannot see report filters, slicers, or the visual. A measure is computed at query time in the filter context the visual provides. This is a correctness decision first, cost second (the cost framing ; calc columns ~4x larger ; is already covered). The deciding question: does the value need to respond to what the user filters? If yes it must be a measure ; no calc-column cleverness can read filter context. A row-fixed [Quantity] * [Unit Price] is a legitimate calc column (better, a Power Query or source column); a "% of total" / running total / "rank within selection" is inherently a measure because the denominator/window depends on what is filtered, and as a calc column it bakes in the whole-table answer and never changes as the user slices.

The frequent mistake is using CALCULATE inside a calc column to "fix" the missing context ; that gives a context transition over the table (wrong, frozen at refresh) and is the direct cause of the circular-dependency trap: CALCULATE in a calc column makes that column depend on every column in its table, so a second such column depends on the first and the model refuses to validate. This is a top forum error. The fix restricts the dependency surface to the key: wrap the filter removal in ALLEXCEPT('Table', 'Table'[PrimaryKey]), or on a keyless table use ALLNOBLANKROW (a bare ALL still creates a blank-row dependency). Inventory mis-modeled columns with te find "CALCULATE" --in expressions --paths-only | grep column; convert by adding the measure, validating, checking downstream dependents (te find "Sales[RowRevenue]" --in expressions), then removing the column. Pitfalls: "it works in my test visual" hides the bug (a calc-column percent looks right unfiltered then stays constant when sliced ; always test against a slicer selection); calc columns/tables are not materialized under DirectQuery and (mid-2026) Direct Lake (default to measures or upstream columns for any model that might move to Direct Lake); the circular-dependency error names the second column you created, not the design flaw at the first.

Sources: SQLBI understanding-circular-dependencies / avoiding-circular-dependency-errors; Fabric forums (circular-dependency threads); repo te-cli SKILL; repo te-cli semantic-modeling-practices

Dimensional modeling

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: build a dimension with te add table "Dim" --columns "Key:Int64,Attr:String" --save and relate it via te add relationship "Fact[Key]->Dim[Key]" --save. Build junk / bridge / SCD calc tables with te script (TOM CROSSJOIN / ADDCOLUMNS) when there is no source, then hide keys with te set <col> -q isHidden -i true --save.

Slowly changing dimensions (SCD2): durable keys and version-safe counting

A type-2 dimension keeps history by inserting a new row per tracked-attribute change. Each row carries a surrogate key (unique per version), a durable/natural key (stable per business entity), and validity bounds (ValidFrom/ValidTo, often IsCurrent). The fact joins on the surrogate, so each fact row points at the version correct at event time. This is the only shape where "sales by the customer's region at time of sale" and "by current region" coexist.

Two silent bugs appear the moment an SCD2 dimension exists, neither throwing an error:

• DISTINCTCOUNT('Sales'[CustomerKey]) counts versions, not customers ; a customer who moved twice counts as three. Count the durable key instead, evaluated through the fact so VertiPaq solves it in the storage engine: COUNTROWS(SUMMARIZE('Sales', 'Customer'[Customer Code]))
• Slicing by a dimension attribute gives point-in-time history by default (the fact froze the version). Authors expecting current-state grouping get wrong totals. For current-state, do not put the changing attribute on the SCD2 table at all; split it into a separate type-1 (overwrite) dimension keyed on the durable key with its own 1:M to the fact. Then "current region" and "region at time of sale" live on different tables and the author picks. A LASTNONBLANK/ValidTo IS BLANK re-derivation works but pushes cost to the formula engine

There is no model property that says "this is SCD2"; infer it. A te query probe comparing COUNTROWS(VALUES(Customer[CustomerKey])) against COUNTROWS(VALUES(Customer[Customer Code])) plus a check for Valid* columns tells you: surrogate rows greater than durable entities means history is present and every DISTINCTCOUNT over the surrogate is suspect. Enumerate offenders with a te script C# pass over Model.AllMeasures, add corrected durable-key measures, and set the surrogate IsHidden, SummarizeBy=None, isAvailableInMDX=false (high-cardinality VertiPaq hog) ; never delete it, it is the relationship key. Drop to TOM only to read ValidFrom/ValidTo contents for non-overlap validation. Note RELATED('Customer'[Region]) in a fact calc column returns the historical region ; usually correct, but it bites agents porting "current attribute" logic.

Sources: SQLBI distinct-count-of-customers-in-SCD2; SQLBI slowly-changing-dimensions-in-powerpivot; learn.microsoft.com star-schema (slowly changing dimensions)

Junk dimensions: collapsing low-cardinality flag columns

A junk dimension folds several small, independent, low-cardinality attributes (order status, ship method, yes/no flags) into one table whose rows are the Cartesian product of distinct values plus a surrogate. Three status flags with 3, 2, and 4 states collapse from three relationships and three fact FKs into one dimension of at most 24 rows and one FK ; the fact shrinks, the relationship graph simplifies, and the AI schema surface reads cleaner. The payoff is bounded by the product of distinct counts, so it only works for genuinely low-cardinality attributes ; cross two 50-value columns and you have a 2,500-row table that is no longer junk.

Build the Cartesian product upstream (warehouse view, or Power Query full-outer-joins of the distinct lists plus an index surrogate merged back onto the fact). When the source is fixed, build it as a DAX calculated table via te script (CROSSJOIN/ADDCOLUMNS of the distinct flag values, with a concatenated StatusKey), hide every column, set SummarizeBy=None, and relate on a matching computed StatusKey on the fact (1:M, single direction). A calculated-table junk dim avoids ETL but will not materialize in DirectQuery or (mid-2026) Direct Lake; push the build to the Lakehouse/warehouse for those modes. Prefer building from observed tuples over the full Cartesian when many combinations never occur, so slicers do not surface impossible pairs.

Sources: learn.microsoft.com star-schema (junk dimensions); learn.microsoft.com fabric dimensional-modeling-dimension-tables (junk dimensions)

Header-detail: carrying header-grain measures on a flattened model

Transactional sources arrive as a header (order: date, customer, store, freight, order-level discount) plus detail lines. Denormalize header attributes onto the line fact ; never relate two facts on the order number (SQLBI's benchmark: a 94M-distinct order-number join ran one query at 15x the CPU of the star, and a product filter forcing bidirectional propagation pushed a query from 6 to 17 minutes). The part that bites after flattening: header-grain measures (freight, shipping cost, a flat order fee) double-count when summed off the line fact, because the header value repeats per line.

Two correct shapes, picked by how the header measure is sliced: 1. Header value additive only over header-grain dimensions. Keep one line-grain fact and de-dup the header value per order: SUMX(VALUES('Sales'[Order Number]), CALCULATE(MAX('Sales'[Freight]))). Correct sliced by date/customer/store, and intentionally won't break down by product (freight has no product grain). Hide the raw Freight so nobody drags the SUM version onto a visual 2. Many header measures, heavily used. Keep two facts at their natural grains (a Sales Order header fact, a Sales line fact), each related to the shared conformed dimensions, never to each other. Header measures live on the header fact, line measures on the line fact; both filter correctly by shared dimensions and neither double-counts. This is "two facts, conformed dimensions," distinct from the forbidden "two facts related to each other"

Validate the de-dup with a te query comparing naive SUM(Sales[Freight]) against the SUMX form ; equal means either one line per order or freight was already allocated upstream. For the two-fact shape, confirm both facts relate only to shared dimensions via INFO.VIEW.RELATIONSHIPS(), and validate RI on both (a header customer the lines lack diverges totals). Allocating freight down to the line is a different decision that changes the number; only do it if the business wants freight attributable per product.

Sources: SQLBI header-detail-vs-star-schema-models; learn.microsoft.com relationships-one-to-one

Bridge tables as factless facts, and degenerate-dim as-table vs as-column

Two nuances the simple framing misses:

• The recommended way to relate two dimensions many-to-many is a factless-fact bridge (only the two keys, duplicates allowed) with both dimensions on the one-side and the bridge on the many-side, preferred over a native many-to-many relationship. Both produce the same filtering, but the bridge is a real table you can put RLS on, hang a weighting/allocation measure on, and read in lineage; the native m2m hides that. A native m2m is Limited, so RI violations group silently under a blank; two 1:M Regular legs surface the same blanks but are inspectable
• A degenerate dimension is not always "a hidden fact column." One degenerate attribute means a hidden fact column. Two or more correlated ones (order number and order line number) mean a separate 1:1 dimension built from a composite surrogate (OrderNumber * 1000 + OrderLineNumber), giving clean Sales Order / Sales Order Line fields while keeping the fact narrow

Build the bridge via te script as a calculated table of distinct key pairs (or load from source), hide its columns, add two 1:M relationships, and make exactly one leg bidirectional. m2m grand totals are non-additive (a salesperson in two regions contributes to both, so "All Regions" is less than the sum of parts) ; bake that into the measure Description so Copilot and authors do not read it as a bug. The degenerate-as-table only works at exactly one row per fact line with matching surrogate values both sides ; build it at fact grain, never DISTINCT. Do not reach for a bridge to fix a snowflake ; that is a normalization issue, flatten upstream.

Sources: learn.microsoft.com star-schema (factless fact tables); learn.microsoft.com relationships-many-to-many; learn.microsoft.com relationships-one-to-one (degenerate dimensions)

Direct Lake (OneLake vs SQL, framing, fallback, guardrails)

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: read the partition mode + shared expression with te script to tell OneLake from SQL. Frame after any deploy / add with te refresh --type automatic. Surface hidden DirectQuery fallback by setting te set "Model.DirectLakeBehavior" DirectLakeOnly --save in dev, then run representative report DAX.

Direct Lake on OneLake vs on SQL (not interchangeable)

Both share the VertiPaq engine and a directLake partition mode but differ in schema discovery, security, and recovery. On SQL the shared expression points at the lakehouse/warehouse SQL analytics endpoint; on OneLake it points directly at the OneLake storage path. Three behaviors an agent will otherwise get wrong:

• Fallback: on SQL a query that can't serve in-memory silently falls back to DirectQuery (slower); on OneLake there is no fallback ; it returns an error and visuals fail to render
• Composite: OneLake supports composite models (mix Direct Lake with Import/DQ/Dual); SQL does not support mixing storage modes in one model (extend it only by building a composite on top of the published model in Desktop)
• Security path: SQL checks run through the SQL endpoint (SELECT is enough); OneLake uses OneLake Security (identity needs Read+ReadAll or a OneLake role). SQL-based RLS is simply not applied on OneLake

Other deltas: SQL-only tables may be based on a SQL view (forces fallback); OneLake cannot bind a non-materialized SQL view (use a materialized view or Import); deployment pipelines rebind the data source on SQL but not directly on OneLake (use a parameter expression in the connection string); neither flavor works through any gateway. Inspect the flavor before touching: read the partition mode and the shared expression (SQL endpoint URL means on SQL; OneLake abfss path means on OneLake). Choosing OneLake "because no fallback" means hard query failure under guardrail breach or unprocessed tables, not graceful degradation ; size capacity and optimize Delta so in-memory always wins. Adding multiple model tables from the same source Delta table is unsupported via Desktop/web in both flavors (XMLA only, and "Edit tables" + refresh then errors).

Sources: learn.microsoft.com direct-lake-overview (key concepts, comparison, limitations); learn.microsoft.com direct-lake-develop (model tables); learn.microsoft.com direct-lake-how-it-works; repo te-cli command-reference

Framing, reframing, and why a fresh Direct Lake table answers nothing

Framing points the model at the latest committed Delta version by reading the Delta log and current Parquet; it is triggered by a refresh, usually completes in seconds, and is mostly metadata. After framing, queries see the Delta state as of that last framing, not necessarily the latest writes. Framing evicts changed column segments so they reload on next access; unchanged columns stay resident (incremental framing). The trap: a Direct Lake table created/added via XMLA/automation (or just deployed with te deploy) is unprocessed until you send a refresh ; until framed, on SQL every query falls back to DirectQuery, on OneLake queries error. So "I deployed and it's slow / returns errors" is almost always "you never framed it." Tabular Editor 3 frames on first deploy for this reason; the cross-platform te CLI does not implicitly, so the agent must trigger it.

A refresh is the frame ; after deploying or adding Direct Lake tables, run te refresh --type automatic (or per-table). For a full cold rebind the XMLA path is processClear then processFull; with te the nearest is --type clearvalues then --type full, and --dry-run/--trace inspect what TMSL it sends and confirm framing. Control automatic framing (the default that reflects OneLake changes without manual refresh) ; disable it for point-in-time control and frame deliberately (set via manage APIs/TMSL if te set lacks the toggle). Pitfalls: te refresh on Direct Lake reframes metadata pointers, it does not copy data; framing fails if a Delta table breaks a guardrail (e.g. >10,000 Parquet files) ; optimize first; fresh lakehouse rows do not appear when automatic updates are off and you never reframed; do not --type full a hot production model expecting "safer," it forces a colder reload that hurts the next queries.

Sources: learn.microsoft.com direct-lake-how-it-works (framing, automatic updates); learn.microsoft.com direct-lake-understand-storage; learn.microsoft.com direct-lake-overview; docs.tabulareditor.com direct-lake-guidance; repo te-cli command-reference

DirectQuery fallback: exact triggers and how to forbid it

On Direct Lake on SQL only, a query that can't serve in-memory transparently switches that table to DirectQuery against the SQL endpoint ; slower, but always returns the latest source data. On OneLake there is no fallback. Fallback is the biggest hidden Direct Lake regression; worse, the docs note fallback uses hybrid query plans that carry a tradeoff even when no fallback is needed, so leaving it enabled taxes every query. The evaluation order for a Direct Lake on SQL query: (1) semantic-model OLS on a restricted object -> error; (2) SQL-endpoint CLS denial -> error; (3) SQL-endpoint RLS, or any table on a SQL view -> fall back to DirectQuery; (4) exceeds a capacity guardrail -> fall back; (5) otherwise in-memory. Sharp edge: if a lakehouse SQL endpoint switches from fixed-identity to SSO, OneLake roles become SQL granular rules and Direct Lake on SQL then falls back 100% of the time.

Control with the model-level directLakeBehavior: Automatic (default, falls back), DirectLakeOnly (never fall back ; a query that can't run in Direct Lake fails instead of going slow), DirectQueryOnly (A/B comparison). Setting DirectLakeOnly in dev is the best way to surface hidden fallback ; failures become loud instead of slow. It applies only to Direct Lake on SQL. Set via te set "Model.DirectLakeBehavior" DirectLakeOnly --save, or TOM/TMDL if rejected, then run representative report DAX and watch for visual errors (a real fallback path to fix). Do not set DirectLakeOnly in production where some queries legitimately need RLS/view paths ; those visuals hard-fail. To detect whether fallback happened, capture an XMLA query trace and look for DirectQuery storage-engine events. Any table on a non-materialized view forces fallback on SQL and is not creatable on OneLake ; materialize it or use Import for that one table.

Sources: learn.microsoft.com direct-lake-how-it-works (DirectQuery fallback); learn.microsoft.com direct-lake-security-integration; learn.microsoft.com direct-lake-understand-storage; learn.microsoft.com DirectLakeBehavior; fabric.guru controlling-direct-lake-fallback

Direct Lake guardrails and modeling-rule deltas vs Import

Direct Lake imposes constraints Import doesn't, and breach behavior differs by flavor. The model-level Max model size guardrail is evaluated once; the rest are per query. Max Memory is a paging ceiling, not a guardrail (it pages and degrades, won't fail). Breach: on SQL the refresh warns and queries still return via fallback; on OneLake the refresh fails like Import until the Delta tables are optimized back under the limits. Because guardrails are mostly per-query, you cannot audit by model size alone ; the same model serves small queries in-memory and falls back (or fails) on a wide scan. The fix is upstream Delta optimization (V-Order, large segments roughly 1-16M rows, Parquet files well under 10,000, reduced cardinality), not a model edit.

Modeling rules that differ: no calculated columns/tables referencing Direct Lake columns on SQL (unsupported); on OneLake calculated columns are preview and unmaterialized (review before relying). Push row-level logic upstream into the Delta table or an Import table in an OneLake composite. Calc groups, what-if/field parameters implicitly create calculated tables but are allowed (they don't reference Direct Lake columns), which is why a SQL-flavor model can host calc groups while banning calc columns. MDX clients (Analyze in Excel) treat Direct Lake tables like DirectQuery: no session-scoped MDX, no Direct-Lake-table user hierarchies (Import-table hierarchies still work in a composite). Audit before deploy: list calculated columns/tables (illegal on a SQL-flavor model) and partition modes, and run te bpa with org rules flagging calc columns on Direct Lake partitions and tables bound to SQL views. te-cli detects and edits the model; it cannot V-Order or compact Parquet (that is fab + a lakehouse/Spark OPTIMIZE job).

Sources: learn.microsoft.com direct-lake-overview (capacity, limitations); learn.microsoft.com direct-lake-understand-storage; learn.microsoft.com direct-lake-how-it-works; repo te-cli command-reference

Documentation and BPA

Companion to the semantic-model skill (SKILL.md). Documentation (data dictionary, change diffs), BPA as a documentation gate, and intra-model impact analysis for safe edits.

Scope note. This reference is intra-model only. For lineage of artifacts (which reports, dataflows, and lakehouses feed and consume this model across Fabric workspaces), use the lineage-analysis skill and fab. That artifact lineage is a different question from the object-to-object dependencies covered here; do not confuse the two.

Working with `te`: te bpa run --fail-on error (rules via --rules / TE_BPA_RULES); build a data dictionary or coverage audit with te query -q "EVALUATE INFO.VIEW.MEASURES()" and the INFO.VIEW.*() family. For impact analysis before an edit, te deps "<obj>" and INFO.CALCDEPENDENCY (intra-model object dependencies). Artifact lineage across workspaces is the lineage-analysis skill, not te.

Generate a model data dictionary from INFO functions

A reproducible data dictionary (every table, column, measure, relationship with description, format, folder, type, hidden flag, source) built by querying the model's own metadata through DAX INFO functions instead of parsing TMDL by hand. INFO functions are DAX wrappers over the AS schema DMVs, returning tables you reshape with SELECTCOLUMNS/ADDCOLUMNS/FILTER. TMDL is the source of truth but spread across dozens of files and does not resolve inherited/inferred state (effective data type, summarizeBy, post-perspective hidden status); one DAX query returns the evaluated catalog, which is what a dictionary, a coverage audit, or a version diff needs. The first ~200 chars of each description is what Copilot ingests, so a description-coverage report doubles as an AI-readiness gate.

The INFO.VIEW.* family (TABLES(), COLUMNS(), MEASURES(), RELATIONSHIPS()) returns friendly pre-joined human-named columns and is the only INFO family usable inside calculations and calculated tables (so you can even bake a self-documenting hidden table into the model). Raw rowsets (INFO.MEASURES(), INFO.COLUMNS(), etc.) expose ID/TableID/LineageTag for joins and matching against TMDL lineage tags; INFO.MODEL() gives the dictionary header. te has no document verb but te query runs the EVALUATE and redirects to CSV/JSON ; that is the generator. A description-coverage audit is FILTER(INFO.VIEW.MEASURES(), NOT [IsHidden] && LEN([Description]) = 0). Batch multiple queries in one te script pass (each te call carries ~1-2s startup). Pitfalls: INFO functions need write/admin permission and can't run over a Desktop live connection (a non-issue against your own .pbip via te query); INFO.MEASURES() returns TableID not a name (join to INFO.TABLES() or use INFO.VIEW.MEASURES() which pre-resolves it; the two families don't share column names, don't mix blind); [Expression]/[Description] carry literal newlines (emit JSON, not CSV, for naive parsers); INFO.VIEW.* returns the default culture only (pull translated captions from INFO.CULTURES() for localized models). If te can't reach the model, run the identical EVALUATE through executeQueries REST or XMLA (INFO is server-side); direct-TMDL parsing is a strictly worse last resort (declared, not effective, metadata).

Sources: learn.microsoft.com info-functions-dax / info-measures / info-model; repo te-cli command-reference

Intra-model impact analysis with INFO.CALCDEPENDENCY (object dependencies, not artifact lineage)

This is the object lineage inside the model (measure -> measure -> column), used to know what breaks before an edit. It is not the cross-workspace artifact lineage of the lineage-analysis skill; pair the two, since neither alone sees the full blast radius.

Before renaming or deleting a column/measure you need the full transitive set of referencing objects: other measures, calc columns, calc items, format-string expressions, detail-rows, RLS filters, relationships. The two tools see different things and you want both. te deps "Sales/Revenue" --upstream --downstream walks Tabular Editor's static reference graph (fast local refactors; powers te deps --unused, objects with no DAX refs and not used in relationships/hierarchies/sort-by/variations/time roles). INFO.CALCDEPENDENCY (alias INFO.DEPENDENCIES) is the engine's own dependency graph, including dependencies that only materialize in a query context, so it surfaces references te deps may not classify the same way: a column pulled in only via a measure's RLS context, a calc item's SELECTEDMEASURE chain, or dependencies of an arbitrary ad-hoc DAX query. Crucially it answers "given this report visual's DAX, which model objects does it need" via the "Query" restriction ; query-context impact te deps cannot do.

Run it through te query (full graph EVALUATE INFO.CALCDEPENDENCY(); upstream of a measure via the "Query" restriction; reverse/downstream by filtering on [REFERENCED_TABLE]/[REFERENCED_OBJECT]). Safe-rename sequence: te deps --downstream for the fast view, the reverse INFO.CALCDEPENDENCY filter for context-only refs, the edit with te mv/te set, te validate, then te bpa run --fail-on error. te mv/rename does not rewrite references inside report PBIR, so combine with the `lineage-analysis` skill (artifact lineage) and pbir-cli for the external blast radius ; see references/refactoring-renaming.md. Pitfalls: needs write permission, won't run over a Desktop live connection; each row is one direct edge (recurse yourself for N-hop, where this pass's [REFERENCED_OBJECT] becomes the next pass's [OBJECT]); double-quotes inside the "Query" restriction must be doubled; te deps --unused and INFO.CALCDEPENDENCY both miss a measure referenced only by a report visual (neither sees PBIR), so don't auto-delete on --unused alone, cross-check report (artifact) lineage.

Sources: learn.microsoft.com info-calcdependency-function-dax / info-dependencies; SQLBI understanding-data-lineage-in-dax; repo te-cli command-reference

BPA as the documentation and metadata-completeness gate

Use the Best Practice Analyzer not for performance/DAX antipatterns but as an automated documentation enforcer: fail the build when visible measures lack a description, columns lack a format string, or objects sit outside a display folder. The rules are tiny Dynamic LINQ expressions scoped to an object type. Documentation rots silently; a CI gate keeps it honest far cheaper than human review, and since te bpa run already gates deploys, folding metadata rules in costs nothing extra. This complements the INFO dictionary: INFO reports coverage, BPA enforces it and can auto-fix trivial cases.

Author metadata rules (e.g. not IsHidden and string.IsNullOrWhitespace(Description) scoped to Measure; a numeric-column-needs-format-string rule with a FixExpression = FormatString = "#,0"), then run te bpa run -r ./metadata-rules.json --no-defaults --fail-on error --ci github for a clean documentation gate, or --fix --save to auto-apply trivial fixes. -r accepts a local file or URL (repeatable); --no-defaults runs only your rules so the gate isn't drowned by the standard ruleset; to ship rules with the model, embed them in the BestPracticeAnalyzer model annotation. Pitfalls: a FixExpression writing a format string is fine, one "fixing" a missing description is not (no sensible default text ; leave description rules fix-less and let them fail loudly); BPA evaluates the model graph so it can't enforce description coverage that only matters downstream in reports (pair with report-side review); --no-defaults (drop built-in rules) differs from --no-model-rules (drop model-annotation rules); severity is advisory until mapped to a gate (--fail-on error only trips on error-severity rules, so set documentation rules' severity to match).

Sources: github.com TabularEditor/BestPracticeRules; docs.tabulareditor.com Best-Practice-Analyzer-Improvements; repo te-cli command-reference; repo bpa-rules skill

Snapshot and diff model metadata for change documentation

Capture the evaluated catalog (from the INFO queries) as a versioned text artifact, then diff two snapshots to auto-generate a human-readable changelog. TMDL git diffs show file changes but are noisy (lineage tags, reordering, whitespace) and don't summarize semantically ("3 measures added, 1 description removed, format changed on Sales[Amount]"). A metadata snapshot is a stable sorted projection of only the fields you track, so its diff is the changelog ; the documentation counterpart to the pbi-desktop refresh-cache hook that snapshots metadata to tmp/model-metadata.json.

Emit a deterministic sorted projection (SELECTCOLUMNS(INFO.VIEW.MEASURES(), ...) ORDER BY [Table], [Name]) to CSV per object type (tables, columns, relationships in sibling files so each diffs independently), commit it, and git diff --no-index between releases. The ORDER BY is what makes the diff stable across exports. Pitfalls: don't snapshot INFO.MEASURES() raw (its ModifiedTime/StructureModifiedTime change on every save, producing noise; project only semantic fields via INFO.VIEW.*); embedded newlines in [Expression] make CSV line-diffs awkward (format DAX consistently first with te format --save so only real logic changes surface); a renamed object shows as delete+add in a text diff (key the projection on [LineageTag], stable across rename, for rename-aware changelogs).

Sources: learn.microsoft.com info-functions-dax; repo te-cli command-reference; pbi-desktop refresh-cache hook precedent

Hierarchies, KPIs, perspectives, cultures

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: te add hierarchy ... and set levels; KPIs, cultures, and metadata translations that te does not expose go through te script (TOM) or TMDL. Perspectives: te add perspective plus membership.

User hierarchies (creation, levels, ordering, anti-patterns)

A user hierarchy is a navigation path on a single table (Category -> Subcategory -> Product); metadata only, no storage, no DAX. In TOM/TMDL it is a Hierarchy owning ordered Level objects, each pointing at one column on the same table. It is a usability and AI-readiness signal (Copilot, Q&A, and the field list read it); a model with logical drill paths reads as curated. The hard constraint authors miss: a hierarchy cannot span tables (one reason to flatten snowflakes into one dimension upstream); if the levels live on two tables, fix it upstream in Power Query, not with a relationship.

Create the hierarchy then add levels top-to-bottom (level order = creation order); bind each level to its feeding column. Confirm the Level child-property names (Column, Ordinal) with te set <level> -q before scripting many; reordering an existing hierarchy means setting Ordinal explicitly. For bulk work (the same hierarchy across role-playing date dimensions, or fixing ordinals across dozens), use te script appending Level objects to a Hierarchy.Levels. Pitfalls: a level needs its column's Sort By Column set for non-alphabetical order (the hierarchy honors the column's sort, it defines none of its own); set summarizeBy: none on every level column and key (an auto-summing level shows a sigma and produces nonsense aggregations dropped in alone); TOM/TMDL does not auto-hide level columns (decide deliberately ; hide raw columns to force navigation, or leave visible for ad-hoc, but both visible doubles the field list); te deps <col> --unused treats a hierarchy-level column as used, so an unused sweep won't remove it (remove the level first).

Sources: learn.microsoft.com tmdl-reference-tabular-object; repo te-cli command-reference / semantic-modeling-practices; SQLBI parent-child-hierarchies-in-tabular

Parent-child hierarchies via PATH (ragged trees)

When depth is variable or unknown (org charts, chart-of-accounts, BOM) you cannot pre-build N named level columns. The tabular pattern flattens a self-referencing parent/child table into fixed level columns with the PATH family in DAX calculated columns, then builds a normal user hierarchy over them. This is the one legitimate documented use of DAX calculated columns the modeling-practices ref calls out by name (reserve calc columns for RELATED, PATH, or COMBINEVALUES keys) ; the recursion is naturally a DAX path operation and the ragged depth defeats a regular relationship.

Author the calc columns with te add -t CalculatedColumn, validating each: PATH(Employee[EmployeeId], Employee[ManagerId]) for the root-to-node path string, PATHLENGTH for node depth, and one LOOKUPVALUE(..., PATHITEM(path, n, INTEGER)) column per fixed level to a chosen max depth >= the deepest branch; then build the hierarchy over the level columns. For depth > 5 or several parent-child tables, write the loop in te script after reading max depth from a te query. The actual hard part is ragged depth: a shallow branch leaves trailing Level columns blank, rendering as repeated leaf labels under deeper siblings. Suppress with a browse-depth-vs-node-depth measure using ISINSCOPE per level that blanks the value when the visual has drilled past where a node exists; every measure surfaced against the hierarchy needs the blanking guard (the maintenance cost a reviewer should flag ; parent-child couples every measure to the depth logic). Pitfalls: PATHITEM(..., position) without the INTEGER type arg returns text and a LOOKUPVALUE against an integer key silently misses; multiple roots are valid but each starts its own path (add a synthetic root upstream for a single tree); PATH requires the parent column to reference the same table's key with no orphans (an orphan ManagerId errors at refresh, not author time ; the check-ri hook covers cross-table keys not self-references); these are calc columns, so unsupported in DirectQuery and not materialized in Direct Lake (forces Import, or push the flattened columns to the Lakehouse).

Sources: learn.microsoft.com path-function-dax; SQLBI parent-child-hierarchies-in-tabular; repo te-cli semantic-modeling-practices; Fabric forums (PATH multiple-root thread)

KPI objects on measures (status / goal / trend)

A KPI is a sub-object on a single measure adding a goal (target), a status expression (band logic mapping a value to good/neutral/bad), and optional trend, so clients render a traffic light without the author rebuilding the band DAX. In TOM it is the KPI object at Measures/<name>/KPI (one of the three single-object TOM children, with Hierarchies and LinguisticMetadata, rather than collections). Defining status once on the measure means every consumer and Copilot's notion of "on target" agree; the tradeoff is the goal/status are baked into the model, so a per-report target override is impossible (if targets vary by audience, leave it as report-level conditional formatting).

A KPI attaches to an existing measure: create the wrapper (te add "<table>/<measure>/KPI" -t KPI), discover settable properties with te set <kpi> -q (TargetExpression/target-measure, StatusExpression, StatusGraphic, TrendExpression), then set them. The status expression must return a small integer band (-1 bad / 0 / 1 good) referencing the owning measure, e.g. SWITCH(TRUE(), [Margin %] >= 0.4, 1, [Margin %] >= 0.25, 0, -1) ; returning a boolean or the raw value renders no indicator. StatusGraphic is a string from a fixed client-recognized set ("Three Circles Colored", "Traffic Light", "Five Bars Colored") ; a typo serializes fine but renders nothing, so pin the exact spelling from a model Power BI already authored (te get <measure>/KPI --output-format tmdl). For anything beyond one KPI, drive it through te script (measure.KPI = new KPI() then assign). A measure carries one KPI (different bands for different audiences = different measures, or report-level formatting). These are legacy KPI objects, distinct from Fabric Metrics/Scorecards (a workspace item type) ; do not conflate them in a review.

Sources: learn.microsoft.com introduction-to-tabular-object-model (KPI single child); repo te-cli command-reference; repo tmdl object-properties

Incremental refresh and refresh strategy

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: read the (preview, unstable) flags with te incremental-refresh set --help, then te incremental-refresh set ..., te incremental-refresh apply, and te incremental-refresh show. Targeted refresh: te refresh --table <t> --partition "<t>.<p>" --type full. Properties not exposed drop to TOM / TMSL or the refreshPolicy TMDL block.

Configure an incremental refresh policy from a terminal

A refreshPolicy on one table tells the service how to auto-partition by date and which partitions to re-process. Two windows: archive (rolling) = history kept; incremental = recent slice re-queried per refresh. The service rolls both forward, merges aged partitions, drops out-of-archive ones. None are visible in Desktop or the service UI until the first service refresh applies the policy. It is the biggest lever on large-fact refresh cost (a 10k-rows/day fact with a 3-day window re-queries ~30k rows, not all history) and a one-way door: once published you cannot re-publish from Desktop (it wipes partitions) or download the .pbix, so the policy must be right before first service refresh, and edits after that go only through XMLA/te-cli.

Prerequisites in order: (1) two Date/Time model parameters named exactly RangeStart/RangeEnd (reserved, case-sensitive); (2) the partition M filters its date column half-open >= RangeStart and < RangeEnd (upper-exclusive so boundary rows aren't double-counted); (3) the filter and column are Date/Time and query-fold (if the source keys on an integer like OrderDateKey, convert the params inside the filter via Int32.From(DateTime.ToText(RangeStart,[Format="yyyyMMdd"])) rather than abandoning folding ; a non-folding filter is the dominant cause of initial-refresh timeouts).

te incremental-refresh is the operable entry point, but exact flag names are not stable across preview builds, so read them from the binary first (te incremental-refresh set --help). set writes the policy (rolling-window granularity/periods, incremental granularity/periods, incrementalPeriodsOffset for complete-periods-only, policyType=basic, mode=import|hybrid); apply materializes/expands the partition set (equivalent to TOM ApplyRefreshPolicies = RequestRefresh + SaveChanges). If a flag is missing (e.g. pollingExpression), fall to TOM/TMSL or hand-edit the refreshPolicy block. Compat level must be >= 1550 (>= 1565 hybrid). Pitfalls: every table reuses the same RangeStart/RangeEnd (no per-table pairs); size the incremental window to the late-arrival margin, not wider; enable Large model storage before the first refresh if over ~1 GB; a backdated update to the partition date column itself breaks IR (engine reads delete+insert, the delete is never picked up) ; treat transaction dates as immutable and selectively refresh from the change point; the first service refresh loads the whole store window (bootstrap on Premium to dodge the 5h/2h ceilings).

Sources: learn.microsoft.com incremental-refresh-overview / -configure / -xmla / -troubleshoot; repo te-cli command-reference, workflows; repo tmdl object-properties; repo c-sharp-scripting partitions

Detect data changes and custom polling queries

An optional refinement: instead of unconditionally re-querying every period in the incremental window, the service tracks the max of a dedicated audit date/time column (e.g. ModifiedDate) per period and skips periods whose max hasn't moved. This can collapse a 3-day refresh to 1 day or fewer when most days are quiet, cutting work without shrinking the window (so you keep a late-arrival safety margin). Hard constraints: the audit column must differ from the RangeStart/RangeEnd partition column (same column = no signal); default behavior caches that column into memory for comparison, costing RAM proportional to cardinality (reduce it first, or use a polling query to avoid materializing it); it detects soft deletes only ; a hard delete (row physically gone) is invisible.

A custom polling query (Premium, TOM/TMSL only) sets pollingExpression to a lightweight M scalar run once per partition; a changed scalar flags that partition for full processing. This avoids caching the audit column and lets an ETL process drive refresh by writing a control table the polling expression reads, so a backdated change to one month reprocesses one month cheaply. No Desktop UI ; set via te-cli (if exposed), TOM, or TMSL. Microsoft's 120 months granularity example is deliberate: a month-grain rolling window over 10 years lets a backdated change reprocess a single month but sacrifices some compression vs coarser yearly partitions ; surface that RAM-vs-refresh tradeoff, don't silently pick.

Sources: learn.microsoft.com incremental-refresh-overview (real-time data); learn.microsoft.com incremental-refresh-xmla (custom queries for detect data changes); repo tmdl object-properties

Hybrid (real-time) tables: the DirectQuery partition and its blast radius

Setting the policy mode to hybrid (Premium) appends one DirectQuery partition covering the slice newer than the incremental window; the table then serves imported history and live source rows in one query. Compat >= 1565, AS client libs >= 19.27.1.8. "Add one DQ partition" is misleadingly small: it converts the table to hybrid storage and propagates to related tables and report caching. Two consequences an agent must address: 1. Related dimensions must move to Dual. A hybrid table is queried in both Import and DQ contexts; any related table must be Dual or the relationship degrades to limited (over-fetch, slow). Desktop reminds on toggle but does not auto-fix import dims (a DQ dim flips to Dual trivially; an import dim must be recreated in DQ then switched by hand). Through TOM/te-cli there is no reminder, so check every related table's mode after enabling hybrid 2. Report visuals cache and won't show the live partition by default. Power BI caches visual results, defeating the DQ partition unless reports use Automatic Page Refresh (fixed-interval, or change-detection ; the latter Premium-only). This is a report-side setting; the model change alone doesn't deliver real-time

"Only refresh complete days" is mandatory under hybrid (auto-enabled) ; with partial periods allowed, the boundary between the live DQ partition and the newest import partition can double-count or drop rows mid-day. It is also useful standalone when partial-day metrics are meaningless or upstream data finalizes late (set incremental period = 1 month, schedule for the close date). Service refreshes run in UTC unless you set a refresh time zone, which shifts what counts as a complete day.

Sources: learn.microsoft.com incremental-refresh-xmla (partitions); learn.microsoft.com incremental-refresh-troubleshoot (hybrid in the service); learn.microsoft.com incremental-refresh-overview

Refresh-strategy decision guide for large fact tables

A decision path before reaching for configuration, since agents tend to jump to "enable incremental refresh" when the real constraint is folding, freshness, or capacity tier ; picking the wrong layer wastes a one-way-door publish:

• Does the source query-fold on the date filter? If not, incremental refresh is off the table until folding is fixed (the per-partition queries won't filter at the source and the initial refresh times out). Verify with a tracing tool that one folded query carries the RangeStart/RangeEnd filter
• Large but static history? Plain incremental refresh (import); archive window to reporting need, incremental window to the late-arrival margin
• Most of the window quiet day-to-day? Add detect-data-changes (or a polling expression for ETL-driven control); watch the audit-column RAM cost
• Sub-hour freshness on the newest slice? Hybrid (Premium) + Dual dimensions + report Automatic Page Refresh; accept the storage-mode and caching complexity
• Initial load can't finish? Bootstrap the first refresh on Premium (create partitions empty, backfill via XMLA) and enable Large model storage beforehand; for small per-external-query sources (ADX, Log Analytics, App Insights) shrink store/refresh granularity to avoid truncation

After publish you never touch Desktop again for that model: inspect with te incremental-refresh show, trigger targeted refresh with te refresh --table <t> --partition "<t>.<part>" --type full (note --apply-refresh-policy true is the default and re-evaluates the rolling window; pass false to refresh data without rolling it). Fix a backdated-data conflict by refreshing every partition from the change point to current to keep the one-side key unique. Metadata-only changes deploy through XMLA (ALM Toolkit, TMSL, te deploy --skip-refresh-policy), never a re-publish.

Sources: learn.microsoft.com incremental-refresh-troubleshoot / -overview / -xmla; repo te-cli command-reference; repo refresh-semantic-model SKILL

Metadata, naming, and organization

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: te set <obj> -q displayFolder -i "Sales" --save, te set <obj> -q isHidden -i true --save, te set <obj> -q formatString -i "..." --save. To rename, te mv or te set <obj> -q name, but renaming breaks downstream references: follow references/refactoring-renaming.md first (lineage check, then propagate with pbir-cli / fabric-cli).

Measure tables: how many, where they sort, and the DirectQuery gotcha

A measure table hosts measures so they don't clutter fact/dimension tables. Three decisions the one-liner hides: (1) one vs many ; a single _Measures is the default, but a model with hundreds of measures across disjoint subject areas benefits from several (_Sales Measures, _Finance Measures) giving a coarse first-level grouping before display folders, at the cost of more top-level field-list entries ; do not split a 20-measure model. (2) sort position ; measure tables sort alphabetically with everything, so a leading _ pins them to the top (underscore sorts above letters and survives TMDL round-trips; avoid leading characters TMDL must quote). (3) the placeholder column must stay but be invisible ; the repo's add-measure-table.csx builds a single-row table and sets the lone column IsHidden=true and IsAvailableInMDX=false (hiding removes it from the field list; isAvailableInMDX=false stops MDX clients showing the stub and stops an unnecessary attribute hierarchy).

Run the existing add-measure-table.csx and move-measures-to-table.csx scripts against a live/local model via te ... --file --stage, then te validate and te save. Pitfalls: an empty measure table from Table.FromRows is an Import partition ; in a pure DirectQuery or Direct Lake model that single-row Import table forces composite/mixed storage (build from a constant-folding source or accept the flag, do not assume it's free); give it no relationship (a stray one makes its hidden column filterable); a measure's home table is cosmetic (it doesn't change filter context) but moving a measure changes its qualified name's table part, breaking any visual that qualified it as 'OldTable'[Measure] ; run the rename-cascade check after moving measures.

Sources: repo c-sharp-scripting add-measure-table.csx / move-measures-to-table.csx; repo SpaceParts __Measures.tmdl; docs.tabulareditor.com creating-and-testing-dax

Display-folder structure: path syntax, nesting, and per-table scoping

DisplayFolder groups columns/measures into a virtual tree. Non-obvious rules: (1) nesting uses a forward slash ; "Columns/Keys" creates Columns with child Keys (confirmed by the repo's organize_folders.csx); a backslash does not nest, only / does, so a wrong separator yields one literal folder Columns\Keys. (2) folders are scoped to the home table ; "Metrics" on a column in Invoices and "Metrics" in Customers are two unrelated folders that never merge (for one cross-table folder use perspectives or a measure table). (3) measures and columns share the namespace within a table ; on a measure table a shallow tree (Time Intelligence, Ratios, Counts) is right, and the scalable move is driving folders from naming convention (YTD/MTD/QTD -> Time Intelligence, %/Rate -> Ratios), as organize_measures_by_type.csx does. (4) empty/whitespace segments and leading/trailing slashes produce ghost folders ; trim first.

Fold an entire model's measures by naming pattern in one pass with the shipped scripts, wiping inconsistent folders first (clear_all_display_folders.csx) so you don't leave orphans from a prior scheme, then organize. Pitfalls: a folder on a hidden object is invisible work (hidden objects don't appear in the field list at all); folders are not security or a perspective (everything is still queryable and visible to Copilot, folders only tidy the human browse); TMDL stores displayFolder as a plain property line with / separators, no quoting unless it contains a reserved character.

Sources: repo c-sharp-scripting organize_folders.csx / organize_measures_by_type.csx / clear_all_display_folders.csx; repo tmdl object-properties

Perspectives as an AI/Copilot scope, not security

A perspective is a named subset of tables/columns/measures/hierarchies. The forward-looking reason to build one: it is a meaningful scoping surface for Copilot/Q&A (a focused perspective constrains what the experience reasons over, reducing the "too many fields, ambiguous names" failure) ; a different lever than hiding (hiding is global, a perspective is a named view you point a specific consumption surface or audience at). Hard rule: perspectives are usability, not security ; anyone who reaches one can still query every table with hand-written DAX ("perspectives are not security") ; use OLS/RLS to actually deny access, and never present a perspective to a stakeholder as access control. Maintenance cost is real: a perspective must be re-synced whenever you add a measure/column or new fields silently fall out.

Use the shipped perspective scripts; the key operational pattern defines membership from the model's own visible/hidden state (the "Sync Perspective with Hidden Status" pattern: loop Model.Tables.Where(t => !t.IsHidden) and set InPerspective[name]=true for non-hidden children) so the perspective tracks hide decisions and stays in sync with one re-run after model changes. Audit contents before trusting one. Editing perspectives requires unlocking "Allow unsupported Power BI features" in the GUI; via te/TOM/TMDL there is no such gate (the property is directly settable, another reason to drive this from the CLI). A perspective should be additive from empty (add only what the audience needs), because new tables default to excluded from existing perspectives, so a subtractive "everything minus a few" mental model drifts as the model grows. Test in the actual consumption surface (Excel honors perspectives; some web experiences historically ignored them); include columns a visible measure depends on, or the perspective confuses a human/Copilot browsing it (the measure still computes).

Sources: repo c-sharp-scripting perspectives examples / object-types; repo SpaceParts Measure Selection.tmdl; learn.microsoft.com copilot-semantic-models; mssqltips.com perspectives-in-power-bi; docs.tabulareditor.com

The "No Measure Selection" perspective pattern for calc-group defaults

The SpaceParts model ships two near-empty perspectives, Measure Selection and No Measure Selection, with nothing explaining them ; a specific Tabular Editor idiom worth documenting so an agent encountering them knows they're load-bearing. When a model has a calculation group and a report uses SELECTEDMEASURE()-driven items, a visual with no explicit measure falls into the calc group's multiple-or-empty branch and returns BLANK(), leaving cards empty until the user picks a measure. The paired perspectives are the convention for wiring a default measure so something renders before selection: one represents the chosen state, the other the empty state, and the report (or a field-parameter/default-member setup) switches between them. The perspectives carry no DAX ; they are markers the report layer keys off.

Recognize, don't delete (run the rename-cascade/downstream-report check before removing either). Diagnose blank visuals at the calc group's empty/multiple-selection branch (give it a sensible default measure), not in the perspectives ; the perspectives organize which measures are offered, the BLANK comes from the calc-group expression. This is a convention, not an engine feature (there is no "default measure" property), so it only works if the report and any field-parameter wiring agree with the perspective names ; renaming a perspective without updating the report silently breaks the default-measure UX. Don't confuse it with security or AI scoping.

Sources: repo SpaceParts Measure Selection.tmdl / No Measure Selection.tmdl; community.fabric.microsoft.com (Tabular Editor calculation groups thread); learn.microsoft.com calculation-groups

Cultures, metadata translations, and perspectives (full-coverage workflow)

A culture (locale, e.g. fr-FR) carries metadata translations: per-object translated caption, description, and displayFolder for tables, columns, measures, hierarchies. The client picks the culture via the connection (LocaleIdentifier=1036, or the Analyze-in-Excel language drop-down) and the engine returns translated names only. Translations and perspectives are commonly half-done (a culture with three measures translated and forty not, so a user sees a jarring mix); there is no per-object fallback beyond "fall back to the default culture string," so treat partial translation as a finding, not a feature. Constraints: all translations share one collation (you cannot natively sort French and Japanese in the same model); translations are metadata only, never data values (a Color column's "Red" stays "Red" ; translated data is an upstream ETL problem).

Create the culture, then translate ; properties are bracket-indexed by culture (TranslatedNames[<culture>], TranslatedDescriptions[<culture>], the display-folder equivalent ; confirm its exact name first). Hand-setting per object does not scale and silently leaves gaps; drive completeness from te script iterating every visible Table/Column/Measure/Hierarchy, looking up each translation, and reporting or filling blanks (the only reliable way to audit coverage ; there is no te subcommand for a translation-coverage report). For perspectives, go beyond bare membership and build additive from empty (new tables default to excluded, so a subtractive model drifts as the model grows). Pitfalls: report visuals bind to the object name not the translated caption, so translating does not break reports but also does not localize hard-coded report titles; the Power BI service does not expose a culture picker like Excel/AS clients, so metadata translations land mainly for Excel/Analyze-in-Excel and XMLA clients (a report will not auto-switch language from these alone); perspectives are not a security boundary; adding a table to the model does not add it to existing perspectives or translate it (both are maintenance surfaces that silently rot ; re-check coverage after any model growth).

Sources: learn.microsoft.com translations-in-tabular-models; learn.microsoft.com translation-support-in-analysis-services; learn.microsoft.com tmdl-reference-tabular-object (translations); repo te-cli workflows / command-reference

Field and what-if parameters

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: build field parameters with the c-sharp-scripting macro via te script (do not hand-author the DAX + annotations through te add). A what-if is te add table with a GENERATESERIES partition plus a SELECTEDVALUE measure. Verify with te get "<FP>" --output-format tmdl.

Field parameter structure, ordering, and the sort column

A field parameter is a calculated table whose import partition is a DAX constructor of 3-tuples ("Label", NAMEOF([Measure or Column]), <sortIndex>), projected into three columns with the second tagged extendedProperty ParameterMetadata = { "version": 3, "kind": 2 }. Three things break silently if wrong:

• The label column (Value1) must carry sortByColumn pointing at the order column (Value3), or the slicer sorts labels alphabetically instead of business order
• ParameterMetadata goes on the hidden NAMEOF column (Value2), not the label or table; omit it and the slicer's field-swap never activates
• relatedColumnDetails/groupByColumn on the label binds it back to the hidden column so a selection swaps the field; lose it and the slicer filters rows without swapping

Ordering has two independent mechanisms that fight: the Value3 index controls slicer order, but the order fields appear inside the target visual is driven by selection order at runtime (regular slicer) or hierarchy order (hierarchy slicer). So Value3 is a slicer-presentation concern only; do not assume it controls matrix column order.

Do not hand-author the DAX + annotations through te add/te set (the repo's te-cli workflow flags it as error-prone). Run the field-parameter macro from c-sharp-scripting via te script, which builds the table, constructor, extended property, and sort binding in one pass; verify with te get 'FP - MTD' --output-format tmdl that ParameterMetadata sits on the hidden column and sortByColumn on the label. Fall back to create-field-parameter.ps1 (connect-pbid) for a live local instance, then mirror the FP - MTD.tmdl example as a last resort. Review findings: a field parameter references only explicit measures/columns by NAMEOF (no implicit aggregation); it is not valid as a drillthrough/tooltip linked field (link the underlying fields); selecting zero items equals selecting all (no empty state); it needs a local model on live-connect (composite); it is unsupported in Q&A/AI visuals; keep Value3 a dense integer (0,1,2,...) or order is nondeterministic.

Sources: learn.microsoft.com power-bi-field-parameters; repo SpaceParts FP - MTD.tmdl; repo te-cli workflows; repo create-field-parameter.ps1

What-if (numeric range) parameters

A what-if parameter produces two objects from one Desktop dialog: a calculated table of evenly spaced values via GENERATESERIES(min, max, increment), and a measure SELECTEDVALUE([col], default) returning the picked value. It is a scenario input (discount rate, FX, threshold), distinct from a field parameter (swaps fields) and a dynamic M query parameter (folds a value into the source). The decision rule: swap which measure/dimension a visual shows means field parameter; let the user feed a scalar into a calculation means what-if; push a value into the source query for server-side folding means dynamic M query parameter (they are not interchangeable). A downstream measure consumes the Value measure, not the table.

There is no special metadata flag, so standard te object commands fully build it: a calculated table with the GENERATESERIES source plus the value measure; set formatString (e.g. 0%) and summarizeBy: none on the column so it stays a slicer dimension. Validate the series row count is (max-min)/increment + 1. Review findings: the table holds at most 1,000 unique values ; beyond that Power BI evenly samples and silently drops granularity, so pick an increment keeping cardinality at or under 1,000, and flag any GENERATESERIES over it. Use the value in a measure, not a dimension/row-context calculation (the selection is not in scope there ; a SELECTEDVALUE-of-parameter inside a calculated column or grouping is a red flag). Always set a meaningful default (the SELECTEDVALUE second arg fires on multi-select and no-select; an omitted default blanks dependent measures when the slicer clears). GENERATESERIES is unsupported in DirectQuery for calculated columns/RLS; the what-if table is an Import calculated table (fine for Import/composite, materialized, non-foldable).

Sources: learn.microsoft.com desktop-what-if; learn.microsoft.com generateseries-function-dax; learn.microsoft.com power-bi-visualization-troubleshoot; learn.microsoft.com desktop-slicer-numeric-range

Dynamic visual titles tied to a parameter (model-side measure)

Field and what-if parameters pair with an expression-based title: a measure reflecting the current selection so the visual header narrates what is on screen. The model piece is a SELECTEDVALUE-based measure; the report binds it to the title via conditional formatting. The value of a parameter usually surfaces to the user through this title, and the fix lives in the model (a measure), reusable across pages, so it belongs in the semantic-model skill even though the binding is a report step.

Add a measure that reads the parameter's visible label column ("Showing: " & SELECTEDVALUE('FP - MTD'[FP - MTD], "All metrics")), or for what-if surfaces the scalar ("Discount: " & FORMAT([Discount percentage Value], "0%")), then validate the string with te query. SELECTEDVALUE returns the default on both multi-select and no-select, so pick a default reading correctly in both ("All metrics", not ""). Reference the visible label column, not the hidden NAMEOF column. Keep the measure model-level (not a report-scoped extension measure) so every report reusing the model gets it. USERCULTURE() returns the user's culture only inside a measure (in a calculated column/table it returns the model default at load time), so keep dynamic-title logic in measures.

Sources: learn.microsoft.com desktop-conditional-format-visual-titles; learn.microsoft.com power-bi-field-parameters

Semantic Model Performance

Guidance for evaluating semantic model performance: memory analysis, query optimization, unused column detection, and benchmarking.

Working with `te`: time a query with te query -q "..." --trace --cold --runs 10 and compare medians; find unused objects with te deps --unused (confirm with te get before removing, keys can read as unused); read the model-size split with te vertipaq --columns --detail. Formula-engine vs storage-engine timings need a trace (connect-pbid / DAX Studio).

Performance Analysis Tools

Recommended Workflow

1. Run VertiPaq Analyzer in Tabular Editor to identify memory hotspots (large dictionaries, high-cardinality columns) 2. Run Best Practice Analyzer in Tabular Editor with an appropriate rule set to catch structural issues 3. Use DAX Studio to test specific slow queries with server timings enabled 4. Use Performance Analyzer in Power BI Desktop to identify which report visuals generate the most expensive queries 5. For production monitoring, enable Workspace Monitoring and deploy the monitoring dashboards from microsoft/fabric-toolbox

Memory and Size Analysis

What to Look For

• Total model size relative to capacity SKU; large models increase refresh time and memory pressure
• Column cardinality; high distinct-value counts (GUIDs, transaction IDs, composite keys) inflate dictionary size and hurt query performance
• DateTime columns; combined DateTime columns create near-unique cardinality with massive dictionaries; split into separate Date and Time columns
• Text column average length; long text values increase dictionary size
• Unused columns; columns not referenced by any measure or visual waste memory and slow refresh

Unused Column Detection

Unused columns waste memory and slow refresh without contributing to any report or measure. Detection approaches:

Via TMDL analysis (static): Grep all .tmdl files for column references in measures, calculated columns, and relationships. Columns not referenced anywhere are candidates for removal. Caveat: this misses references from report visual bindings.

Via Workspace Monitoring (runtime): Query logs reveal which columns are actually scanned during queries. Columns never scanned over a sustained period are candidates for removal.

Via SemanticModelAudit (automated): The SemanticModelAudit notebook in microsoft/fabric-toolbox automates unused column detection for Direct Lake models by comparing Delta table schemas with model columns.

Common Memory Optimization Patterns

• Remove or hide unnecessary columns (especially GUIDs, composite keys, transaction IDs)
• Split DateTime columns into separate Date and Time columns
• Disable Auto Date/Time tables (hidden LocalDateTable_* bloating memory)
• Disable attribute hierarchies (IsAvailableInMDX) on hidden or high-cardinality columns
• Replace calculated columns with Power Query computed columns where possible
• Reduce text column precision (trim, truncate long descriptions)
• Use appropriate data types (Integer instead of Double for whole numbers)

DAX Query Performance

Cache States

DAX query performance depends heavily on cache state. Always specify which state was measured.

Always test with warm or hot cache for typical user experience. Cold cache represents worst-case (first user after refresh or capacity resume).

Testing Methodology

1. Run each query 3+ times per cache state (ideally 10+) 2. Measure in the Power BI service, not locally (local doesn't reflect production capacity) 3. Use DAX Studio server timings to separate Storage Engine (SE) from Formula Engine (FE) time 4. A single test yields misleading conclusions -- always use multiple iterations 5. Compare before/after when making optimization changes under controlled conditions

Common DAX Performance Issues

Benchmarking DAX Queries

For systematic benchmarking across multiple queries and cache states, consider the DAXPerformanceTesting notebook from microsoft/fabric-toolbox. It automates cache clearing, capacity management, and trace capture for reliable comparisons.

For AI-assisted query optimization, consider the DAXPerformanceTunerMCPServer which identifies anti-patterns and suggests optimizations with semantic equivalence checking.

For DAX optimization, use the `dax` skill.

Performance Targets

There are no universal performance targets -- always consider targets from the consumer's perspective. Generally aim for sub-second queries for visuals.

Performance targets should be documented and communicated to model developers. Consider including them as prerequisites for endorsing (certifying) models.

Querying a semantic model

Companion to the semantic-model skill (SKILL.md). How to read data and metadata out of a model from the terminal with DAX, for validation, review, and probing. For query performance tuning, use the dax skill.

Working with `te`: te query -q "EVALUATE SUMMARIZECOLUMNS(...)" (inline) or te query -f query.dax (from a file); add --output-format json for parseable results and --output-file out.csv to save. Target a local file with -m ./model, a remote model with -s <workspace> -d <model>.

Inline vs file

Short probes go inline with -q; multi-line or reused queries go in a .dax file with -f. A file avoids shell-escaping the double quotes DAX uses for strings and column aliases, which is the main inline foot-gun. Always wrap a table expression in EVALUATE; wrap a scalar in EVALUATE ROW("x", <scalar>).

Output formats

--output-format json is the default to use when driving te programmatically (the text/table output mangles in transcripts). csv/tsv for tabular results, --output-file <path> to write to disk (format inferred from the extension). Errors and warnings go to stderr in JSON mode, so they never contaminate the parseable stdout.

Metadata via INFO functions

The model's own schema is queryable as data, which is how you inspect a model that te ls cannot fully enumerate:

• EVALUATE INFO.VIEW.RELATIONSHIPS() ; relationships with friendly from/to, cardinality, cross-filter, active flag (te ls cannot list relationships)
• EVALUATE INFO.VIEW.MEASURES() / INFO.MEASURES() ; measures, expressions, format strings, display folders
• EVALUATE INFO.VIEW.COLUMNS() / INFO.TABLES() ; columns and tables with data types and properties
• EVALUATE INFO.STORAGETABLECOLUMNS() / INFO.DICTIONARYSTORAGES() ; live cardinality and dictionary sizes when a VPAX export is not available

Probing patterns

• Test a measure in isolation: EVALUATE ROW("Result", [Total Revenue])
• Visual-shaped query (what a report sends): EVALUATE SUMMARIZECOLUMNS('Date'[Year], "Revenue", [Total Revenue])
• Check column values / cardinality: EVALUATE TOPN(20, VALUES('Geography'[Region])), EVALUATE ROW("n", DISTINCTCOUNT('Sales'[CustomerKey]))
• Emulate an RLS filter offline: EVALUATE CALCULATETABLE(<query>, TREATAS({"alice@contoso.com"}, 'UserMap'[UserEmail])) (proves the predicate; does not exercise the security context, see security.md)

Performance-aware querying

For timing, te query ... --trace --cold --runs 10 and compare medians, not means; discard the first cold run as warm-up. --cold clears the cache for a true cold measurement. Single runs are noise. Formula-engine vs storage-engine split needs a trace via connect-pbid or DAX Studio; te query returns the result, not the timings breakdown.

Pitfalls

• Each Claude Bash call is a fresh shell, so a te connect from a prior call is gone; pass -m (and -s/-d) on every te query, or set TE_SESSION.
• pbir model -q (in the reports plugin) runs EVALUATE DAX only; INFO.* and DMV queries return 400 there. Use te query against the model endpoint for metadata.
• DMV-style SELECT * FROM $SYSTEM.TMSCHEMA_* works through ADOMD against a live local instance (connect-pbid), not through te query's DAX path.

Refactoring and renaming objects safely

Companion to the semantic-model skill (SKILL.md). Renaming or moving a model object is never a local edit; the name is a contract that downstream consumers depend on. Run the lineage check first, then rename, then propagate.

Working with `te`: rename with te mv <old> <new> --save or te set <obj> -q name -i "<new>" --save, but ONLY after the lineage check below. Never rename a model object in isolation.

Why renaming is dangerous

A measure, column, or table name is referenced far beyond the model. Renaming breaks, often silently:

• Report visuals bound to the old name ; the visual drops the field or errors, and Power BI does not auto-repair code-edited PBIR
• Other model objects (measures, calculated columns, calc items) that reference it ; te validate catches these, but only these
• Downstream models in a composite, and every report in any workspace that binds to this model
• Bookmarks, report-level filters, conditional formatting, and field parameters that name the old object

te's save-time validation sees model-internal breaks only. It cannot see reports or downstream models, so a structurally valid rename can still break production dashboards.

The safe rename workflow

1. Lineage check FIRST. Find every consumer before touching the name:

• the lineage-analysis skill, or fab (fabric-cli), to list reports and downstream models bound to this model across workspaces
• te deps "<obj>" and te find "<obj>" --in expressions --paths-only for model-internal references

2. Rename in the model: te mv or te set <obj> -q name, then te validate. 3. Propagate to reports: rebind every affected visual, filter, and bookmark with the pbir-cli skill (pbir locates and updates the references); for service-side items use fabric-cli (fab). 4. Re-validate: te validate the model and pbir validate each report.

Coordinated rename (the te + pbir tandem)

The canonical pattern: rename in the model with te, capture the old -> new map, then run the matching rename in pbir-cli against each downstream report so visual bindings, filters, and bookmarks follow. Treat the model rename and the report rename as one change set so the model and its reports never diverge in source control.

Pitfalls

• Renaming a measure's home table also moves the 'OldTable'[Measure] form that reports may use; check both the measure name and its table.
• Internal name vs display name can diverge; confirm what actually changed with te get <obj> --output-format tmdl.
• A field parameter references fields by NAMEOF; renaming a referenced field requires rebuilding the parameter, not just renaming.
• Renaming is the one model edit where "it validates" is not "it is safe"; the lineage step is mandatory, not optional.

Relationships and cardinality

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: enumerate with te query -q "EVALUATE INFO.VIEW.RELATIONSHIPS()" (te ls cannot list relationships). Create with te add relationship "Sales[K]->Date[K]" --save; set cross-filter / active / security behavior with te set Relationships/<name> -q <prop>, or te script (TOM) when the property is not exposed.

Detecting limited relationships and the silent drops they cause

Regular vs limited is inferred at evaluation time, not set. A relationship is limited when its cardinality is many-to-many (even if both columns hold unique values) or it crosses a source group (import-to-DQ, or DQ-to-different-DQ). Everything else 1:M or 1:1 inside one source group is regular. Direct Lake + import composites keep regular relationships across the two modes, unlike classic import + DirectQuery.

The difference changes numbers silently:

• Regular 1:M joins LEFT OUTER and synthesizes a blank "unknown member" for unmatched many-side keys, so RI violations still appear in totals under (Blank)
• Limited joins INNER, adds no blank row, and drops unmatched rows entirely ; an orphan fact row just vanishes from every aggregate. No error, a quietly understated total
• RELATED() cannot traverse a limited relationship (it errors); table expansion never happens, so the join resolves per query in multiple passes and degrades fast above low-cardinality keys

From a terminal there is no diagram, so infer and probe. INFO.VIEW.RELATIONSHIPS() lists cardinality, cross-filter behavior, and active flag (te ls cannot enumerate relationships). Flag any many-to-many row as limited; for composites cross-check storage modes and treat any cross-mode relationship as limited. Probe orphans with an anti-join (EXCEPT/NATURALLEFTOUTERJOIN on the keys) ; a non-zero orphan count on a regular relationship lands under (Blank), on a limited one those rows are silently gone (the dangerous case). If the m2m was an accident (transient duplicates at create time), fix the cardinality once the one-side is genuinely unique; confirm the property name on a live object first or fall to te script. Do not force cardinality on a real cross-source-group limited relationship ; no cardinality change makes it regular, so reduce cost instead (Dual on the shared dimension, or pull it into the Vertipaq group).

Sources: learn.microsoft.com desktop-relationships-understand; learn.microsoft.com direct-lake-web-modeling (DL+import keep regular); SQLBI strong-and-weak-relationships; repo te-cli command-reference

Cardinality and uniqueness go unvalidated in Direct Lake and web modeling

Import and DirectQuery profile columns when you create a relationship and auto-populate cardinality and direction. Direct Lake and web modeling do not. Direct Lake guesses the many side from a row-count DAX query, pre-sets single direction, and never checks one-side uniqueness; web modeling issues no validation queries at all, including for the marked date column.

The hard rule: a Direct Lake relationship's one-side column must be unique, and if duplicates exist the query fails at runtime, not at refresh or edit time. So an agent can author a structurally valid model, pass every static check, deploy it, and have every visual touching that relationship error the first time a user opens the report because the Delta table had a duplicate key. The row-count heuristic also picks the wrong many side whenever the dimension is larger than the fact (a wide SCD against a sparse fact), producing a backwards relationship.

Prove it yourself: te query COUNTROWS(Product) vs DISTINCTCOUNT(Product[ProductKey]) (rows greater than distinct means duplicates ; de-dup upstream in the Delta table before the model is usable) and a non-zero blank-key count (a second uniqueness hazard). Confirm the heuristic assigned the right many side via INFO.VIEW.RELATIONSHIPS(); correct mis-assignment explicitly. After any Direct Lake relationship change, re-run a real visual-shaped SUMMARIZECOLUMNS query to actually exercise the join, since nothing else does until a user does. Direct Lake requires exact data-type match across the relationship (string vs int is rejected; Binary/GUID must be cast to string upstream), and Direct Lake on SQL needs an explicitly marked date table joined on a real date column ; no auto date-part leniency.

Sources: learn.microsoft.com direct-lake-edit-tables (no preview, row-count heuristic, no validation); learn.microsoft.com direct-lake-overview (one-side unique or queries fail; type match); learn.microsoft.com desktop-create-and-manage-relationships

Resolving ambiguous filter paths deterministically (priority tiers and weight)

Ambiguity has two causes, only one being the expected bidirectional case: (1) a bidirectional cross-filter opening a second route, (2) a diamond schema with two paths to the same target and no bidirectional filter at all (two bridges reaching one dimension). Power BI resolves with a fixed priority-tier sequence, first matching tier wins: 1:M-only paths, then 1:M-or-M:M, then M:1, then 1:M-to-intermediate-then-M:1, then the same allowing M:M legs, then anything else. A relationship in every candidate path is dropped from consideration. Within one tier, path weight is the maximum weight of its relationships (count is irrelevant) and the higher-weight path wins; the engine never crosses tiers to chase weight. A same-tier same-weight tie is a hard ambiguous-path error, not a silent pick.

Two failure modes that look nothing alike: sometimes Desktop refuses a bidirectional change at edit time (safe), other times it accepts the topology and silently routes filters down the tier-1 path, so a measure returns plausible-but-wrong numbers. An agent that "just adds the relationship the measure needs" can flip the chosen path and change every cross-table total with no error. USERELATIONSHIP is the deliberate lever ; it raises a relationship's weight (innermost nested call gets the highest), activating an inactive relationship and breaking a same-tier weight tie toward the path you want.

Map the topology before and after any edit with INFO.VIEW.RELATIONSHIPS() plus te deps; any two tables reachable by more than one active path (including a bidirectional return route) are an ambiguity candidate. For a genuine diamond, keep one path active and others inactive, then select per measure with USERELATIONSHIP rather than leaving the engine to pick. If activating one path still raises ambiguity, nest a second USERELATIONSHIP to force ordering. Prefer the documented bidirectional alternatives (a visual-level "is not blank" filter, or CROSSFILTER(..., BOTH) scoped to one measure) over a model-level bidirectional flag, which is the usual source of accidental ambiguity. USERELATIONSHIP in a calculated column does nothing (row context) ; use LOOKUPVALUE.

Sources: learn.microsoft.com desktop-relationships-understand (resolve path ambiguity); SQLBI bidirectional-relationships-and-ambiguity-in-dax; tabulareditor.com ambiguous-paths-in-power-bi

Active/inactive relationships and the dead-inactive-relationship defect

A model holds at most one active path between two tables; extras must be inactive. Inactive relationships still participate in table expansion (so a regular one still costs refresh-time index build) but propagate no filter until a calculation wraps the query in USERELATIONSHIP. The common real-world bug, confirmed by heavy forum traffic on inactive-relationship date filtering, is an inactive relationship that no measure ever activates: it sits there, the author wires a date or visual to it expecting filtering, and gets either an unfiltered result or the active relationship's behavior. Treat an inactive relationship with zero USERELATIONSHIP references as a defect, the inverse of "missing relationship."

Cross-reference in one pass: list inactive relationships with FILTER(INFO.VIEW.RELATIONSHIPS(), NOT [IsActive]), then te find "USERELATIONSHIP" --in expressions --paths-only. Any inactive relationship whose columns never appear in a USERELATIONSHIP call is dead weight or a latent bug ; either a measure is missing, the relationship should be deleted, or (role-playing case) the dimension should have been duplicated per role with its own active relationship. Duplicate the physical dimension per role unless simultaneous multi-role filtering is genuinely needed; the shared-table-plus-inactive pattern forces a USERELATIONSHIP wrapper into every measure and breaks Q&A/Copilot, which cannot inject one. USERELATIONSHIP on an RLS-bearing relationship is blocked; relocate the filter instead.

Sources: learn.microsoft.com desktop-relationships-understand (make-this-relationship-active); learn.microsoft.com relationships-active-inactive; local forums DB (inactive-relationship date-filter threads); repo te-cli semantic-modeling-practices

Review checklist

Companion to the semantic-model skill (SKILL.md). The full audit workflow and per-category checks. Drives inspection through the te-cli-first cascade; produces prioritized, actionable findings rather than a pass/fail.

Working with `te`: gather context with scripts/get_model_info.py; inspect with te load, te ls Measures, te query -q "EVALUATE INFO.VIEW.RELATIONSHIPS()", and te vertipaq --columns --detail; gate findings with te validate and te bpa run --fail-on error.

Workflow

Step 0: gather context

Run scripts/get_model_info.py -w <workspace-id> -m <model-id> for storage mode, model size, connected reports, deployment pipeline, endorsement, sensitivity label, data sources, refresh schedule, last refresh, capacity SKU. Then ask the user: what business process the model serves; who consumes it (report developers, analysts, executives, Copilot/AI); whether they own the model, the reports, or both; whether it is in dev, test, or production; and where findings should be documented. Severity shifts with context: a model for three analysts is judged differently from one Copilot queries org-wide.

Step 1: inspect structure

Read the model with the cascade. te load ./model for a summary, te ls Measures / te ls Tables, te query -q "EVALUATE INFO.VIEW.RELATIONSHIPS()" for relationships (te ls cannot enumerate them), te vertipaq --columns --detail for size. Drop to connect-pbid for traces and storage DMVs when the endpoint is unreachable from te.

Step 2: audit by category

Walk the categories below; each links to the topic reference with the mechanics and the fix.

Step 3: performance

Query-level timing, unused-column detection, and memory analysis in performance.md; model-size diagnosis in vertipaq-optimization.md.

Step 4: report findings

Produce a markdown report: a count-by-severity summary, detailed findings with object paths and line numbers, a specific remediation per finding, and a prioritized action list (critical first).

Categories

Critical

• Bidirectional relationships creating ambiguous filter paths, and circular dependencies between tables (relationships.md)
• Missing data types on columns; orphaned tables with no relationship
• Fail-open RLS (IF(..., TRUE()) fall-through) and limited relationships that silently drop unmatched rows (security.md, relationships.md)

Memory and size (vertipaq-optimization.md)

• High-cardinality dictionaries (GUIDs, transaction IDs, composite keys); unsplit DateTime columns
• isAvailableInMDX left on hidden or high-cardinality columns (wasted attribute-hierarchy memory)
• Auto date/time tables (hidden LocalDateTable_*); wrong data types (Double for currency, String for numeric)
• Calculated columns that should be measures (dax-authoring.md); unused columns or tables

Data reduction

• Fact history with no date-range filter or incremental refresh (incremental-refresh.md)
• Columns not needed for reporting or calculations; detail grain finer than any report needs; logic better done upstream

DAX correctness (dax-authoring.md; for query tuning use the dax skill)

• Filtering whole tables instead of columns in CALCULATE; unguarded division (DIVIDE vs bare /)
• Context-blind calculated columns using CALCULATE; variable time-shift bugs

Measure hygiene

• Implicit measures where explicit measures should exist; report-scoped extension measures that belong in the model; ambiguous duplicate measure names

Documentation and AI (ai-copilot-readiness.md, metadata-and-organization.md)

• Tables/columns/measures missing descriptions (Copilot truncates after 200 characters; front-load keywords)
• Missing display folders; missing synonyms; inconsistent naming (use standardize-naming-conventions)

Design (dimensional-modeling.md, relationships.md, time-intelligence.md)

• Star-schema violations (fact-to-fact joins, snowflakes); many-to-many without a bridge
• Date table not correctly marked for the relationship column type; dead inactive relationships (no USERELATIONSHIP)
• Multiple facts on the same dimension via different keys without a conformed dimension

Direct Lake, if applicable (direct-lake.md)

• Non-unique one-side relationship keys (queries fail at runtime, not refresh); DirectQuery fallback risk (RLS, SQL views)
• Calculated columns on Direct Lake tables; Delta health (Parquet file count, V-Order, guardrails)

Notes

• The structural audit reads metadata; it does not execute report DAX or check data quality
• For companion report review, use the review-report skill (reports plugin)

Storage modes (Import / DirectQuery / Dual / Hybrid)

Companion to the semantic-model skill (SKILL.md). Original guidance; each section cites its sources.

Working with `te`: read each table mode / source with te script over Partitions[0]; set the mode via te script (TOM ModeType = import|directQuery|dual|directLake). After any change, re-list relationships with te query -q "EVALUATE INFO.VIEW.RELATIONSHIPS()" to catch newly limited ones.

Storage-mode decision matrix (Import / DirectQuery / Dual / Hybrid / Direct Lake)

Storage mode is a per-table partition property deciding where a DAX subquery is answered: VertiPaq cache (Import, Direct Lake after transcoding) or the source via native query (DirectQuery). Dual is both, decided per query. Hybrid is import partitions plus one DQ partition. Mixed modes = composite model. Direct Lake has two flavors: on OneLake (composites with Import/DQ/Dual) and on SQL (single-source, no mixing, falls back to DQ on views/granular RLS).

The biggest non-obvious lever: a dimension left Import on the one-side of a DirectQuery fact forces a cross-source-group path ; its groupings ship to the source as materialized subqueries and the relationship goes limited. Setting it Dual keeps the join intra-source-group and regular, so one native query handles slicer+fact, with slicers served from cache. You only get both behaviors from one table if it is Dual, not Import. Hybrid is the move for "latest live, history fast" (import bulk + one DQ tail), but hybrid tables do not support aggregations. Direct Lake on SQL cannot mix with DQ/Dual; on OneLake it can.

Mode-to-when: DirectQuery for large facts / near-real-time / unimportable volume; Import for tables not filtering a DQ/Hybrid fact, unreachable sources, all calculated tables; Dual for dimensions queried with a DQ/Hybrid fact from the same source; Hybrid for one fact needing live latest + fast history; Direct Lake on OneLake for very large Delta facts you do not refresh (composites with Import dims); Direct Lake on SQL for single-source Delta. There is no headless storage-mode dropdown ; mode is partition.Mode (import|directQuery|dual|directLake), set via te script (TOM ModeType). After any change, list relationship types (INFO.VIEW.RELATIONSHIPS()) to confirm you did not create limited relationships. Calculated tables are always Import regardless of what they reference; "Mixed" in Desktop is a UI label, not a fourth mode.

Sources: learn.microsoft.com service-dataset-modes-understand; learn.microsoft.com composite-model-guidance; learn.microsoft.com direct-lake-overview; learn.microsoft.com directquery-model-guidance