DEV Community

Thesius Code
Thesius Code

Posted on • Originally published at datanest-stores.pages.dev

Power BI DAX Pattern Library

#datascience #sql #python #analytics

Power BI DAX Pattern Library

100+ production DAX patterns organized by category: time intelligence, dynamic filtering, ranking, budget vs. actuals, statistical calculations, and advanced table manipulation. Each pattern includes the formula, explanation, sample data model context, and common variations.

Key Features

  • 100+ DAX Patterns — organized into 12 categories for fast lookup
  • Time Intelligence — YTD, QTD, MTD, prior period, rolling averages, same-period-last-year
  • Dynamic Filtering — slicers that control measure behavior, top N, parameter tables
  • Ranking & TopN — dense rank, dynamic top N with "Other" grouping
  • Budget vs. Actuals — variance analysis, forecast blending, plan comparisons
  • Statistical Measures — percentiles, standard deviation, moving averages, correlation
  • Row-Level Security — dynamic RLS patterns for multi-tenant reports
  • Calculation Groups — reusable time intelligence with calculation items

Quick Start

  1. Open your Power BI Desktop file
  2. Browse patterns by category in src/patterns/
  3. Copy the DAX formula into a new measure
  4. Update table and column references to match your model

Basic: Year-to-Date Revenue 

Revenue YTD =
CALCULATE(
    [Total Revenue],
    DATESYTD('Calendar'[Date])
)
Enter fullscreen mode Exit fullscreen mode

Usage Examples

Time Intelligence: Same Period Last Year with Growth % 

Revenue SPLY =
CALCULATE(
    [Total Revenue],
    SAMEPERIODLASTYEAR('Calendar'[Date])
)

Revenue YoY Growth % =
VAR _Current = [Total Revenue]
VAR _Prior = [Revenue SPLY]
RETURN
    DIVIDE(_Current - _Prior, _Prior, 0)

// Rolling 3-Month Average
Revenue 3M Avg =
AVERAGEX(
    DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -3, MONTH),
    [Total Revenue Monthly]
)
Enter fullscreen mode Exit fullscreen mode

Dynamic TopN with "Other" Bucket 

// Parameter table: create a disconnected table with values 5, 10, 15, 20
// Name it 'TopN Parameter' with column [TopN Value]

Revenue TopN Display =
VAR _TopN = SELECTEDVALUE('TopN Parameter'[TopN Value], 10)
VAR _CurrentProduct = SELECTEDVALUE(Products[Product Name])
VAR _Rank =
    RANKX(
        ALLSELECTED(Products[Product Name]),
        [Total Revenue], , DESC, DENSE
    )
RETURN
    IF(
        _Rank <= _TopN,
        [Total Revenue],
        -- Aggregate everything ranked below TopN into "Other"
        IF(
            _CurrentProduct = "Other",
            CALCULATE(
                [Total Revenue],
                FILTER(
                    ALLSELECTED(Products[Product Name]),
                    RANKX(ALLSELECTED(Products[Product Name]),
                          [Total Revenue], , DESC, DENSE) > _TopN
                )
            )
        )
    )
Enter fullscreen mode Exit fullscreen mode

Budget vs. Actuals Variance 

Actual Revenue =
CALCULATE(
    SUM(FactSales[Amount]),
    FactSales[Type] = "Actual"
)

Budget Revenue =
CALCULATE(
    SUM(FactBudget[Amount]),
    FactBudget[Type] = "Budget"
)

Variance $ = [Actual Revenue] - [Budget Revenue]

Variance % =
DIVIDE([Variance $], [Budget Revenue], 0)

// Conditional formatting helper
Variance Status =
SWITCH(
    TRUE(),
    [Variance %] >= 0.05, "Above Plan",
    [Variance %] >= -0.05, "On Track",
    "Below Plan"
)
Enter fullscreen mode Exit fullscreen mode

Row-Level Security (Dynamic RLS) 

// In the RLS role definition:
[Region] = LOOKUPVALUE(
    SecurityTable[Region],
    SecurityTable[UserEmail],
    USERPRINCIPALNAME()
)
Enter fullscreen mode Exit fullscreen mode

Pattern Index

Category Count Key Patterns
Time Intelligence 18 YTD, QTD, MTD, SPLY, rolling average, custom fiscal
Dynamic Filtering 12 TopN, parameter tables, dynamic measures
Ranking 8 Dense rank, percentile rank, running total
Budget vs. Actuals 10 Variance, forecast blending, plan comparison
Statistical 12 Percentile, std deviation, correlation, moving avg
Text & Formatting 8 Dynamic titles, conditional format, KPI arrows
Row-Level Security 6 Static RLS, dynamic RLS, multi-role
Calculation Groups 8 Time intel group, currency conversion, unit switching
Parent-Child 6 Hierarchy flattening, path functions
Many-to-Many 5 Bridge tables, bi-directional filters
Semi-Additive 4 Balance snapshots, inventory, headcount
Error Handling 3 IFERROR, DIVIDE safe, blank handling

Best Practices

  1. Use variables (VAR/RETURN) — improves readability and performance by avoiding repeated calculations
  2. Never use CALCULATE with conflicting filters — understand filter context before adding CALCULATE
  3. Prefer DIVIDE() over / — handles division by zero gracefully
  4. Star schema first — DAX performs best on properly modeled star schemas
  5. Use calculation groups — avoid duplicating time intelligence across 20+ measures
  6. Test with DAX Studio — profile query performance before publishing

Troubleshooting

Issue Cause Fix
Measure returns BLANK Filter context eliminates all rows Use IF(ISBLANK(...), 0, ...) or check filter state
SAMEPERIODLASTYEAR returns wrong values Calendar table has gaps Ensure contiguous date table with CALENDAR()
Performance degradation Complex iterator (SUMX over large table) Pre-aggregate in Power Query or use summarized table
RLS not filtering correctly UserPrincipalName mismatch Verify email format matches security table exactly

This is 1 of 11 resources in the Data Analyst Toolkit toolkit. Get the complete [Power BI DAX Pattern Library] with all files, templates, and documentation for $29.

Get the Full Kit →

Or grab the entire Data Analyst Toolkit bundle (11 products) for $129 — save 30%.

Get the Complete Bundle →

Related Articles

Top comments (1)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.