Power BI — DAX, Data Modeling & Production Reporting

Power BI is Microsoft's analytics platform — from self-service reports in Power BI Desktop to enterprise semantic layers shared across thousands of users on Power BI Service. Its differentiator is the semantic model: a reusable, centrally-governed layer of metrics and relationships that sits between raw data and every report built on top of it.

Platform Overview

┌──────────────────────────────────────────────────────────────┐
│                      Power BI Platform                        │
│                                                              │
│  Data Sources         Semantic Model         Consumption      │
│  ────────────         ─────────────         ──────────────   │
│  SQL databases  →     Tables                Reports          │
│  Azure Synapse  →     Relationships         Dashboards       │
│  Snowflake      →     Measures (DAX)        Mobile           │
│  REST APIs      →     Hierarchies           Embedded         │
│  Excel/CSV      →     Row-Level Security    Paginated        │
│                        ↕                                     │
│             Power BI Service (Cloud)                          │
│  Workspaces, sharing, scheduled refresh, capacity            │
└──────────────────────────────────────────────────────────────┘

Data Modeling: Star Schema

The star schema is the right model for Power BI. One fact table joined to multiple dimension tables — simple, fast, and DAX-friendly.

Fact_Sales ──────── Dim_Date
     │               (DateKey, Year, Month, Quarter...)
     │
     ├──────────── Dim_Customer
     │               (CustomerKey, Name, Region, Segment...)
     │
     ├──────────── Dim_Product
     │               (ProductKey, Name, Category, Brand...)
     │
     └──────────── Dim_Store
                     (StoreKey, City, Country, Channel...)

Key modeling rules:

Always use a dedicated Dim_Date table — never derive date parts from fact table dates.
One-to-many relationships from dimensions to facts only — no many-to-many unless required.
Integer surrogate keys perform better than string keys for large fact tables.
Mark your date table with Mark as Date Table in Power BI for time intelligence functions to work correctly.

Creating a Date Table in DAX

DAX

Dim_Date =
VAR StartDate = DATE(2020, 1, 1)
VAR EndDate   = DATE(2030, 12, 31)
RETURN
ADDCOLUMNS(
    CALENDAR(StartDate, EndDate),
    "Year",         YEAR([Date]),
    "Quarter",      "Q" & QUARTER([Date]),
    "Quarter Num",  QUARTER([Date]),
    "Month Num",    MONTH([Date]),
    "Month Name",   FORMAT([Date], "MMMM"),
    "Month Short",  FORMAT([Date], "MMM"),
    "Week Num",     WEEKNUM([Date], 2),
    "Day of Week",  WEEKDAY([Date], 2),
    "Day Name",     FORMAT([Date], "dddd"),
    "Is Weekday",   IF(WEEKDAY([Date], 2) <= 5, TRUE, FALSE),
    "Year-Month",   FORMAT([Date], "YYYY-MM"),
    "FY Year",      IF(MONTH([Date]) >= 7,
                       "FY" & YEAR([Date]) + 1,
                       "FY" & YEAR([Date]))
)

DAX — Data Analysis Expressions

DAX is a formula language for creating measures (dynamic calculations) and calculated columns (static row-level values).

The Evaluation Context Principle

Understanding context is essential. Every DAX expression evaluates in two contexts:

Filter context: the filters applied by slicers, row/column headers, and visual-level filters
Row context: exists during calculated column evaluation (row by row) and CALCULATE iterator functions

DAX

-- MEASURE: recalculates based on the current filter context
Total Sales = SUM(Fact_Sales[Revenue])

-- CALCULATED COLUMN: computed once at data refresh, static
Profit Margin % =
    DIVIDE(Fact_Sales[Revenue] - Fact_Sales[Cost], Fact_Sales[Revenue])

Core Aggregation Measures

DAX

-- Basic aggregations
Total Revenue    = SUM(Fact_Sales[Revenue])
Total Units      = SUM(Fact_Sales[Quantity])
Avg Order Value  = AVERAGE(Fact_Sales[Revenue])
Transaction Count = COUNTROWS(Fact_Sales)
Distinct Customers = DISTINCTCOUNT(Fact_Sales[CustomerKey])

