i

Description

Pinces accrochées à un mur d’atelier, chacune entourée du contour tracé de sa place.

Source

Syced · original page

Licence

CC0 Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

Cross-Cutting Power BI Good Practices

Practices that apply regardless of the source (Fabric / OneLake or Managed SQL Server): star schema, naming, DAX, Power Query, RLS, performance, themes, accessibility…

In brief

• Practices that apply regardless of the source (Fabric / OneLake or Managed SQL Server): star schema, naming, DAX, Power Query, RLS, performance, themes, accessibility, multilingual, versioning, deployment, governance.
• You will know how to build a readable, fast, secure, and versioned semantic model, and evolve it without breaking the reports that consume it (the lineageTag rule).
• You will have a catalog of more than forty common pitfalls with symptom, cause, and remedy.
• File interpretation: “OneLake + SaaS mobile apps” and “Managed SQL Server in SaaS” are defined in the folder README; this file depends on neither.

On this page

1. 1. Guiding Principles
   1. 🟢 Novice — Five Ideas to Remember
   2. 🟡 Practical — Where Each Decision Belongs
2. 2. Star Schema
   1. 🟢 Novice — The Principle
   2. 🟡 Practical — Concrete Rules
   3. 🔴 Expert — Edge Cases and Variants
3. 3. Naming and Conventions
   1. 🟢 Novice — Why Name Carefully
   2. 🟡 Practical — Suggested Convention
4. 4. DAX
   1. 🟢 Novice — The three concepts
   2. 🟡 Practical — Writing sustainable DAX
   3. 🔴 Expert — Engine, plans, and costly measures
5. 5. Power Query and data access layer
   1. 🟢 Novice — What Power Query is for (and what it is not for)
   2. 🟡 Practical — Rules
   3. 🔴 Expert — Fabric and SaaS
6. 6. Row-level security (RLS) and other protections
   1. 🟢 Novice — The principle
   2. 🟡 Practical — Static and dynamic RLS
   3. 🔴 Expert — Limits and interactions
7. 7. Performance
   1. 🟢 Novice — The four levers
   2. 🟡 Practical — Method
   3. 🔴 Expert — By storage mode
8. 8. Themes, visuals, and accessibility
   1. 🟢 Novice — Theme as a visual contract
   2. 🟡 Practical — Accessibility
   3. 🔴 Expert — Templates and visual governance
9. 9. Multilingual and TMDL translations
   1. 🟢 Novice — Two distinct topics
   2. 🟡 Practical — Translations in the model (cultures)
   3. 🔴 Expert — Multilingual pitfalls
10. 10. PBIP, TMDL, and Git
    1. 🟢 Novice — Why move away from .pbix
    2. 🟡 Practical — Versioning rules
    3. 🔴 Expert — Report format and drift
11. 11. Deployment pipelines and environments
    1. 🟢 Novice — Dev, Test, Prod
    2. 🟡 Practical — Rules
    3. 🔴 Expert — Full lifecycle
12. 12. Endorsement, catalog, and governance
    1. 🟢 Novice — Making what we share reliable
    2. 🟡 Practical — Useful governance, not bureaucratic
    3. 🔴 Expert — Self-service without chaos
13. 13. Measures and lineageTag
    1. 🟢 Novice — Why this is a separate topic
    2. 🟡 Practical — The absolute rule
    3. 🔴 Expert — Edge cases
14. 14. Testing and acceptance testing of numbers
    1. 🟢 Novice — Testing a report means testing numbers
    2. 🟡 Practical — Testing levels
    3. 🔴 Expert — Automate
15. 15. Documentation
    1. 🟡 Practical — What must be documented
16. 16. Common pitfalls
    1. 16.1 Model
    2. 16.2 DAX
    3. 16.3 Power Query and data
    4. 16.4 Security and governance
    5. 16.5 Delivery and operations
    6. 16.6 Design and adoption
17. 17. AI contribution
18. 18. See also

Level: 🟢→🔴

1. Guiding Principles#

🟢 Novice — Five Ideas to Remember#

