Power BI vs Microsoft Fabric: What Stays in Your PBIX and What Moves to the Lakehouse

1. Think Architecture First: PBIX vs Fabric Roles

Before deciding where a specific transformation lives, be clear about the roles:

Power BI (PBIX / semantic model) is best at:

• Data visualization and storytelling
• Business-friendly semantic models (measures, relationships, perspectives)
• Light modelling and shaping for a few tables
• Row-level security and calculation logic

Fabric Lakehouse / Warehouse / Dataflows are best at:

• Heavy data preparation and integration
• Large-scale joins, aggregations, and historical data
• Reusable, governed datasets shared by many reports
• Data engineering patterns (CDC, slowly changing dimensions, partitioning)

A useful mental model:

• Fabric = kitchen (raw ingredients, prep, cooking)
• Power BI = restaurant table (presentation, slicing, consumption)

Anything that feels like cooking belongs in Fabric.

2. A Simple Decision Framework: 5 Questions

When you’re unsure where something should live, run it through these five questions.

2.1 Volume: How Big and How Many Rows?

Ask:

• Does this table grow to tens of millions of rows or more?
• Do you need long history (years) at detailed grain?

If yes, prefer:

• Lakehouse / Warehouse for storage and heavy transformations
• Import or Direct Lake to Power BI with pre-aggregated tables

Keep in PBIX only:

• Small helper tables
• Aggregation tables derived from Lakehouse data

2.2 Reuse: Is This Logic Used in Many Reports?

Ask:

• Will multiple reports need this cleaned dimension or fact?
• Is this calculation part of a standard KPI definition?

If yes, move it out of PBIX:

• Implement core data shaping in Lakehouse (Spark / SQL) or Dataflows Gen2
• Expose clean tables via Warehouse or Lakehouse shortcuts

PBIX should reference the curated tables; avoid copy-pasting Power Query logic across PBIX files.

2.3 Complexity: Does This Transform Feel Like ETL?

Red flags that your PBIX is doing ETL work:

• Multi-step merges of large tables
• Complex incremental patterns in Power Query
• Heavy use of custom functions for row-by-row logic

These are better in Fabric:

• Use Spark notebooks for complex business rules
• Use Warehouse for large joins and aggregations

Keep in PBIX:

• Lightweight shaping (renaming, simple filters)
• Business-friendly calculated columns that truly belong in the semantic layer

2.4 Performance: Where Is the Bottleneck?

If you see:

• Slow refreshes
• Queries timing out
• Desktop becoming sluggish while editing

Check:

1. Refresh duration: Long Power Query steps over big tables → move those steps to Lakehouse/SQL.
2. Query diagnostics: Expensive queries at report time → consider pre-aggregations in Fabric.

2.5 Governance: Who Owns the Logic?

Ask:

• Is this logic official, audited, or compliance-sensitive?
• Do you need lineage, approvals, and change control?

If yes, push it into Fabric:

• Implement as Lakehouse tables or Warehouse views
• Use semantic model measures for presentation logic only

PBIX becomes a consumer of governed data, not the source of truth.

3. What Should Stay in Your PBIX

Not everything belongs in a Lakehouse. PBIX still has a clear job.

3.1 Measures and Business Calculations

DAX measures are the semantic layer, and they belong in the model, not in Fabric.

Examples that should stay in PBIX:

• Time intelligence: YTD, MTD, rolling periods
• Ratios and percentages
• Business KPI thresholds

Sales YTD = 
CALCULATE(
    [Total Sales],
    DATESYTD('Date'[Date])
)

Gross Margin % = 
DIVIDE([Gross Margin], [Total Sales])

Keep these in PBIX even if the underlying tables come from a Lakehouse.

3.2 Light Modelling and Helper Tables

PBIX is fine for:

• Simple calculated columns that depend on model relationships
• Small disconnected tables (e.g., parameter or selection tables)

Sales Bucket = 
SWITCH(
    TRUE(),
    'Sales'[Amount] < 1000, "Small",
    'Sales'[Amount] < 10000, "Medium",
    "Large"
)

Parameter table example:

What If Discount = 
ADDCOLUMNS(
    GENERATESERIES(0, 20, 1),
    "Discount Label", FORMAT([Value] / 100, "0%")
)

These are tightly coupled to how the report works, so they belong in the PBIX.

3.3 Report-Specific Shaping

Keep in PBIX:

• Renaming columns for readability
• Hiding technical columns
• Simple filters that are only relevant to one report

If your shaping logic is:

• One or two steps
• Only used by this report
• Not performance-critical

…it’s fine to keep it in the PBIX Power Query.

4. What Should Move to Lakehouse or Warehouse

