Power BI DAX Formula Reference & Best Practices
A practical quick-reference guide for authoring fast, accurate DAX measures in Microsoft Power BI. Covers CALCULATE, Filter Context, Time Intelligence, and optimization.
Who This Guide Is For
BI developers, data analysts building executive dashboards, and students learning data modeling in Power BI.
Common Workplace Use Cases:
- Building dynamic Year-to-Date (YTD) and Prior Year (PY) comparison cards.
- Calculating dynamic profit margins and conversion ratios across interactive slicers.
- Activating secondary date relationships with USERELATIONSHIP.
- Eliminating memory bloat by replacing calculated columns with efficient measures.
Comprehensive Syntax & Formula Preview
Ungated Reference1. Dynamic Profit Margin Measure
Using DIVIDE to safely handle zero division and calculate executive margins.
Gross Margin % =
VAR TotalRev = SUM(FactSales[Revenue])
VAR TotalCost = SUM(FactSales[Cost])
RETURN
DIVIDE(TotalRev - TotalCost, TotalRev, 0)2. CALCULATE with Filter Context Overrides
Modifying filter context to compute benchmark totals across all categories.
Revenue Contribution % =
VAR CurrentCategoryRev = [Total Revenue]
VAR AllCategoriesRev = CALCULATE([Total Revenue], ALL(DimProduct[Category]))
RETURN
DIVIDE(CurrentCategoryRev, AllCategoriesRev, 0)3. Time Intelligence: Year-over-Year (YoY) Growth
Comparing current performance against the exact corresponding period in the prior calendar year.
YoY Revenue Growth % =
VAR CurrentRev = [Total Revenue]
VAR PriorYearRev = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DimDate[Date]))
RETURN
DIVIDE(CurrentRev - PriorYearRev, PriorYearRev, 0)Students, educators, bloggers, and tech writers may cite or reference this quick-reference guide in coursework, portfolio articles, or technical guides with proper attribution:
Key Topics Covered
- Evaluation Context: Row Context vs Filter Context
- Filter Modification: CALCULATE, FILTER, ALL, ALLEXCEPT
- Time Intelligence: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD
- Optimization: Using variables (VAR) to prevent redundant measure re-evaluation
Apply These Formulas to Real Business Data
Master the full data analytics toolchain in our Live Online or Gurugram Classroom program with hands-on projects and interview preparation.