Powerbi Core

Power BI Core Concepts and Data Modeling

Overview

Core Power BI knowledge covering data modeling best practices, connectivity modes, source types, relationships, and common pitfalls. This skill provides the foundational architecture guidance every Power BI developer needs.

Data Model Design - Star Schema

Always design data models using star schema topology:

Mandatory rules:

• One fact table at the center, dimensions radiating out
• Relationships flow from dimension (one side) to fact (many side)
• Use surrogate integer keys, not natural/business keys
• Keep fact tables narrow (keys + measures only)
• Denormalize dimensions (flatten snowflake into star)
• Create a dedicated Date dimension table (disable auto date/time)
• Never use bidirectional cross-filtering unless absolutely required and contained

Storage Modes

Import mode considerations:

• 1GB PBIX file size limit (10GB for Premium/PPU in service)
• Data is a snapshot at refresh time; not real-time
• Scheduled refresh limit: 8/day (Pro), 48/day (Premium/PPU)

DirectQuery limitations:

• No Power Query transformations applied at query time
• Single source per model (unless composite)
• Performance depends entirely on source query speed
• Many DAX functions unavailable or degraded
• No calculated columns on DirectQuery tables
• Row limit of 1 million rows per visual query

Direct Lake key considerations (2025-2026 GA):

• Two variants: Direct Lake on OneLake (DL/OL) and Direct Lake on SQL endpoints (DL/SQL)
• DL/OL does NOT fall back to DirectQuery -- queries fail if data cannot be served
• DL/SQL CAN fall back to DirectQuery via SQL analytics endpoint
• Guardrails vary by capacity: F32 allows up to 1,000 files/row groups per table; F64/P1 allows up to 5,000
• Max Memory is a soft limit for paging, not a hard guardrail -- excess paging hurts performance
• Max model size on disk/OneLake IS a hard guardrail -- exceeding causes DQ fallback (DL/SQL) or failure (DL/OL)
• Full DAX support including calculated columns
• Framing (metadata-only refresh) completes in seconds
• Power BI Embedded with Direct Lake mode is GA since March 2025

Choosing storage mode decision tree: 1. Data in Fabric OneLake delta tables? Use Direct Lake 2. Need real-time data, source is fast? Use DirectQuery 3. Data under 1GB, can tolerate refresh lag? Use Import (best performance) 4. Large data + need fast queries? Use composite model (Import dimensions + DQ facts + aggregation tables)

Relationships

Relationship rules:

• Only one active relationship between any two tables
• Use USERELATIONSHIP() in DAX to activate inactive relationships
• Avoid bidirectional filtering -- it causes ambiguous filter paths, performance degradation, and unexpected results
• Many-to-many requires a bridge table or composite model many-to-many cardinality
• Referential integrity: set "Assume Referential Integrity" for DirectQuery performance

Data Sources Quick Reference

Incremental Refresh

Configure incremental refresh for large Import tables to avoid full refresh:

1. Create RangeStart and RangeEnd parameters (type DateTime) in Power Query 2. Apply filter on the date column using these parameters 3. Configure refresh policy: archive period (e.g., 3 years), incremental period (e.g., 30 days) 4. Optionally enable "detect data changes" with a last-modified column 5. Optionally enable real-time data with DirectQuery for the latest partition

Requirements: Premium, PPU, or Fabric capacity for more than basic incremental refresh. Pro workspaces support incremental refresh but with limitations.

2025-2026 improvements:

• Semantic models with incremental refresh can now be edited directly in Power BI Service (change calculated columns, rename tables, adjust hierarchies) without reopening Desktop
• Enhanced refresh API supports selective partition refresh for finer control
• Improved performance for terabyte-scale datasets with faster partition processing

Gateway Configuration

On-premises data gateway bridges on-premises sources to Power BI Service:

VNet data gateway (2025-2026):

• Connects to Azure data sources within a VNet without on-premises hardware
• Managed by Fabric/Power BI Service, no gateway machine maintenance
• Supports Azure SQL, Synapse, Azure Data Explorer, and other VNet-bound services
• Enable in Fabric Admin portal under gateway management