1. A report is only the storefront of a model. Quality is determined in the semantic model (tables, relationships, measures) far more than on the page.
2. Put complexity at the source. Anything that can be calculated upstream (SQL, lakehouse) should be: a calculation done once upstream is better than doing it a thousand times at query time. Conversely, a calculation that depends on filter context belongs in DAX.
3. One number = one definition = one measure. A KPI that exists in three versions across three reports is trust debt.
4. Everything must be reconstructable from versioned files. If the model exists only in a .pbix on someone's machine, it does not exist.
5. Measure before optimizing, test before delivering.

🟡 Practical — Where Each Decision Belongs#

2. Star Schema#

🟢 Novice — The Principle#

A star schema (Kimball, The Data Warehouse Toolkit) separates fact tables (measurable events: visits, orders, readings) and dimension tables (descriptions: customer, product, date, user). Dimensions filter facts, never the other way around, at least in the main direction: the relationship is one-to-many, “one” side = dimension, with filtering from dimension to fact.

            DIM_Date
               │
 DIM_Client ── FACT_Visite ── DIM_Produit
               │
          DIM_Utilisateur

Why: the Power BI columnar engine (VertiPaq) compresses low-cardinality columns very well and responds quickly to filters propagated from small tables to large ones; a “flat” or deep snowflake model costs memory, readability, and increases ambiguity risk.

🟡 Practical — Concrete Rules#

🔴 Expert — Edge Cases and Variants#

• Slowly changing dimensions (SCD). Type 2: one row per attribute version, with validity bounds. In Power BI, join the fact on the valid version's surrogate key at the time of the event, not on the natural key. Document whether the report shows “attribute at event time” or “current attribute.”
• Multiple roles for one dimension (e.g., creator employee, seller employee): multiple relationships to the same dimension (one active) or multiple copies of the dimension; choose based on whether simultaneous filtering is needed.
• Fact tables with different grains (weekly forecast, daily actual): shared conformed dimensions and measures that explicitly handle grain (weekly aggregation).
• Semi-additivity (inventory, headcount): dedicated measure (LASTNONBLANK, snapshot at last date); never expose the raw column to sum.
• Calculation groups for time variants (YTD, N-1, variance): reduce dozens of derived measures, at the cost of tooling (Tabular Editor, TMDL, or Desktop's calculation group editor) and readability that must be managed carefully.
• Aggregations (Import aggregate tables in front of a DirectQuery table): deliver an order-of-magnitude gain on large tables, provided you test that queries do hit the aggregate.

3. Naming and Conventions#

🟢 Novice — Why Name Carefully#

The name is the first piece of documentation. A self-service user selects a measure by its name; a developer finds code by its name. Column names coming from a source must remain identical to the source names: renaming in Power Query breaks traceability and complicates schema refresh. User-facing labels should go through translations (§ 9), not aliases.

🟡 Practical — Suggested Convention#

Formatting rules: consistent casing, no obscure internal abbreviations, no characters that complicate DAX or TMDL (apostrophes in table names, for example), a unit suffix when two measures differ only by unit (%, k€), and systematically named variants (base, variant, period).

4. DAX#

🟢 Novice — The three concepts#

• Row context: “the current row” (calculated column, iterator).
• Filter context: all active filters (slicers, visual rows/columns, relationships, RLS).
• Context transition: CALCULATE transforms a row context into a filter context.

Revenue = SUM ( FACT_Commande[Montant] )

Revenue N-1 =
CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( DIM_Date[Date] ) )

Prefer a measure over a calculated column as soon as the value depends on filters; a calculated column is stored (memory) and recalculated at refresh.

🟡 Practical — Writing sustainable DAX#

1. Variables (VAR/RETURN): readability, single evaluation, easier debugging.
2. Safe division: DIVIDE ( num, den ) rather than / to handle a zero denominator.
3. No systematic IFERROR: it hides model errors; fix the root cause.
4. Filters on columns, not on tables: CALCULATE ( [M], DIM_Client[Pays] = "FR" ) — a full-table filter (FILTER ( ALL ( Table ), ... )) is slow and over-constraining.
5. Reusable base measures: each derived KPI should rely on a single base measure, not a copied formula.
6. Use iterators sparingly: SUMX on a large table is costly; if the expression is not row-dependent, use SUM.
7. Time intelligence with the marked date table; avoid time calculations on fact date columns.
8. “%” measures: separate numerator, denominator, ratio to test each term.
9. Formatting: standardized formatting (DAX Formatter or equivalent tool) makes reviews readable.

