DAX Patterns for AggieDB

Reusable DAX patterns for common calculations across AggieDB semantic models. Copy and adapt these for your specific fact tables.

Audience

Report builders who've completed Level 1

Prerequisites

Understanding of measures vs calculated columns, basic CALCULATE

Foundational Patterns

Sum with Unit Conversion

Raw data often comes in bytes or milliseconds. Convert at the measure level, not the column level:

Total Storage GB = 
DIVIDE(
    SUM(EXO_Storage[Storage_Used_Bytes]),
    1073741824,  -- bytes to GB
    0
)

Count Distinct (Unique Users)

Active Users = 
DISTINCTCOUNT(Teams_Usage[User_Principal_Name])

Percentage of Total

% of Total Storage = 
DIVIDE(
    [Total Storage GB],
    CALCULATE([Total Storage GB], REMOVEFILTERS(Person[Department])),
    0
)

Time Intelligence (Fiscal Year)

TAMU's fiscal year starts September 1. All time intelligence functions need "8/31" as the year-end date.

Fiscal Year-to-Date

Storage FYTD = 
TOTALYTD(
    [Total Storage GB],
    Date[Date],
    "8/31"
)

Same Period Last Fiscal Year

Storage SPLY = 
CALCULATE(
    [Total Storage GB],
    SAMEPERIODLASTYEAR(Date[Date])
)

Year-over-Year % Change

Storage YoY % = 
VAR CurrentPeriod = [Total Storage GB]
VAR PriorYear = [Storage SPLY]
RETURN
    DIVIDE(CurrentPeriod - PriorYear, PriorYear, 0)

Month-over-Month Change

Storage MoM Change = 
VAR CurrentMonth = [Total Storage GB]
VAR PriorMonth = 
    CALCULATE(
        [Total Storage GB],
        DATEADD(Date[Date], -1, MONTH)
    )
RETURN
    CurrentMonth - PriorMonth

Rolling 90-Day Average

Avg Daily Storage 90d = 
AVERAGEX(
    DATESINPERIOD(Date[Date], MAX(Date[Date]), -90, DAY),
    [Total Storage GB]
)

SWITCH Pattern (Parameter Translation)

Use SWITCH with SELECTEDVALUE for dynamic measure selection. This replaces Tableau's parameter-driven calculations:

Selected Metric = 
SWITCH(
    SELECTEDVALUE(Metric_Selector[Metric]),
    "Storage", [Total Storage GB],
    "Users", [Active Users],
    "Growth", [Storage MoM Change],
    [Total Storage GB]  -- default
)

Pair this with a Metric_Selector table (disconnected table with a slicer):

Metric_Selector = 
DATATABLE(
    "Metric", STRING,
    {
        {"Storage"},
        {"Users"},
        {"Growth"}
    }
)

Use KEEPFILTERS instead of plain CALCULATE filters when you want to add a filter without overriding RLS. Plain CALCULATE(measure, table[column] = value) replaces the filter context — which can accidentally bypass Row-Level Security.

Row-Level Security Patterns

Basic RLS Role (Department-Based)

-- In the RLS role definition:
[Department] = LOOKUPVALUE(
    RLS_Security[Department],
    RLS_Security[User_Principal_Name],
    USERPRINCIPALNAME()
)

Multi-Department Access

-- Allows users to see multiple departments via a security table
CONTAINS(
    FILTER(
        RLS_Security,
        RLS_Security[User_Principal_Name] = USERPRINCIPALNAME()
    ),
    RLS_Security[Department],
    Person[Department]
)

Admin Override (See Everything)

-- Checks if user is admin; if yes, no filter applied
VAR IsAdmin = 
    CONTAINS(
        RLS_Security,
        RLS_Security[User_Principal_Name], USERPRINCIPALNAME(),
        RLS_Security[Role], "Admin"
    )
RETURN
    IF(IsAdmin, TRUE(), [Department] = LOOKUPVALUE(...))

Formatting Patterns

Conditional Formatting Measure

Storage Status Color = 
SWITCH(
    TRUE(),
    [Storage % Used] >= 0.9, "#d04343",   -- Red: >90%
    [Storage % Used] >= 0.7, "#E5B229",   -- Yellow: >70%
    "#389F5A"                              -- Green: <70%
)

Display Units

Storage Display = 
VAR Value = [Total Storage GB]
RETURN
    IF(
        Value >= 1024,
        FORMAT(Value / 1024, "#,0.0") & " TB",
        FORMAT(Value, "#,0.0") & " GB"
    )

Anti-Patterns to Avoid

Don't Do This	Do This Instead	Why
Calculated columns for metrics	Measures	Calculated columns increase model size; measures compute on demand
`CALCULATE` without `KEEPFILTERS` in RLS context	`KEEPFILTERS`	Plain filter overrides can bypass RLS
`FILTER(ALL(table))`	`REMOVEFILTERS(column)`	`FILTER(ALL(...))` is slower — it materializes the entire table
Bidirectional relationships	One-way with explicit CALCULATE	Bidirectional filters cause ambiguity and performance issues
Nested CALCULATE	Variables (VAR/RETURN)	Variables compute once; nested CALCULATE recomputes

Date Dimension

Fiscal year structure and time intelligence basics

RLS Walkthrough

Step-by-step Row-Level Security implementation

Department IT Support