Gateway releases (2025-2026):

• Monthly releases throughout 2025-2026 with enhanced caching and query folding
• Improved query performance through optimized connection pooling
• 64-bit only for Power BI Desktop for Report Server starting September 2025

Common gateway failures:

• Credentials expired -- update in gateway settings
• Source unreachable -- check firewall, VPN, DNS
• Memory exhaustion -- monitor gateway machine resources
• Mashup engine crash -- check Power Query complexity

Data Source Authentication

Workspace Identity (2025-2026):

• Tied to a Fabric workspace, similar to Azure Managed Identity
• No expiration, no secret or password to manage
• Configure in workspace settings, assign to semantic model data sources
• Preferred over service principal for Fabric-native scenarios

OAuth2 token limitation: When set via REST API (not UI), OAuth2 credentials lack a refresh token and expire after 1 hour. Use service principal for long-running automation.

Connection pooling best practices:

• Gateway reuses connections where possible -- minimize distinct credential sets
• Set query timeout in data source settings (default 5 min, increase for complex queries)
• Implement retry logic in Power Query for transient source failures using try/otherwise

Common Gotchas and Anti-Patterns

Additional Resources

Reference Files

• `references/data-sources-detail.md` -- Detailed connector configuration for all source types
• `references/gotchas-deep-dive.md` -- Extended pitfall analysis with examples and resolution patterns

Data Sources - Detailed Configuration

SQL Server Family

SQL Server (on-premises)

Server: servername\instancename
Database: DatabaseName
Authentication: Windows | SQL Server | Azure AD
Gateway: Required (standard or personal)
Connectivity: Import or DirectQuery

Connection string format:

Data Source=server\instance;Initial Catalog=dbname;Integrated Security=True

Query folding: Fully supported. Native SQL query passthrough available.

Azure SQL Database

Server: servername.database.windows.net
Database: DatabaseName
Authentication: SQL Server | Azure AD | Managed Identity (for dataflows)
Gateway: Not required (cloud-to-cloud)
Connectivity: Import or DirectQuery

Best practice: Use Azure AD authentication with conditional access policies. Enable "Assume Referential Integrity" for DirectQuery.

Azure Synapse Analytics (Dedicated SQL Pool)

Server: workspacename.sql.azuresynapse.net
Database: PoolName
Authentication: SQL | Azure AD
Connectivity: Import or DirectQuery (preferred for large datasets)

DirectQuery recommended for Synapse due to massive data volumes. Synapse handles query distribution across nodes.

Azure Synapse Analytics (Serverless SQL Pool)

Server: workspacename-ondemand.sql.azuresynapse.net
Database: DatabaseName (or master for ad-hoc)
Connectivity: DirectQuery strongly recommended

Warning: Import mode against serverless pool charges per TB scanned on every refresh. Use DirectQuery or build views for controlled access.

Azure Data Services

Azure Cosmos DB

Connector: Azure Cosmos DB v2 (recommended)
Connection: AccountEndpoint=https://account.documents.azure.com:443/;AccountKey=...
Container: collection name
Connectivity: Import only (no DirectQuery)

Limitations:

• Flattening nested JSON can be complex in Power Query
• Large datasets may timeout -- use incremental refresh or pre-aggregate
• RU consumption during refresh can be significant

Azure Data Explorer (Kusto)

Cluster: https://clustername.region.kusto.windows.net
Database: DatabaseName
Authentication: Azure AD
Connectivity: Import or DirectQuery

DirectQuery is preferred for Kusto -- it translates DAX to KQL efficiently. Native KQL passthrough supported.

Azure Blob Storage / Data Lake Storage Gen2

Account: https://accountname.blob.core.windows.net (Blob)
         https://accountname.dfs.core.windows.net (ADLS Gen2)
Authentication: Account Key | SAS Token | Azure AD
File formats: CSV, JSON, Parquet, Excel, XML
Connectivity: Import only

