Designing a Reusable Power BI Semantic Model for Multi-Fact Analysis

We are going to design a Power BI semantic model that serves multiple reports built on top of a Snowflake / SQL warehouse using a typical Gold layer (fact and dimension tables) for a hybrid DeFi & TradFi analytics platform. The original question came from a real analytical query that joins two fact tables (FACT_TRADE_EXECUTION and FACT_WALLET_ACCOUNT) with several conformed dimensions (DIM_PROTOCOL, DIM_INVESTOR_TIER, DIM_ASSET_CLASS, DIM_INSTRUMENT_TYPE) and later mixes ledger-statement logic with window functions (ROW_NUMBER, SUM() OVER (...)).

The design decision was: one semantic model per fact table, one model per business subject area, or a single wide custom query that pre-computes everything?

This article summarizes the conclusions and aligns them with current Microsoft and community guidance.

TL;DR

• Avoid “one semantic model per fact table” as a default. Prefer subject-area semantic models (a star schema that covers a coherent business domain) so multiple reports can share the same model.
• Keep the model in star schema: narrow facts + wide conformed dimensions.
• For complex row-level logic — UNION of two sources, “most recent ledger statement” via ROW_NUMBER() and inequality joins, running totals, fully-settled flag — push the work to the warehouse view (Gold) or to a transformation dataflow. Don’t try to recreate window functions in DAX.
• Model relationships should mirror the real cardinality of the data: many-to-one from facts to dimensions, single-direction filters by default.
• Use a composite model only when governance forces datasets to remain separate but reports still need to combine them. It is not the first choice.

The Original Problem

Two patterns appear in the same analysis:

1. Multi-fact join with shared dimensions

SELECT *
FROM      GOLD.FACT_TRADE_EXECUTION       te
JOIN      GOLD.FACT_WALLET_ACCOUNT        wa  ON te.wallet_id              = wa.wallet_id
JOIN      GOLD.DIM_PROTOCOL               p   ON wa.protocol_id            = p.protocol_id
LEFT JOIN GOLD.DIM_INVESTOR_TIER          it  ON wa.investor_tier_id       = it.investor_tier_id
LEFT JOIN GOLD.DIM_ASSET_CLASS            ac  ON wa.asset_class_id         = ac.asset_class_id
LEFT JOIN GOLD.DIM_INSTRUMENT_TYPE        it2 ON te.instrument_code        = it2.instrument_code
WHERE     te.instrument_code IN ('001','002','003', ...);

2. Row-level enrichment (after a UNION with an OTC source)

• “Most recent ledger statement on or before the settlement date” → LEFT JOIN with inequality + ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...).
• Running total of settlements per ledger using:

  SUM(...) OVER (
      PARTITION BY ledger_id
      ORDER BY     execution_date, trade_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )

• Fully-settled flag derived from the running total versus settlement totals.

Key insight: Pattern (1) is exactly what Power BI’s engine is built for. Pattern (2) is not what DAX measures and relationships are good at.

Why Not “One Semantic Model per Fact”

The intuition (“one fact, one model”) sounds tidy, but in practice:

• Reports that need both FACT_TRADE_EXECUTION and FACT_WALLET_ACCOUNT cannot be served by a single model that only contains one of them.
• You end up duplicating dimension definitions and business logic across models, which directly contradicts current best practice for datasets as managed data products.
• Cross-fact KPIs (e.g. on-chain settlements versus off-chain wallet balances) become brittle and hard to govern.

A better mental model is subject-area semantic models: a single star schema covering “Trade Settlement” with the trade-execution fact at the center, wallet-related attributes as a dimension, conformed dimensions on the side, and (optionally) a separate snapshot fact when its grain truly differs.

Recommended Architecture

Star schema in the semantic model

• Fact: FACT_TRADE_EXECUTION (transaction / trade grain) — and, where needed, a unified settlements fact built in Gold (see below).
• Wallet dimension: built from FACT_WALLET_ACCOUNT. In the semantic model it behaves as a slowly-changing dimension that carries WALLET_ADDRESS, AUTO_REINVEST, KYC fields, FK to DIM_PROTOCOL, etc.
• Conformed dimensions: DIM_PROTOCOL, DIM_INVESTOR_TIER, DIM_ASSET_CLASS, DIM_INSTRUMENT_TYPE, plus a marked Date table.
• Relationships: many-to-one from facts to dimensions, single direction by default. Avoid bidirectional filters unless an ambiguity is clearly needed.

Where each piece of logic should live

Logic Best home Why

This mirrors the layered guidance in Microsoft’s dataflows best practices:

• Staging dataflows extract raw data with minimal transformation.
• Transformation dataflows centralize business rules.
• Computed entities assemble reusable building blocks.
• The semantic model on top stays slim, well-shaped, and shared.

For a warehouse-centric team the equivalent is: Gold view = transformation layer, dataflow = thin pass-through or staging, semantic model = star schema + measures.

Reusability Across Many Reports

Because we want this model reused by many reports, the following practices apply:

• Treat the semantic model as a certified data product: clear ownership, description, and refresh SLA.
• Hide internal columns (surrogate keys, technical flags) and expose only business-meaningful fields and measures.
• Centralize measures in a dedicated measures table or organized folders.
• Avoid bidirectional filters and high-cardinality columns when possible.
• Use Import mode by default; consider DirectQuery or hybrid only when required for freshness; consider composite models only as an explicit governance choice.
• Document the model in your repository, not only in the .pbix.

Decision Checklist

Use this checklist for any future analytical request that looks like the original one:

• Is the request cross-fact? If yes, design at the subject-area level.
• Is the row-level logic just joins + filters? Keep it in the semantic model.
• Does the row-level logic require window functions, ranking, inequality joins, or unions of heterogeneous sources? Move it to a Gold view (or transformation dataflow).
• Will more than one report consume it? Promote the dataset to certified and lock down breaking changes.
• Are KPIs additive across the chosen grain? If not, design measures with explicit filter context — do not encode them as columns.

References

• Microsoft Learn — Understand star schema and the importance for Power BI
• Microsoft Learn — Model relationships in Power BI Desktop
• Microsoft Learn — Composite model guidance in Power BI Desktop
• Microsoft Learn — Optimization guide for Power BI
• Microsoft Learn — Dataflows best practices
• Microsoft Learn — Develop solutions with dataflows
• Power BI Consulting — Power BI Semantic Model Best Practices: Treating Datasets as Managed Data Products