Coverage rate % =
VAR _Visites   = CALCULATE ( DISTINCTCOUNT ( FACT_Visite[ID_Client] ) )
VAR _Portefeuille =
    CALCULATE (
        DISTINCTCOUNT ( DIM_Client[ID_Client] ),
        REMOVEFILTERS ( FACT_Visite )
    )
RETURN
    DIVIDE ( _Visites, _Portefeuille )

🔴 Expert — Engine, plans, and costly measures#

• Two engines: the formula engine (evaluates DAX, single-threaded) and the storage engine (VertiPaq: xmSQL queries, multi-threaded). A slow measure is almost always one that puts heavy demand on the formula engine (nested iterators, large materializations) or triggers many callbacks.
• Analysis tools: Performance Analyzer (in Desktop) to identify the problematic visual, DAX Studio (queries, plans, Server Timings) to understand, Tabular Editor for scripts and good practice rules (Best Practice Analyzer), and Desktop’s DAX query view.
• Cardinality: a high-cardinality text column (IDs, second-level timestamps) is expensive; split date and time, truncate, or do not import it.
• Cascading calculated columns: avoid them; prefer upstream calculations.
• Inactive relationships and USERELATIONSHIP: test under RLS (security behavior and inactive relationships require explicit verification).
• Batch-evaluated measures: the SUMMARIZECOLUMNS function is generally more efficient than combinations of ADDCOLUMNS/SUMMARIZE for query tables; think about output grain.
• Blank values: a measure returning 0 instead of BLANK() forces display of all combinations (empty rows) and hurts performance; return BLANK() when “no data” is semantically true.

5. Power Query and data access layer#

🟢 Novice — What Power Query is for (and what it is not for)#

Power Query (M language) loads and prepares data: connection, typing, row filters, merge. It is not an industrial transformation engine: it works for light cleanup, not for joins across hundreds of millions of rows.

🟡 Practical — Rules#

let
    Source = Sql.Database(Serveur, Base),
    Faits = Source{[Schema = "dbo", Item = "FACT_Visite"]}[Data],
    Filtrage = Table.SelectRows(
        Faits,
        each [DateModification] >= RangeStart and [DateModification] < RangeEnd
    )
in
    Filtrage

🔴 Expert — Fabric and SaaS#

• With Direct Lake, Power Query disappears from model data reads: preparation lives in the lakehouse/warehouse (notebooks, pipelines, dataflows Gen2). Naming and typing rules move upstream; keep the same discipline. See 02 — OneLake + SaaS Mobile Apps: architecture.
• dataflows Gen2 are useful for repeatable preparations by less technical profiles, but consume capacity: measure it.
• SaaS sources expose quota-limited APIs: isolate extraction in a dedicated component (pipeline, function) with retry/recovery, not in an interactive Power Query query. See 03 — OneLake + SaaS Mobile Apps: Difficulties and Pitfalls.
• On the managed SQL side: expose versioned, tested reporting views; see 06 — SQL Server in SaaS — architecture.

6. Row-level security (RLS) and other protections#

🟢 Novice — The principle#

RLS limits the rows visible to each user or group. It is defined in the semantic model, with roles whose expressions filter tables (generally dimensions); filters propagate to facts through relationships.

🟡 Practical — Static and dynamic RLS#

• Static: one role per scope ([Country] = "FR"). Simple, but it proliferates.
• Dynamic: user → scope mapping table and filter on USERPRINCIPALNAME().

-- “Representative” role on DIM_User
[Email] = USERPRINCIPALNAME ()

🔴 Expert — Limits and interactions#

• OLS (object-level security) hides columns or tables; combine with RLS, taking client tool compatibility limits into account.
• Workspace owners and Contributor/Member/Admin roles bypass RLS: do not assign these roles to consumers.
• Direct Lake / SQL endpoint / DirectQuery: security must be designed at both levels (model and source); behavior depends on the identity used (SSO or fixed account) and platform feature states (to be verified, status 2026-10).
• Embedding (reports embedded in an application): effective identity and roles are passed in the embed token; an error here is a data leak.
• Performance: an RLS expression that references large tables or uses costly functions is paid at every query for every user.
• Cache: visual and query caches are partitioned by security identity; many distinct roles reduce cache hit rate.
• Sensitive data: sensitivity labels, export restrictions, no unnecessary personal data in the model (GDPR minimization).