Best practice for ADLS Gen2: Use Parquet format for best performance. Organize files with partitioned folder structure (year/month/day).

Cloud Databases

Snowflake

Server: account.region.snowflakecomputing.com
Warehouse: WAREHOUSE_NAME
Database: DATABASE_NAME
Schema: SCHEMA_NAME
Authentication: Username/Password | Azure AD SSO
Connectivity: Import or DirectQuery

Query folding: Supported for most operations. Snowflake handles transformation pushdown efficiently.

DirectQuery: Supported but test performance. Snowflake auto-suspend can cause initial query delays.

Databricks (SQL Warehouse)

Server hostname: adb-workspace-id.azuredatabricks.net
HTTP Path: /sql/1.0/warehouses/warehouse-id
Authentication: Personal Access Token | Azure AD
Connectivity: Import or DirectQuery

Best practice: Use Databricks SQL Warehouse (not cluster) for Power BI connectivity. SQL Warehouse is optimized for BI queries.

Google BigQuery

Project: project-id
Authentication: Google Account | Service Account
Connectivity: Import or DirectQuery
Billing: Charged per query in DirectQuery mode

Warning: DirectQuery with BigQuery charges per TB scanned. Use Import or create materialized views.

Amazon Redshift

Server: cluster.region.redshift.amazonaws.com:5439
Database: database_name
Authentication: Username/Password
Connectivity: Import or DirectQuery

Files

Excel

Source: Local file, SharePoint, OneDrive
Supported: .xlsx, .xls, .xlsm
Load: Tables, Named Ranges, Sheets

Best practice: Always use Excel Tables (Ctrl+T) for clean data loading. Avoid named ranges that include headers inconsistently.

CSV / Delimited Text

Encoding: UTF-8 (default), UTF-16, ASCII, others
Delimiter: Comma, Tab, Semicolon, Pipe, custom
Header: First row as header (default)

Query folding: Not supported. All transformations happen in the mashup engine.

Parquet

Source: Local file, Azure Blob, ADLS Gen2, S3
Schema: Embedded in file metadata
Types: Preserved from Parquet schema

Best performance for file-based sources. Column pruning and predicate pushdown supported in some connectors.

JSON

Source: Local file, Web API, Azure Blob
Expansion: Record/List expansion in Power Query
Pagination: Manual implementation required for APIs

Web and API Sources

OData Feed

URL: https://service/odata/v4/EntitySet
Authentication: Anonymous | Basic | OAuth2 | Azure AD
Query folding: Supported (OData $filter, $select, $expand translated)

Query folding with OData: Many Power Query steps fold to OData query parameters. Check "View Native Query" to verify.

Web / REST API

URL: https://api.example.com/endpoint
Method: GET (default), POST via Web.Contents options
Authentication: Anonymous | Basic | API Key | OAuth2
Pagination: Implement manually using List.Generate or recursive functions

Pagination pattern (M code):