-- Conditional sum
Revenue Completed =
    CALCULATE(
        SUM(Fact_Sales[Revenue]),
        Fact_Sales[Status] = "Completed"
    )

-- Using FILTER for complex conditions
High Value Revenue =
    CALCULATE(
        SUM(Fact_Sales[Revenue]),
        FILTER(Fact_Sales, Fact_Sales[Revenue] > 10000)
    )

CALCULATE — The Most Important Function

CALCULATE(expression, filter1, filter2, ...) modifies the filter context before evaluating the expression:

DAX

-- Override a filter (ALL removes existing filters on a column)
Revenue All Regions =
    CALCULATE([Total Revenue], ALL(Dim_Customer[Region]))

-- % of total with ALLSELECTED (respects other slicer selections)
Revenue % of Total =
    DIVIDE(
        [Total Revenue],
        CALCULATE([Total Revenue], ALLSELECTED(Dim_Customer[Region]))
    )

-- Add filters
Revenue Electronics =
    CALCULATE([Total Revenue], Dim_Product[Category] = "Electronics")

-- KEEPFILTERS: adds a filter without overriding existing ones
Revenue North Electronics =
    CALCULATE(
        [Total Revenue],
        KEEPFILTERS(Dim_Customer[Region] = "North"),
        Dim_Product[Category] = "Electronics"
    )

Time Intelligence

Time intelligence functions require a properly marked date table with contiguous dates:

DAX

-- Year-to-date (resets at the start of each year)
Revenue YTD =
    TOTALYTD([Total Revenue], Dim_Date[Date])

-- Quarter-to-date
Revenue QTD =
    TOTALQTD([Total Revenue], Dim_Date[Date])

-- Same period last year
Revenue SPLY =
    CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dim_Date[Date]))

-- Year-over-year growth
Revenue YoY Growth % =
    DIVIDE([Total Revenue] - [Revenue SPLY], [Revenue SPLY])

-- Previous month
Revenue Prev Month =
    CALCULATE([Total Revenue], PREVIOUSMONTH(Dim_Date[Date]))

-- Rolling 3-month average
Revenue Rolling 3M =
    CALCULATE(
        AVERAGEX(
            DATESINPERIOD(Dim_Date[Date], LASTDATE(Dim_Date[Date]), -3, MONTH),
            [Total Revenue]
        )
    )

-- Custom fiscal year YTD (FY starts July 1)
Revenue FY YTD =
    TOTALYTD([Total Revenue], Dim_Date[Date], "06-30")

Advanced DAX Patterns

Ranking and Top N

DAX

-- Dynamic top N with RANKX
Product Sales Rank =
    RANKX(
        ALLSELECTED(Dim_Product[Product Name]),
        [Total Revenue],
        ,
        DESC,
        DENSE
    )

-- Show only top 5, blank for others
Top 5 Revenue =
    IF(
        [Product Sales Rank] <= 5,
        [Total Revenue]
    )

-- TOPN measure: sum revenue of top 10 customers
Top 10 Customer Revenue =
    CALCULATE(
        [Total Revenue],
        TOPN(10, ALL(Dim_Customer[CustomerKey]), [Total Revenue], DESC)
    )

Cumulative and Running Totals

DAX

-- Cumulative revenue over time (respects date slicer context)
Cumulative Revenue =
    CALCULATE(
        [Total Revenue],
        FILTER(
            ALLSELECTED(Dim_Date[Date]),
            Dim_Date[Date] <= MAX(Dim_Date[Date])
        )
    )

-- Customer lifetime value (cumulative per customer)
Customer LTV =
    CALCULATE(
        SUM(Fact_Sales[Revenue]),
        FILTER(
            ALL(Dim_Date[Date]),
            Dim_Date[Date] <= MAX(Dim_Date[Date])
        ),
        VALUES(Fact_Sales[CustomerKey])
    )

SWITCH Pattern — Conditional Measures

DAX

-- Dynamic metric selector (driven by a disconnected slicer table)
Selected Metric =
    SWITCH(
        SELECTEDVALUE(Metric_Selector[Metric]),
        "Revenue",    [Total Revenue],
        "Units",      [Total Units],
        "Customers",  [Distinct Customers],
        "Avg Order",  [Avg Order Value],
        [Total Revenue]   -- default
    )

DirectQuery vs Import Mode

