Home > Docs > Best Practices > Power BI Best Practices

📊 Power BI Best Practices for Microsoft Fabric¶

Last Updated: 2026-04-21 | Version: 1.0.0

📑 Table of Contents¶

• 🎯 Overview
• 🧮 DAX Optimization
• 🏗️ Semantic Model Design
• ⚡ Report Performance
• 🔷 Direct Lake Optimization
• 🔄 Incremental Refresh & Aggregations
• 🧩 Calculation Groups & Field Parameters
• 🎰 Casino Implementation
• 🏛️ Federal Agency Implementation
• ⚠️ Limitations
• 📚 References
• 🔗 Related Documents

🎯 Overview¶

Power BI in Microsoft Fabric operates differently from Power BI Premium or Pro. Fabric's unified capacity model, Direct Lake connectivity, and OneLake-native storage fundamentally change how semantic models should be designed and optimized. This guide covers Fabric-specific best practices across DAX, model design, report performance, and Direct Lake tuning.

Power BI in Fabric vs Traditional Power BI¶

flowchart LR
    subgraph OneLake["📦 OneLake"]
        DT["Delta Tables<br/>V-Order Optimized"]
    end

    subgraph DirectLake["🔷 Direct Lake"]
        FR["Framing<br/>(metadata sync)"]
        VP["VertiPaq Engine<br/>(in-memory read)"]
    end

    subgraph Reports["📊 Reports"]
        R1["Floor Dashboard"]
        R2["Compliance Report"]
        R3["Player Analytics"]
    end

    DT -->|"No data copy"| FR --> VP --> Reports

    style OneLake fill:#2471A3,stroke:#1A5276,color:#fff
    style DirectLake fill:#27AE60,stroke:#1E8449,color:#fff

🧮 DAX Optimization¶

CALCULATE Best Practices¶

CALCULATE is the most powerful and most frequently misused DAX function. Follow these rules for optimal performance:

Iterator vs Aggregator Functions¶

// ❌ BAD: Iterator on 10M rows — scans every row
Slow Revenue = SUMX(Transactions, Transactions[Quantity] * Transactions[UnitPrice])

// ✅ GOOD: Pre-computed column + aggregator — storage engine only
Fast Revenue = SUM(Transactions[LineTotal])
// Where LineTotal is a calculated column: Quantity * UnitPrice

Decision Guide:

flowchart TD
    Start([Need to aggregate?]) --> Q1{Can it be<br/>a simple SUM/COUNT<br/>on one column?}
    Q1 -->|Yes| Agg["Use SUM, COUNT,<br/>AVERAGE, MIN, MAX"]
    Q1 -->|No| Q2{Does it need<br/>row-by-row logic?}
    Q2 -->|Yes| Q3{Table > 1M rows?}
    Q2 -->|No| Agg
    Q3 -->|Yes| CalcCol["Add a calculated column<br/>or Delta computed column,<br/>then SUM"]
    Q3 -->|No| Iter["Use SUMX/COUNTX<br/>with tight filter"]

Variables for Performance¶

Variables are evaluated once and cached. Use them to avoid redundant sub-expression evaluation:

// ✅ GOOD: Variables prevent double evaluation
Revenue Variance % =
VAR _CurrentRevenue = [Total Revenue]
VAR _TargetRevenue = [Revenue Target]
VAR _Variance = _CurrentRevenue - _TargetRevenue
RETURN
    IF(
        _TargetRevenue <> 0,
        DIVIDE(_Variance, _TargetRevenue),
        BLANK()
    )

// ❌ BAD: Measures evaluated twice
Revenue Variance % Bad =
DIVIDE(
    [Total Revenue] - [Revenue Target],
    [Revenue Target]
)

SUMMARIZECOLUMNS vs ADDCOLUMNS¶

// ✅ Best for external queries (Fabric MCP, SSMS, DAX Studio)
EVALUATE
SUMMARIZECOLUMNS(
    'Machine'[FloorLocation],
    'Date'[Month],
    "Total Revenue", [Total Revenue],
    "Avg Hold Pct", [Average Hold Percentage]
)

// ✅ Good for measure definitions
Top Floors =
ADDCOLUMNS(
    TOPN(5, VALUES('Machine'[FloorLocation]), [Total Revenue], DESC),
    "Revenue", [Total Revenue],
    "Machine Count", [Active Machine Count]
)

