Advanced DAX Patterns for Enterprise Power BI Dashboards

The DAX measures, time intelligence patterns, and data modelling techniques I used to build production Power BI dashboards for my CN5026 coursework — applicable to any enterprise reporting scenario.

Why DAX Still Matters

Despite the rise of Python and SQL for analytics, Power BI with DAX remains the dominant tool in enterprise BI. Understanding DAX deeply separates analysts who build slow, fragile reports from those who build fast, maintainable dashboards.

The Star Schema Foundation

Every good Power BI model starts with a proper star schema:

• Fact tables — transactional data (Sales, Orders, Events)
• Dimension tables — descriptive data (Date, Customer, Product)
• Never use flat tables — they kill performance

FactSales ──────── DimDate
    |
    ├──────────── DimCustomer  
    |
    └──────────── DimProduct

Time Intelligence Patterns

The most common requirement in any dashboard is period-over-period comparison:

-- Year-to-date sales
Sales YTD = 
CALCULATE(
    [Total Sales],
    DATESYTD(DimDate[Date])
)

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

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

Dynamic TOP N with RANKX

Top N Products = 
VAR TopN = SELECTEDVALUE(TopNSelector[N], 10)
RETURN
CALCULATE(
    [Total Sales],
    FILTER(
        ALL(DimProduct[ProductName]),
        RANKX(
            ALL(DimProduct[ProductName]),
            [Total Sales],
            ,
            DESC,
            Dense
        ) <= TopN
    )
)

Conditional Formatting via DAX

Instead of static rules, drive formatting from measures:

KPI Color = 
SWITCH(
    TRUE(),
    [Target Achievement %] >= 1.0, "#22c55e",  -- Green
    [Target Achievement %] >= 0.8, "#f59e0b",  -- Amber  
    "#ef4444"                                   -- Red
)

Apply this measure in Format → Conditional Formatting → Field value.

Performance Tips

1. Avoid calculated columns — use measures instead
2. Filter before calculating — CALCULATE with filters beats FILTER(ALL())
3. Use DIVIDE not / — handles divide-by-zero gracefully
4. Avoid bi-directional relationships — they cause ambiguity and slow queries
5. Import mode beats DirectQuery — for datasets under 1GB, always import

These patterns formed the backbone of my CN5026 multi-page enterprise dashboard, which achieved sub-second query times on 500k+ row datasets.

Related Posts

Building a Fleet Management System with Oracle SQL & PL/SQL

10 min read →