Power-Bi-Dax-Best-Practices

// ✅ PREFERRED: Using variables for clarity and performance
Sales YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PreviousYearSales = 
    CALCULATE(
        [Total Sales],
        SAMEPERIODLASTYEAR('Date'[Date])
    )
RETURN
    DIVIDE(CurrentSales - PreviousYearSales, PreviousYearSales)

// ❌ AVOID: Repeated calculations without variables  
Sales YoY Growth % =
DIVIDE(
    [Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])),
    CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
)

// ✅ ALWAYS fully qualify column references
Customer Count = 
DISTINCTCOUNT(Sales[CustomerID])

Profit Margin = 
DIVIDE(
    SUM(Sales[Profit]),
    SUM(Sales[Revenue])
)

// ✅ NEVER fully qualify measure references
YTD Sales Growth = 
DIVIDE([YTD Sales] - [YTD Sales PY], [YTD Sales PY])

// ❌ AVOID: Unqualified column references
Customer Count = DISTINCTCOUNT([CustomerID])  // Ambiguous

// ❌ AVOID: Fully qualified measure references
Growth Rate = DIVIDE(Sales[Total Sales] - Sales[Total Sales PY], Sales[Total Sales PY])  // Breaks if measure moves

// ✅ PREFERRED: Use DIVIDE function for safe division
Profit Margin = 
DIVIDE([Total Profit], [Total Revenue])

// ✅ PREFERRED: Use defensive strategies in model design
Average Order Value = 
VAR TotalOrders = COUNTROWS(Orders)
VAR TotalRevenue = SUM(Orders[Amount])
RETURN
    IF(TotalOrders > 0, DIVIDE(TotalRevenue, TotalOrders))

// ❌ AVOID: ISERROR and IFERROR functions (performance impact)
Profit Margin = 
IFERROR([Total Profit] / [Total Revenue], BLANK())

// ❌ AVOID: Complex error handling that could be prevented
Unsafe Calculation = 
IF(
    OR(
        ISBLANK([Revenue]),
        [Revenue] = 0
    ),
    BLANK(),
    [Profit] / [Revenue]
)

// Use appropriate aggregation functions for performance
Customer Count = DISTINCTCOUNT(Sales[CustomerID])  // ✅ For unique counts
Order Count = COUNTROWS(Orders)                    // ✅ For row counts  
Average Deal Size = AVERAGE(Sales[DealValue])      // ✅ For averages

// Avoid COUNT when COUNTROWS is more appropriate
// ❌ COUNT(Sales[OrderID]) - slower for counting rows
// ✅ COUNTROWS(Sales) - faster and more explicit

// Efficient use of CALCULATE with multiple filters
High Value Customers = 
CALCULATE(
    DISTINCTCOUNT(Sales[CustomerID]),
    Sales[OrderValue] > 1000,
    Sales[OrderDate] >= DATE(2024,1,1)
)

// Proper context modification patterns
Same Period Last Year = 
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

// Using FILTER appropriately (avoid as filter argument)
// ✅ PREFERRED: Direct filter expression
High Value Orders = 
CALCULATE(
    [Total Sales],
    Sales[OrderValue] > 1000
)

// ❌ AVOID: FILTER as filter argument (unless table manipulation needed)
High Value Orders = 
CALCULATE(
    [Total Sales],
    FILTER(Sales, Sales[OrderValue] > 1000)
)

// Standard time intelligence measures
YTD Sales = 
CALCULATE(