| | Import | DirectQuery | Composite | |--|--------|-------------|-----------| | Data location | In-memory (VertiPaq) | Live query to source | Mix of both | | Refresh | Scheduled (up to 8x/day Pro, 48x/day Premium) | Real-time | Configurable | | Performance | Fastest | Depends on source | Balanced | | Data size | Up to 1 GB (Pro) / 400 GB (Premium) | Unlimited | Unlimited | | DAX support | Full | Limited (no calculated tables, some functions restricted) | Partial | | Use when | Dashboard performance critical | Data too large or real-time required | Mixed requirements |

DirectQuery performance tips:

Pre-aggregate in views/materialized tables at the source
Avoid complex DAX in DirectQuery mode — it translates to SQL that may not be index-friendly
Use query reduction options in Power BI settings to reduce queries per interaction

Row-Level Security (RLS)

RLS restricts which rows a user sees based on their identity:

DAX

-- Static RLS: role-based filter in Power BI Desktop
-- In Manage Roles → Create role "Sales_Region_North"
-- Table: Dim_Customer, Filter expression:
[Region] = "North"

-- Dynamic RLS: uses USERNAME() or USERPRINCIPALNAME()
-- Table: Dim_Customer, Filter expression:
[Sales_Rep_Email] = USERPRINCIPALNAME()

-- Multi-tenant pattern: user sees only their tenant's data
-- Table: Dim_Tenant, Filter expression:
[Tenant_ID] = LOOKUPVALUE(
    User_Tenant_Map[Tenant_ID],
    User_Tenant_Map[Email], USERPRINCIPALNAME()
)

In Power BI Service, assign users/groups to the roles. For embedded scenarios, pass the effective identity in the embed token:

// .NET: generate embed token with RLS identity
var tokenRequest = new GenerateTokenRequest(
    accessLevel: TokenAccessLevel.View,
    identities: new List<EffectiveIdentity>
    {
        new EffectiveIdentity(
            username: userEmail,            // maps to USERPRINCIPALNAME()
            datasets: new[] { datasetId },
            roles: new[] { "DynamicRLS" }
        )
    }
);

Power BI Embedded

Embed Power BI reports in your own application — authenticated via Azure AD service principal:

// Install: dotnet add package Microsoft.PowerBI.Api

using Microsoft.PowerBI.Api;
using Microsoft.PowerBI.Api.Models;
using Microsoft.Rest;
using Azure.Identity;

public class PowerBiEmbedService
{
    private readonly PowerBIClient _client;
    private readonly Guid _workspaceId;

    public PowerBiEmbedService(IConfiguration config)
    {
        var credential = new ClientSecretCredential(
            config["AzureAd:TenantId"],
            config["AzureAd:ClientId"],
            config["AzureAd:ClientSecret"]
        );
        var token = credential.GetToken(
            new TokenRequestContext(new[] { "https://analysis.windows.net/powerbi/api/.default" })
        );
        _client = new PowerBIClient(new TokenCredentials(token.Token));
        _workspaceId = Guid.Parse(config["PowerBI:WorkspaceId"]);
    }

    public async Task<EmbedConfig> GetEmbedConfigAsync(
        Guid reportId, string userEmail, string[] roles)
    {
        var report = await _client.Reports.GetReportInGroupAsync(_workspaceId, reportId);

        var tokenRequest = new GenerateTokenRequest(
            accessLevel: TokenAccessLevel.View,
            identities: new List<EffectiveIdentity>
            {
                new EffectiveIdentity(
                    username: userEmail,
                    datasets: new[] { report.DatasetId },
                    roles: roles
                )
            }
        );

        var embedToken = await _client.Reports.GenerateTokenInGroupAsync(
            _workspaceId, reportId, tokenRequest);

        return new EmbedConfig
        {
            EmbedUrl   = report.EmbedUrl,
            EmbedToken = embedToken.Token,
            ReportId   = reportId.ToString()
        };
    }
}

JAVASCRIPT

// Frontend: embed using Power BI JavaScript SDK
// npm install powerbi-client

import * as pbi from 'powerbi-client';

const config = await fetch('/api/powerbi/embed-config').then(r => r.json());