Now the other side: what should clearly leave the PBIX.

4.1 Heavy Joins and Aggregations

If you’re joining large tables in Power Query or DAX, move that logic to Fabric.

Example Warehouse view:

CREATE VIEW vw_SalesFact AS
SELECT 
    f.SalesId,
    f.SalesDate,
    f.CustomerId,
    f.ProductId,
    f.Amount,
    c.Region,
    p.Category
FROM FactSales f
LEFT JOIN DimCustomer c ON f.CustomerId = c.CustomerId
LEFT JOIN DimProduct p ON f.ProductId = p.ProductId;

Power BI then imports vw_SalesFact instead of raw tables and complex merges.

4.2 Complex Business Rules and Data Quality

Data quality rules and complex transformations should be centralised.

Examples:

• Multi-step cleansing of customer data
• Standardising product hierarchies from multiple systems
• Applying business calendars or special fiscal rules

In a Lakehouse notebook:

from pyspark.sql.functions import col, trim, upper, when

customers = spark.read.table("raw.Customers")

clean_customers = (
    customers
    .withColumn("CustomerName", trim(col("CustomerName")))
    .withColumn("CustomerName", upper(col("CustomerName")))
    .withColumn("IsActive", when(col("Status") == "A", True).otherwise(False))
)

clean_customers.write.mode("overwrite").saveAsTable("curated.DimCustomer")

Power BI connects to curated.DimCustomer, not raw.Customers.

4.3 Slowly Changing Dimensions and History

Anything involving:

• SCD Type 2 dimensions
• Audit fields (CreatedOn, ModifiedOn)
• CDC or incremental ingestion

…belongs in Fabric.

In a Warehouse, you handle SCD logic once, then expose the result as a dimension table. Power BI should not be simulating SCD behaviour via DAX or complex Power Query.

4.4 Shared, Certified Data Models

If a table or metric is:

• Used by multiple teams
• Considered a corporate KPI
• Needs certification and clear ownership

…it should be built in Fabric and surfaced via:

• A central semantic model over the Warehouse/Lakehouse
• Shared with thin report PBIX files

Thin reports contain visuals and measures referencing the central model, not their own data imports.

5. Common Patterns: From Monolithic PBIX to Fabric-Centric

Most teams start with a monolithic PBIX that does everything. Here’s how to refactor.

5.1 Pattern: Monolithic PBIX → Lakehouse + Thin Reports

Starting state:

• One PBIX with Power Query, model, measures, and visuals
• Multiple copies of that PBIX for different audiences

Target state:

1. Create a Lakehouse or Warehouse.
2. Move heavy Power Query logic to Lakehouse notebooks or SQL views.
3. Build a central semantic model on top of the curated tables.
4. Create thin PBIX files that connect live to the central model.

Benefits:

• Single source of truth
• Smaller PBIX files
• Easier refresh troubleshooting

5.2 Pattern: Dataflows Gen2 as a Bridge

If you’re not ready to go full Lakehouse, Dataflows Gen2 can be a stepping stone.

Use Dataflows Gen2 when:

• You want to reuse Power Query logic across multiple models
• You’re comfortable in Power Query but not yet in Spark/SQL

Flow:

1. Move shared Power Query logic into a Dataflow Gen2.
2. Store output in a Lakehouse or Warehouse.
3. Point multiple semantic models to the same curated tables.

6. Practical Heuristics and Anti-Patterns

When in doubt, use these quick rules.

6.1 Keep in PBIX When…

• The table is small (< a few hundred thousand rows).
• The transformation is simple and report-specific.
• The logic is purely presentational (bucketing, labels, thresholds).
• You’re prototyping and validating with users.

6.2 Move to Fabric When…

• You’re joining multiple large tables.
• Refreshes are slow or failing.
• Multiple reports need the same cleaned table.
• You’re implementing data quality or compliance rules.

6.3 Anti-Patterns to Avoid

• PBIX as a mini data warehouse: dozens of tables, complex ETL in Power Query, long refresh times.
• Copy-paste models: same Power Query and DAX logic cloned across many PBIX files.
• DirectQuery on messy sources: pushing complex logic to a non-optimised source instead of staging in Fabric.

When you see these, treat them as refactoring candidates into Lakehouse/Warehouse.

7. One Concrete Next Step

Pick your most important PBIX file and:

1. List the tables by size and refresh time.
2. Mark any table that is both large and used in more than one report.
3. For those tables, plan to move the heavy joins and cleaning steps into a Lakehouse or Warehouse over the next iteration.

You don’t need to rebuild everything in Fabric at once; start by shifting the worst ETL offenders out of PBIX, and let Power BI focus on what it does best: modelling and reporting.