7. Performance#

🟢 Novice — The four levers#

1. Less data: strictly necessary columns and rows only.
2. Better model: star schema, simple relationships, suitable data types.
3. Efficient measures (§ 4).
4. Fewer visuals per page: each visual triggers at least one query.

🟡 Practical — Method#

Page good practices: few visuals (around ten); dropdown slicers rather than long lists; apply page filters rather than exclusion measures; page tooltips designed to stay lightweight; bookmark buttons rather than ten nearly identical pages; avoid visuals that require tables with several thousand rows.

🔴 Expert — By storage mode#

Monitor capacity (smoothing, throttling) and concurrency: a slow page at 5 PM is often a normal page on a capacity saturated by concurrent loads. See 09 — comparison for platform trade-offs.

8. Themes, visuals, and accessibility#

🟢 Novice — Theme as a visual contract#

A JSON theme sets palette, typography, and visual styles. It guarantees consistency and allows you to change identity with a single file. A minimal theme:

{
  "name": "Academy Theme",
  "dataColors": ["#1F4E79", "#2E86AB", "#F18F01", "#C73E1D", "#6B8E23", "#7D5BA6"],
  "background": "#FFFFFF",
  "foreground": "#1B1B1B",
  "tableAccent": "#1F4E79",
  "textClasses": {
    "title":  { "fontFace": "Segoe UI Semibold", "fontSize": 14 },
    "label":  { "fontFace": "Segoe UI", "fontSize": 10 }
  }
}

🟡 Practical — Accessibility#

On mobile (Power BI app), design a dedicated vertical layout, KPI cards, few filters, and adequate touch targets; test offline if the use case requires it (offline capabilities are limited, to be verified).

🔴 Expert — Templates and visual governance#

• Report template (.pbit) or shared base report: header, footer, navigation, theme, help pages.
• Custom visuals: use only certified or audited visuals; a third-party visual is code executed in the report.
• Consistency across reports: documented component library (KPI cards, tables).
• Readability: one page = one question; titles that state the conclusion (“sales are declining in the South”) rather than the object (“Sales by region”).
• Cognitive overload: limit the number of colors, align elements, remove noise (superfluous grids, shadows).

9. Multilingual and TMDL translations#

🟢 Novice — Two distinct topics#

1. Translate metadata in the model (table names, columns, measures, display folders, descriptions): the viewer sees “Chiffre d'affaires” or “Revenue” depending on their client language.
2. Translate content (dimension values, data labels): this is handled in the data (columns by language, labels table).
3. Report text (titles, text boxes, tooltips) does not always follow model translation: plan for it separately (dynamic title measures, for example).

🟡 Practical — Translations in the model (cultures)#

The semantic model supports cultures (languages) with, for each object, a translatedCaption, translatedDescription, translatedDisplayFolder. In TMDL, they are declared in the model's cultures/ folder (one file per culture), with separate linguisticMetadata blocks.

Modèle.SemanticModel/
  definition/
    model.tmdl
    tables/
    cultures/
      fr-FR.tmdl
      en-US.tmdl
      de-DE.tmdl

Illustrative excerpt from a culture file (exact syntax must be validated against your tool version; status 2026-10, to be verified):

cultureInfo fr-FR

  translations
    model Model
      table 'FACT_Visite'
        caption: Visites
        measure 'Nb visites'
          caption: Nombre de visites
          description: Nombre de visites réalisées sur la période
          displayFolder: Activité

Good practices:

🔴 Expert — Multilingual pitfalls#

• Translations and report references: reports reference objects by their source name; translation does not change the reference, hence the importance of never renaming instead of translating.
• Dynamic title measures: SELECTEDVALUE ( LUP_Libelle[Texte] ) filtered by language, or USERCULTURE() in the measure (depending on context, verify behavior in the service).
• Sorting: translated columns must have consistent sort-by-column settings (numeric key), otherwise order changes by language.
• Client language: the service selects culture based on browser or mobile app settings; plan a default language.
• Multilingual content: favor a “label by language” model (labels table with LANG_COD) over Libelle_FR, Libelle_EN columns that multiply columns.
• Removing/adding a language: documented procedure; a new language = a new culture + scripts + acceptance testing.

