DAX Best Practices

// ❌ Bad naming — ambiguous, inconsistent, hard to maintain
rev = SUM(Orders[revenue])
calc_1 = [rev] - [prev_rev]
x = DIVIDE([calc_1], [prev_rev])
Col1 = Orders[Revenue] * 1.1

// ✓ Good naming — self-documenting, consistent
Total Revenue =
    SUM(Orders[Revenue])

Revenue YoY Change =
    [Total Revenue] - [Revenue Prior Year]

Revenue YoY % =
    DIVIDE([Revenue YoY Change], [Revenue Prior Year])

Adjusted Revenue =           -- calculated column
    Orders[Revenue] * 1.1

// Set display folders in Power BI Desktop:
// 1. Click a measure in the Fields pane
// 2. In the Measure tools ribbon → Display folder
// 3. Type a folder name — nested folders use backslash: "Revenue\Time"

// Recommended folder structure for a sales model:
//
// 📁 KPIs
//     Total Revenue
//     Gross Margin %
//     Order Count
//     Avg Order Value
//
// 📁 Revenue
//     📁 Revenue\YoY
//         Revenue Prior Year
//         Revenue YoY Change
//         Revenue YoY %
//     📁 Revenue\YTD
//         Revenue YTD
//         Revenue PY YTD
//         Revenue YTD vs PY YTD %
//
// 📁 Orders
//     Orders Late Count
//     Orders Late %
//     Avg Days to Deliver
//
// 📁 _Helpers        ← underscore sorts helpers to top or bottom
//     _Base Revenue
//     _Prior Period Rev
//     _Rank Helper

// ❌ Hard to read — everything nested, no intermediate names
Revenue YoY % =
    DIVIDE(
        SUM(Orders[Revenue]) -
            CALCULATE(SUM(Orders[Revenue]),
                SAMEPERIODLASTYEAR(Calendar[Date])),
        CALCULATE(SUM(Orders[Revenue]),
            SAMEPERIODLASTYEAR(Calendar[Date]))
    )
// SAMEPERIODLASTYEAR evaluated twice — performance cost
// Cannot debug intermediate values without rewriting

// ✓ Maintainable — VAR names each step, RETURN is clear
Revenue YoY % =
VAR CurrentRevenue  = SUM(Orders[Revenue])
VAR PriorRevenue    =
    CALCULATE(
        SUM(Orders[Revenue]),
        SAMEPERIODLASTYEAR(Calendar[Date])
    )
VAR GrowthAmount    = CurrentRevenue - PriorRevenue
RETURN
    DIVIDE(GrowthAmount, PriorRevenue)

// Debugging tip: temporarily change RETURN to any VAR to inspect it
-- RETURN PriorRevenue   ← shows prior year revenue in every cell
-- RETURN GrowthAmount   ← shows the raw change amount

// Recommended documentation pattern for complex measures

Revenue YTD vs PY YTD % =
-- PURPOSE: Shows year-to-date revenue growth vs the same YTD
--          point in the prior year. Used in the Executive Summary
--          page header card. Requires Calendar table relationship.
-- DEPENDENCIES: Total Revenue, Calendar[Date]
-- LAST MODIFIED: 2024-03 · added BLANK() guard for Jan prior year

VAR CurrentYTD =
    -- YTD from Jan 1 of the current year to the last date in context
    CALCULATE(
        SUM(Orders[Revenue]),
        DATESYTD(Calendar[Date])
    )

VAR PriorYTD =
    -- YTD from Jan 1 of the prior year to the same date one year ago
    CALCULATE(
        SUM(Orders[Revenue]),
        DATESYTD(SAMEPERIODLASTYEAR(Calendar[Date]))
    )

VAR GrowthAmount = CurrentYTD - PriorYTD

RETURN
    -- Guard: return BLANK if prior year has no data (e.g. new data set)
    IF(
        ISBLANK(PriorYTD),
        BLANK(),
        DIVIDE(GrowthAmount, PriorYTD)
    )

// Testing checklist — validate every new measure against all five:

// 1. Grand total (no slicer)
//    Expected: sum of all data
//    Fails if: ALLSELECTED used where ALL was intended

// 2. Single dimension slice (e.g. Region = North in slicer)
//    Expected: filtered total for that dimension
//    Fails if: CALCULATE replaces filter instead of intersecting

// 3. Table visual with dimension on rows
//    Expected: each row = correct value for that dimension
//    Fails if: row context not converting to filter context correctly

// 4. Total row in the table visual
//    Expected: grand total, not sum of visible row values
//    Common failure: RANKX returning 1 for the total row;
//    % of total returning more than 100%

// 5. BLANK handling — filter to a dimension value with no data
//    Expected: BLANK() or a designated fallback, not 0 or error
//    Fails if: DIVIDE denominator is 0, or SAMEPERIODLASTYEAR
//    has no prior year data

// Debugging workflow:
// Step 1: Add the measure to a table visual with all dimensions
// Step 2: Check grand total row first
// Step 3: Check individual dimension rows
// Step 4: Apply a slicer and repeat steps 2-3
// Step 5: If wrong, change RETURN to each VAR to find the failure point