🏗️ Semantic Model Design¶

Star Schema Principles¶

Every Fabric semantic model should follow strict star schema design. Snowflake schemas and flat tables degrade both DAX performance and user experience.

erDiagram
    FactTransactions ||--o{ DimMachine : machine_id
    FactTransactions ||--o{ DimPlayer : player_id
    FactTransactions ||--o{ DimDate : date_key
    FactTransactions ||--o{ DimTime : time_key
    FactTransactions ||--o{ DimLocation : location_id

    FactTransactions {
        bigint transaction_id PK
        int machine_id FK
        int player_id FK
        int date_key FK
        int time_key FK
        int location_id FK
        decimal wager_amount
        decimal payout_amount
        decimal net_revenue
        int spin_count
    }

    DimMachine {
        int machine_id PK
        string machine_name
        string denomination
        string manufacturer
        string floor_location
    }

    DimPlayer {
        int player_id PK
        string tier_level
        string age_group
        string state
    }

    DimDate {
        int date_key PK
        date full_date
        string month_name
        int fiscal_quarter
    }

Relationship Best Practices¶

Cardinality Optimization¶

⚡ Report Performance¶

Visual Reduction Strategy¶

Each visual on a page generates one or more DAX queries. Reducing visuals directly reduces query count and render time.

High-Cardinality Avoidance¶

Never place high-cardinality columns directly on visuals:

❌ Transaction ID on a table visual (10M distinct values)
❌ Timestamp with seconds on an axis (8.6M values/day)
❌ Raw IP address as a slicer

✅ Date (day grain) on axis (~365 values)
✅ Hour bucket on axis (24 values)
✅ Machine ID on a filtered page (< 5,000 values)

Bookmarks Over Complex Visuals¶

Use bookmarks to swap visual states instead of conditional visibility measures:

🔷 Direct Lake Optimization¶

V-Order Configuration¶

V-Order is a write-time optimization that arranges Delta Parquet data for optimal VertiPaq scanning. It is critical for Direct Lake performance.

# Enable V-Order in Spark notebook writes
df.write \
    .format("delta") \
    .option("vorder.enabled", "true") \
    .mode("overwrite") \
    .saveAsTable("lh_gold.gold_slot_performance")

Framing Best Practices¶

Framing is how Direct Lake syncs metadata with the underlying Delta table. Unlike Import refresh, framing copies no data — it updates the VertiPaq engine's pointers to Parquet files.

Framing Guardrails:

Fallback Behavior¶

When Direct Lake cannot serve a query (guardrail exceeded, unsupported DAX), it falls back to DirectQuery against the SQL endpoint. This is significantly slower.

flowchart TD
    Q["DAX Query"] --> DL{Direct Lake<br/>can serve?}
    DL -->|Yes| VP["VertiPaq Engine<br/>⚡ Fast (ms)"]
    DL -->|No| FB{Fallback<br/>enabled?}
    FB -->|Yes| DQ["DirectQuery<br/>🐌 Slow (seconds)"]
    FB -->|No| ERR["Error returned"]

    VP --> R["Result"]
    DQ --> R

Monitor fallback events:

// Detect Direct Lake fallback events
FabricDatasetMetrics
| where TimeGenerated > ago(24h)
| where OperationName == "DirectLakeFallback"
| project TimeGenerated, DatasetName, TableName, FallbackReason
| summarize FallbackCount = count() by DatasetName, FallbackReason
| order by FallbackCount desc

🔄 Incremental Refresh & Aggregations¶

Incremental Refresh in Fabric¶

For Import-mode datasets (when Direct Lake is not applicable), configure incremental refresh to minimize CU consumption:

Aggregation Tables¶

Pre-aggregate high-volume fact tables into smaller rollup tables for fast dashboard rendering:

flowchart TB
    subgraph Detail["Detail Table (500M rows)"]
        FT["fact_slot_transactions<br/>Grain: per-spin"]
    end

    subgraph Agg["Aggregation Table (2M rows)"]
        AG["agg_slot_daily<br/>Grain: machine × day"]
    end

    subgraph Model["Semantic Model"]
        SM["Auto-routes queries<br/>to smallest table<br/>that satisfies grain"]
    end

    FT -->|"Daily SUMMARIZE"| AG
    AG -->|"Direct Lake"| SM
    FT -.->|"Drill-through only"| SM

🧩 Calculation Groups & Field Parameters¶

Calculation Groups¶

Calculation groups reduce measure proliferation by applying time intelligence transformations dynamically:

// Single calculation group replaces 5× measure clones
// Create in Tabular Editor or Fabric semantic model UI

// Calculation Group: Time Intelligence
// Items:
//   Current Period:    SELECTEDMEASURE()
//   Prior Period:      CALCULATE(SELECTEDMEASURE(), DATEADD('Date'[Date], -1, MONTH))
//   YoY Change:        SELECTEDMEASURE() - CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date]))
//   YoY %:             DIVIDE(SELECTEDMEASURE() - CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date])), CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date])))
//   YTD:               CALCULATE(SELECTEDMEASURE(), DATESYTD('Date'[Date]))

Before: 5 measures × 10 KPIs = 50 measures After: 10 KPIs + 1 calculation group (5 items) = 15 objects

Field Parameters¶

Field parameters let users dynamically switch dimensions or measures in visuals without separate pages:

// Field parameter: Metric Selector
Metric Selector = {
    ("Revenue", NAMEOF('Measures'[Total Revenue]), 0),
    ("Payout", NAMEOF('Measures'[Total Payout]), 1),
    ("Hold %", NAMEOF('Measures'[Hold Percentage]), 2),
    ("Spin Count", NAMEOF('Measures'[Total Spins]), 3)
}

🎰 Casino Implementation¶

Floor Performance Dashboard¶

A high-performance floor dashboard using Direct Lake, aggregation tables, and optimized DAX:

Compliance Dashboard¶

// CTR filing status measure
CTR Filing Rate =
VAR _TotalRequired =
    COUNTROWS(
        FILTER(
            Transactions,
            Transactions[amount] >= 10000
        )
    )
VAR _Filed =
    COUNTROWS(
        FILTER(
            ComplianceFilings,
            ComplianceFilings[filing_type] = "CTR"
                && ComplianceFilings[status] = "Filed"
        )
    )
RETURN
    DIVIDE(_Filed, _TotalRequired)

// SAR structuring detection
Structuring Alerts =
COUNTROWS(
    FILTER(
        VALUES(Player[player_id]),
        VAR _TxnCount =
            CALCULATE(
                COUNTROWS(Transactions),
                Transactions[amount] >= 8000
                    && Transactions[amount] < 10000,
                DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -7, DAY)
            )
        RETURN _TxnCount >= 3
    )
)

Player Analytics Model¶

🏛️ Federal Agency Implementation¶

Cross-Agency KPI Dashboard¶

Federal agencies share a unified KPI dashboard with agency-specific measures and field parameters for dynamic switching:

flowchart TB
    subgraph Params["Field Parameters"]
        AP["Agency Selector<br/>USDA | SBA | NOAA | EPA | DOI"]
        MP["Metric Selector<br/>Volume | Budget | Compliance"]
        TP["Time Period<br/>MTD | QTD | YTD | FYTD"]
    end

    subgraph Model["Semantic Model"]
        CG["Calculation Group<br/>Time Intelligence"]
        RLS["Row-Level Security<br/>Agency-based"]
    end

    subgraph Visuals["Report Page"]
        KPI["KPI Cards"]
        TREND["Trend Chart"]
        MAP["Geographic Map"]
        TABLE["Detail Table"]
    end

    Params --> Model --> Visuals

Agency-Specific Measures¶

Row-Level Security for Federal¶

// RLS rule: Agency analysts see only their agency's data
// Table: AgencyData
// DAX filter expression:
[agency_code] = USERPRINCIPALNAME()
// Mapped via Entra ID security groups:
//   sg-usda-analysts → agency_code = "USDA"
//   sg-sba-analysts  → agency_code = "SBA"
//   sg-cross-agency  → no filter (sees all)

⚠️ Limitations¶

📚 References¶

🔗 Related Documents¶

• Direct Lake -- Direct Lake mode deep dive
• Capacity Planning & Cost Optimization -- CU consumption for BI workloads
• Semantic Link -- Programmatic access to semantic models
• Monitoring & Observability -- Track Power BI query performance
• Medallion Architecture Deep Dive -- Gold layer design for BI

Back to Best Practices Index | Back to Documentation

📝 Document Metadata - Author: Documentation Team - Reviewers: Data Engineering, BI, Analytics, Compliance - Classification: Internal - Next Review: 2026-07-21