10. PBIP, TMDL, and Git#

🟢 Novice — Why move away from .pbix#

A .pbix is a binary file: you can neither compare it, nor merge it, nor review it clearly. The Power BI project (.pbip) splits a report into text files: a *.SemanticModel folder (model, in TMDL) and a *.Report folder (report definition). You can then use a version control system, compare, and perform reviews.

MonRapport.pbip
MonRapport.SemanticModel/
  definition.pbism
  definition/
    model.tmdl
    relationships.tmdl
    tables/*.tmdl
    cultures/*.tmdl
MonRapport.Report/
  definition.pbir
  definition/        (PBIR format: one file per page/visual)

🟡 Practical — Versioning rules#

Tools: Power BI Desktop (developer mode), Tabular Editor, text editor with TMDL extension, Fabric Git integration (workspace synchronization with Git; provider limits and support to be verified, status 2026-10 — Azure DevOps cloud is the reference case; for on-premises TFS/Azure DevOps Server, plan a pipeline-based publishing path). For TFVC, see TFVC in Depth: the lack of native integration requires a manual/scripted process for check-in and deployment.

🔴 Expert — Report format and drift#

• PBIR (report in JSON files by page and visual) is the target format for report projects, better suited to diffs than the old monolithic format; it is becoming the default format for Desktop and the service (Microsoft announcement of September 1, 2026, rollout from mid- to late September 2026); the old format remains supported. Check documentation (status 2026-10).
• Alias-based references in visual queries: a column rename leaves stale references; script renaming and validate by opening the report.
• Manual editing of report files is possible but fragile; prefer tested scripts or the tool, and always reopen in Desktop to validate.
• Generated files (diagram layout, editor states): exclude from review or ignore them.
• Kit-based delivery: the published model comes with scripts (SQL, PowerShell); version model, source scripts, and documentation together, with a shared version; see CI/CD for data: foundations, datamarts, pipelines, and reports.

11. Deployment pipelines and environments#

🟢 Novice — Dev, Test, Prod#

Three workspaces (at minimum): Development, Test/Acceptance, Production. Develop in the first, validate in the second, publish in the third. Fabric/Power BI deployment pipelines automate promotion from one stage to the next, with deployment rules (parameters, data sources) specific to each stage.

🟡 Practical — Rules#

Alternative or complement when native deployment is not enough: publish the model via XMLA (command-line tools, TMDL scripts) from a CI/CD pipeline — operation and XMLA write-access status must be verified based on your license (status 2026-10).

🔴 Expert — Full lifecycle#

• Orchestration: database (SQL scripts) → model → reports → refresh → tests → notification. Order matters: publishing a model that references a column not yet deployed in the database breaks load.
• Backward compatibility: consuming reports depend on the model; a breaking change (rename, measure removal) requires advance notice, a coexistence period, and the consumer list (lineage).
• Model versions: visible version number (measure Version du modèle, “About” page), consistent with the repository tag.
• Data schema migration: idempotent, replayable scripts with prerequisite checks.

12. Endorsement, catalog, and governance#

🟢 Novice — Making what we share reliable#

• Promoted: “I recommend this content” (by the owner).
• Certified: validated by authorized people according to the organization's criteria.
• The catalog (OneLake catalog, Purview) makes it possible to find content and its lineage.

🟡 Practical — Useful governance, not bureaucratic#

🔴 Expert — Self-service without chaos#

• Distinguish zones: certified (controlled), team (semi-free), personal (free, unreliable). Each zone has its rules, and the user knows which one they are in.
• Composite models on top of a certified model: allow local extension without copying, but require understanding dependency chains and security (the source model's RLS applies).
• Governance measurement: rate of reports using a certified model, number of duplicated models, average report age, % of workspaces with an owner. See the matrix Governance, Catalog, and Self-Service.
• Capacity: tag workloads by domain for internal chargeback; see 12 — Cost, Licensing, and Capacity.

13. Measures and lineageTag#

🟢 Novice — Why this is a separate topic#

Each object in a model (table, column, measure) carries a lineageTag: a stable identifier that lets tools recognize “the same object” despite renaming or moving. Reports and some derived artifacts (linked reports, composite models, metrics) rely on this identity.

🟡 Practical — The absolute rule#

Operational consequences:

1. Use script instructions that replace (createOrReplace in TMDL) on the same object while preserving the lineageTag in the definition, rather than a delete then create cycle.
2. In a TMDL diff review, watch for any lineageTag line that changes or any measure that disappears then reappears: this is a warning signal.
3. Measure generators (variations) must reread the existing lineageTag and write it back.
4. Breaking changes (deletion, renaming) require prior notice and a coexistence period (old measure kept, marked obsolete in its description).
5. Keep a list of public measures (consumed outside the model) separate from internal measures.

measure 'Chiffre d''affaires' = SUM ( FACT_Commande[Montant] )
    formatString: #,0
    displayFolder: Ventes
    lineageTag: 3f8d2c1e-0b7a-4d59-9a62-5c1d7e4b9a10

🔴 Expert — Edge cases#

• Model duplication (project copy): lineageTags are copied; two “sibling” models share identities; this may be intentional (fork) or a source of confusion in lineage — decide.
• Branch merge: two branches that add the same measure with different lineageTags create a logical conflict, not a textual one: review is essential.
• Refactoring tools (bulk rename): verify that they preserve tags.
• Client reports: reports outside the team's direct scope (delivered to clients, adapted locally) are the ones that break silently: inventory them.

14. Testing and acceptance testing of numbers#

🟢 Novice — Testing a report means testing numbers#

The most costly error in BI is not an interface bug: it is a wrong number that no one challenges. So you need business acceptance testing on known numbers.

🟡 Practical — Testing levels#

// DAX test (to run in DAX Studio or query view): difference between two definitions
EVALUATE
ROW (
    "Mesure", [Chiffre d'affaires],
    "Contrôle", CALCULATE ( SUMX ( FACT_Commande, FACT_Commande[Qte] * FACT_Commande[PrixUnitaire] ) ),
    "Ecart", [Chiffre d'affaires]
        - CALCULATE ( SUMX ( FACT_Commande, FACT_Commande[Qte] * FACT_Commande[PrixUnitaire] ) )
)

🔴 Expert — Automate#

• Fixed reference set: expected numbers over a closed period, stored in the repository; any measure change reruns the set.
• Executing DAX queries by command line or API from the pipeline (published model query endpoint; limits and permissions to verify).
• Continuous data tests after each load (alert thresholds: volume variation > x% day to day).
• Test data: representative datasets, edge cases (null values, accented characters, time zones, end of month, leap years, customers without visits).
• Language switch tests: display the report in each language.
• Tolerance: decide whether acceptable variance is absolute, relative, or zero (exact counts, amounts to rounding precision).

15. Documentation#

🟡 Practical — What must be documented#

16. Common pitfalls#

Cross-functional catalog: symptom → cause → remedy.

16.1 Model#

16.2 DAX#

16.3 Power Query and data#

16.4 Security and governance#

16.5 Delivery and operations#

16.6 Design and adoption#

17. AI contribution#

Prompt examples:

• “Here is the business definition of the coverage rate and the model schema (tables, grains, relationships). Write the DAX measure and three test cases with expected results. Flag any definition ambiguity.”
• “Here is a TMDL diff. List changes likely to break downstream consumer reports (rename, deletion, lineageTag change).”

18. See also#

• In this section: Section portal · 09 — Comparison — OneLake / Fabric vs Managed SQL Server for Power BI Reporting · 11 — Power BI Reporting Project Checklists · 01 — OneLake + SaaS Mobile Apps: Overview · 05 — SQL Server in SaaS — overview · 07 — SQL Server in SaaS — difficulties and pitfalls
• BI Development matrix: README · Semantic Model · DAX Measures and Calculations · RLS Security and Access · Version Control and Industrialization of Reports · Testing and Reliability of Numbers · Visualization and UX
• Tools and methods: VCS and SQL/BI data · Git in Depth · TFVC in Depth · CI/CD · CI/CD for data: foundations, datamarts, pipelines, and reports
• References: GLOSSARY · RATING · Bibliography and Resources · Cross-Functional Summary — AI Contribution

✻ Written by Claude Sonnet 5.5