const powerbi = new pbi.service.Service(
    pbi.factories.hpmFactory,
    pbi.factories.wpmpFactory,
    pbi.factories.routerFactory
);

const container = document.getElementById('report-container');
const report = powerbi.embed(container, {
    type: 'report',
    id: config.reportId,
    embedUrl: config.embedUrl,
    accessToken: config.embedToken,
    tokenType: pbi.models.TokenType.Embed,
    settings: {
        panes: { filters: { visible: false }, pageNavigation: { visible: true } },
        background: pbi.models.BackgroundType.Transparent,
    },
});

report.on('loaded', () => console.log('Report loaded'));
report.on('error', event => console.error('Embed error', event.detail));

Deployment Pipelines

Power BI Deployment Pipelines (Premium/Fabric) promote content through Development → Test → Production:

Dev Workspace       Test Workspace       Prod Workspace
─────────────       ──────────────       ──────────────
Report v2           Report v2            Report v1 (live)
Dataset v2  ──→     Dataset v2  ──→      Dataset v1
Dashboard   Deploy  Dashboard   Deploy   Dashboard

Rules per stage:
  - Dev:  datasource = dev_db, refresh = manual
  - Test: datasource = test_db, refresh = nightly
  - Prod: datasource = prod_db, refresh = hourly

Use deployment rules to parameterize data sources per environment — the same semantic model connects to different databases at each stage without modifying the PBIX file.

REST API Integration

Automate Power BI operations with the REST API:

Python

import requests
from azure.identity import ClientSecretCredential

credential = ClientSecretCredential(
    tenant_id=TENANT_ID,
    client_id=CLIENT_ID,
    client_secret=CLIENT_SECRET
)
token = credential.get_token("https://analysis.windows.net/powerbi/api/.default")
headers = {"Authorization": f"Bearer {token.token}"}

BASE = "https://api.powerbi.com/v1.0/myorg"

# Trigger dataset refresh
def trigger_refresh(workspace_id: str, dataset_id: str) -> str:
    r = requests.post(
        f"{BASE}/groups/{workspace_id}/datasets/{dataset_id}/refreshes",
        headers=headers,
        json={"notifyOption": "MailOnFailure"}
    )
    r.raise_for_status()
    return r.headers.get("Location", "")   # URL to poll for status

# Get refresh history
def get_refresh_history(workspace_id: str, dataset_id: str) -> list:
    r = requests.get(
        f"{BASE}/groups/{workspace_id}/datasets/{dataset_id}/refreshes",
        headers=headers
    )
    return r.json()["value"]

# Export report to PDF
def export_report(workspace_id: str, report_id: str, output_path: str):
    r = requests.post(
        f"{BASE}/groups/{workspace_id}/reports/{report_id}/ExportTo",
        headers=headers,
        json={"format": "PDF"}
    )
    export_id = r.json()["id"]

    # Poll for completion
    import time
    while True:
        status = requests.get(
            f"{BASE}/groups/{workspace_id}/reports/{report_id}/exports/{export_id}",
            headers=headers
        ).json()
        if status["status"] == "Succeeded":
            break
        time.sleep(5)

    # Download the file
    file_content = requests.get(
        f"{BASE}/groups/{workspace_id}/reports/{report_id}/exports/{export_id}/file",
        headers=headers
    ).content

    with open(output_path, "wb") as f:
        f.write(file_content)

Microsoft Fabric: Power BI's Future

Microsoft Fabric unifies Power BI, Azure Synapse, Data Factory, and more into a single SaaS platform built on OneLake (a single data lake for the entire organisation):

Microsoft Fabric
├── OneLake — unified storage (one per tenant, like OneDrive for data)
├── Data Factory — ETL/ELT pipelines
├── Synapse Data Engineering — Spark notebooks
├── Synapse Data Warehouse — SQL analytics
├── Real-Time Intelligence — event streams + KQL
├── Data Science — notebooks + ML
└── Power BI — semantic models, reports, dashboards

What this means for Power BI: Semantic models in Fabric can reference tables directly in OneLake (using Direct Lake mode) — no import copy, no DirectQuery latency. For most workloads this is now the recommended approach.

Related: Azure Cloud Integration — Azure data services
Related: Snowflake Guide — SQL analytics and data warehousing
Related: Databricks Guide — Delta Lake and PySpark