Power BI design + Microsoft Fabric + Dataverse-Fabric-Link patterns

Source: Claude.ai chat (original: power-bi-design-tips-and-microsoft-fabric-integration__06e78a2a.md) Added: 2026-10-02

Summary

• Where 80% of Power BI problems originate: the data model. Star schema always; kill bidirectional relationships; mark your date table + disable Auto Date/Time globally; cardinality (distinct values per column) is the compression story — row count isn't.
• DAX hierarchy: measures over calculated columns · VAR aggressively · prefer CALCULATE(SUM(x), Table[Status] = "Open") to FILTER() · DIVIDE() not /, SELECTEDVALUE() not VALUES().
• Fabric reframes the architecture: Dataverse → Fabric Link → Lakehouse in OneLake → Direct Lake semantic model → Power BI. No refresh windows, no gateway, no Synapse pipelines. "Export to Data Lake" and "Azure Synapse Link for Dataverse" are deprecated/retiring; Fabric Link is the migration target.

Key takeaways (Dataverse-specific traps that bite D365 consultants)

• Dataverse security does NOT flow into Power BI. Business units, teams, hierarchical security, field-level security — none applies once data leaves Dataverse. Must reconstruct as RLS. Clients always assume it's automatic.
• Choice columns come through as integer + separate label; formatted-value handling differs per connector. Multi-select choices arrive as delimited strings and need unpivoting into a bridge table.
• TDS endpoint is convenient for ad-hoc SQL but has hard limits (row caps, timeouts, no server-side aggregation). Fine for exploration, wrong for a production semantic model.
• Native Dataverse/CDS connector is OData-backed and crawls on tables over ~a few hundred thousand rows.
• Activity tables (activitypointer + subtype tables) are a modelling trap — polymorphic regardingobjectid needs a bridge or role-playing dimension.
• CIJ interaction data is high-volume with its own analytics schema — email opens/clicks/sends at scale should NEVER go through the standard connector.
• Soft deletes: replicated Dataverse data carries an IsDelete marker — if you don't filter it, deleted records reappear in reports. This one embarrasses people in front of clients.
• Embedding Power BI in model-driven apps requires a tenant-level setting + per-env Power BI embedding setting. Reports embedded in forms honour Power BI RLS, not Dataverse security — surprises everyone.
• Direct Lake fallback conditions (unsupported DAX, views, certain data types) silently drop the model to DirectQuery and gets slow. Know the fallback list before you promise performance.

Key takeaways (report + ALM)

• Visual count per page is the biggest render-time driver — each visual is at least one DAX query. Aim < 15, question anything > 20.
• PBIP format for Git-friendly semantic models + reports (text files, not binary .pbix) — the single biggest quality-of-life upgrade for repo workflows.
• Separate semantic model workspace from report workspace; reports connect via live connection to a shared model. Most orgs get this wrong → model sprawl.
• Publish via apps with audiences, not workspace access.
• Row-level security is defined in the model, not the report. Dynamic RLS uses USERPRINCIPALNAME() against a user-mapping dimension. RLS does not apply to workspace Admin/Member/Contributor roles — "test as role" in Desktop is not the same as testing as a real viewer.
• Object-level security (hiding columns/tables) is Tabular-Editor-only — not Power BI Desktop.
• Fabric capacity model: F SKUs (F2 → F2048), pay-as-you-go or reserved, with F64 as the threshold where report consumers no longer each need a Pro licence. For nonprofits, F64 vs. per-user Pro arithmetic is a real conversation — Microsoft nonprofit grants complicate it.

Learnings / how to apply (positioning for a D365 consultant pivoting to Fabric)

• The bridge role is scarce: most Power BI developers can't model Dataverse. Most D365 consultants can't build a semantic model. When a Fabric Link implementation stalls, it's almost never because the DAX was hard — it's because nobody understood business units, choice-column semantics, activity-table explosion, or that CASL consent lives on a contact point rather than a contact.
• Defensible pitch: "I architect the Dataverse-to-Fabric analytics layer for Dynamics 365 implementations. I own the pipeline, the semantic model, the security model, and the licensing decision. I work with report developers on visual design." Backable, differentiated, true.
• Avoid "experienced Power BI and Fabric expert" — invites a deep technical probe; a gap on context transition or Direct Lake fallback shows immediately.
• Return-ordered gap closers: (1) DP-600 (Fabric Analytics Engineer) + PL-300 — both achievable in weeks given a D365 base. (2) DAX fundamentals properly — evaluation context, context transition, CALCULATE semantics (~20 hrs to competence). (3) One real end-to-end demo — anonymised client schema → Fabric Link → Lakehouse → Direct Lake semantic model with dynamic RLS mirroring Dataverse BUs → PBIP in Git → deployed through a pipeline. (4) Write up "Dataverse to Fabric: what breaks and why" — publishing the traps credibly establishes expertise.
• Talk in decisions + tradeoffs, not tool lists. "We used Fabric Link over Synapse Link because the client had no Azure landing zone and didn't need custom transformation on ingest, and we accepted the table-scope constraints" reads as experience. "I'm proficient in Power BI, Fabric, and DAX" reads as a bullet list.

---