let
    GetPage = (url) =>
        let
            response = Json.Document(Web.Contents(url)),
            data = response[value],
            nextLink = try response[#"@odata.nextLink"] otherwise null,
            allData = if nextLink <> null
                then data & GetPage(nextLink)
                else data
        in allData,
    result = GetPage("https://api.example.com/data")
in
    result

SharePoint

Site URL: https://tenant.sharepoint.com/sites/SiteName
Authentication: Microsoft Account | Azure AD
Lists: SharePoint List connector
Files: SharePoint Folder connector

Best practice: Use SharePoint List connector for structured data. For files, use SharePoint Folder connector and filter early in Power Query.

Streaming and Real-Time

Streaming Datasets (Push)

Push data via REST API for real-time dashboards:

POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/rows
Content-Type: application/json
Authorization: Bearer {token}

{
  "rows": [
    { "Timestamp": "2026-01-15T10:30:00Z", "Value": 42.5 }
  ]
}

Types of streaming datasets:

Azure Stream Analytics

Output: Power BI (streaming dataset)
Configuration: Set in Stream Analytics job output
Real-time: Yes, sub-second latency
History: Configurable retention

Fabric Data Sources (2025-2026)

Fabric Lakehouse

Connection: SQL Analytics Endpoint or Direct Lake
Server: workspace-guid.datawarehouse.fabric.microsoft.com
Authentication: Azure AD / Workspace Identity
Connectivity: Direct Lake (preferred) or DirectQuery via SQL endpoint

Direct Lake: Zero-copy access to delta tables in OneLake. No gateway needed. Framing (metadata refresh) completes in seconds.

Fabric Warehouse

Connection: SQL Analytics Endpoint or Direct Lake
Server: workspace-guid.datawarehouse.fabric.microsoft.com
Authentication: Azure AD / Workspace Identity
Connectivity: Direct Lake (preferred) or DirectQuery
T-SQL: Full DML support (INSERT, UPDATE, DELETE, MERGE)

KQL Database (Real-Time Intelligence)

Cluster: https://clustername.kusto.fabric.microsoft.com
Database: DatabaseName
Authentication: Azure AD
Connectivity: DirectQuery via KQL connector

Use case: Real-time event data from Eventstream, IoT Hub, or Event Hubs ingested into KQL, then queried by Power BI.

Eventstream

Integration: Eventstream -> KQL Database -> Power BI DirectQuery
             Eventstream -> Lakehouse -> Power BI Direct Lake
Sources: Azure Event Hubs, Kafka, IoT Hub, custom REST

Real-Time Intelligence pipeline: Events flow through Eventstream to KQL Database or Lakehouse, then Power BI queries the destination.

Connector Updates (2025-2026)

Gateway Requirements Summary

Power BI Gotchas and Pitfalls - Deep Dive

1. Auto Date/Time Overhead

Problem: Power BI Desktop creates a hidden date table for every date/time column in the model. Each hidden table contains a full calendar hierarchy (Year, Quarter, Month, Day) consuming memory.

Impact: A model with 20 date columns gets 20 hidden date tables. Can add 100MB+ to model size.

Fix: 1. File > Options > Data Load > uncheck "Auto date/time for new files" 2. Current file > Options > Current File > Data Load > uncheck "Auto date/time" 3. Create ONE explicit Date dimension table:

Date =
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Month", FORMAT([Date], "MMMM"),
    "MonthNumber", MONTH([Date]),
    "YearMonth", FORMAT([Date], "YYYY-MM"),
    "WeekDay", FORMAT([Date], "dddd"),
    "WeekDayNumber", WEEKDAY([Date], 2),
    "IsWeekend", IF(WEEKDAY([Date], 2) > 5, TRUE, FALSE)
)

4. Mark it as a Date table: select table > Table tools > Mark as date table

2. Bidirectional Cross-Filtering

Problem: Setting cross-filter direction to "Both" causes filters to flow in both directions across a relationship. This creates ambiguous paths when multiple relationships exist.

Impact:

• Wrong aggregation results with no error message
• Significant performance degradation (filter propagation explodes)
• Unexpected row counts in visuals
• Security filters (RLS) may not propagate correctly

When it seems needed: Typically for "many-to-many" slicer scenarios.

Fix: Use DAX CROSSFILTER() or TREATAS() instead:

Sales by Selected Category =
CALCULATE(
    [Total Sales],
    TREATAS(VALUES(CategoryBridge[CategoryID]), Sales[CategoryID])
)

3. Implicit vs Explicit Measures

Problem: Dragging a numeric column directly to a visual creates an "implicit measure" with a default aggregation (usually SUM). These cannot be reused, formatted consistently, or used in complex calculations.

Impact:

• Inconsistent calculations across visuals
• No formula bar showing the logic
• Cannot reference in other DAX expressions
• Field list clutter
• Governance nightmare in large models

Fix: ALWAYS create explicit measures:

// BAD: Dragging Revenue column and setting to "Sum"
// GOOD: Explicit measure
Total Revenue = SUM(Sales[Revenue])

Best practice: Hide numeric columns from the report view after creating measures for them. Put measures in a dedicated "Measures" display folder or a disconnected measures table.

4. BLANK vs Zero vs Null

Problem: DAX has three distinct concepts that developers conflate:

Common mistakes:

// This returns BLANK when Sales is blank, not 0
Bad = IF(Sales[Amount] = 0, "No Sales", "Has Sales")
// BLANK <> 0, so BLANK rows show "Has Sales"!

// Correct
Good = IF(ISBLANK(Sales[Amount]) || Sales[Amount] = 0, "No Sales", "Has Sales")

DIVIDE behavior:

// DIVIDE returns BLANK (not error) on division by zero
Ratio = DIVIDE([Numerator], [Denominator])
// Returns BLANK when Denominator is 0 or BLANK

// To return 0 instead of BLANK:
Ratio = DIVIDE([Numerator], [Denominator], 0)

5. Circular Dependency Errors

Problem: DAX calculated columns or tables referencing each other (directly or indirectly) create a circular dependency. This also happens with bidirectional relationships combined with calculated columns.

Common causes:

• Calculated column A references calculated column B, which references A
• Bidirectional relationship + calculated column using RELATED()
• Row-level security filter referencing a calculated column that depends on the filtered table

Fix:

• Restructure calculations to avoid mutual references
• Replace calculated columns with measures where possible
• Remove bidirectional filtering
• Use EARLIER() for recursive row context scenarios

6. 1GB PBIX File Size Limit

Problem: Power BI Desktop cannot save files larger than 1GB. The Power BI Service supports up to 10GB for Premium/PPU but Desktop remains capped.

Warning signs: File save takes progressively longer, eventually fails.

Strategies to reduce size: 1. Remove unused columns (biggest impact) -- especially high-cardinality text columns 2. Reduce cardinality of text columns (group rare values into "Other") 3. Avoid calculated columns with high cardinality results 4. Disable auto date/time (see gotcha #1) 5. Use Import mode with column selection rather than SELECT * 6. Reduce decimal precision (round to 2 decimal places) 7. Move detail data to a separate model; keep summary in the report model 8. Use PBIP/PBIR format for source control (does not have the 1GB limit; large models deploy directly to service)

7. Gateway Refresh Failures

Common failure patterns:

Monitoring best practices:

• Enable gateway performance monitoring (PerformanceCounters in gateway config)
• Set up alerts on refresh failures in Power BI Service
• Schedule refreshes during off-peak hours
• Use gateway cluster (multiple machines) for high availability

8. DirectQuery Gotchas

9. Power BI Service vs Desktop Feature Gaps

10. Composite Model Pitfalls

Problem: Composite models (mixing Import + DirectQuery) introduce complexity:

• Relationships between Import and DQ tables have limited cross-filtering
• Many-to-many relationships between DQ tables can produce incorrect results
• Security context may not propagate across storage mode boundaries
• Performance varies wildly depending on which tables are queried together

Best practice: Use Import for dimensions, DirectQuery for large fact tables. Test thoroughly with production data volumes.

11. Row-Level Security (RLS) Gotchas

• RLS is NOT enforced in Power BI Desktop "Test as role" does not support dynamic RLS with USERPRINCIPALNAME() accurately
• RLS must be tested in the Power BI Service with actual user accounts
• RLS does not apply to workspace admins/members -- only viewers
• USERPRINCIPALNAME() returns the UPN, not the email (they differ in some orgs)
• Bidirectional cross-filter + RLS can fail silently -- always test combinations
• DirectQuery RLS adds WHERE clauses to every query, impacting performance

12. Large Semantic Model Anti-Patterns

13. Direct Lake Specific Gotchas (2025-2026)

14. Power BI Report Server vs. Desktop/Service Gaps

Key rule: If a feature requires cloud infrastructure (AI, streaming, apps, dashboards), it is NOT available in Report Server. Design reports for Report Server using the intersection of Desktop and Report Server capabilities.

15. Workspace Identity vs. Service Principal

16. PBIR Transition Gotchas (2026)