🔗 https://techtown.in/power-bi/dax/
(Topic: DAX in Power BI – Measures, Calculated Columns, and Best Practices)
🧮 Mastering DAX in Power BI – The Brain Behind Your Data
DAX (Data Analysis Expressions) is the formula language of Power BI. It’s what turns raw data into actionable insights.
Whether you’re calculating revenue, year-over-year growth, or running complex filtering logic — DAX is the engine behind it all.
📌 What is DAX?
DAX is a powerful, Excel-like formula language used to create:
- Measures: For aggregations (like total sales, average profit)
- Calculated Columns: For row-level calculations (like full name = first + last)
- Calculated Tables: For creating new tables using DAX expressions
- Custom Filters: Using functions like
FILTER()andCALCULATE()
🔍 DAX vs Excel Formulas
| Feature | DAX | Excel |
|---|---|---|
| Context Aware | Yes (Row & Filter Context) | No |
| Works on Models | Yes (tables, relationships) | Works only on cells |
| Optimized for BI | Yes | General-purpose |
DAX isn’t just about math — it’s about context.
🛠️ Most Useful DAX Functions (With Examples)
🧮 Aggregation
Total Sales = SUM(Sales[TotalAmount])
Average Profit = AVERAGE(Sales[Profit])
📅 Time Intelligence
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
🔍 Filtering
High Value Sales = CALCULATE([Total Sales], Sales[Amount] > 50000)
💡 Logical
Is High Value = IF(Sales[Amount] > 50000, "Yes", "No")
🔄 Context in DAX: The Secret Sauce
DAX calculations depend on two types of context:
| Context Type | Meaning | Example |
|---|---|---|
| Row Context | Current row of the table | Used in Calculated Columns |
| Filter Context | Filtered view during visuals/measures | Used in Visuals and Measures |
🔑 CALCULATE() is used to change filter context and is the most important DAX function.
🧠 Measures vs Calculated Columns
| Feature | Measure | Calculated Column |
|---|---|---|
| Computed At | Visual level | Row level (once per row) |
| Stored in Model? | No (computed on demand) | Yes (increases model size) |
| Usage | KPIs, visual aggregations | Grouping, filtering, keys |
| Performance | Faster and lightweight | Slower, increases size |
✔ Use Measures whenever possible.
📘 Example Use Cases
| Use Case | DAX Solution |
|---|---|
| Year-over-Year Sales | CALCULATE([Sales], SAMEPERIODLASTYEAR(...)) |
| Top 5 Products | Use RANKX with filters |
| Profit Margin % | DIVIDE([Profit], [Revenue]) |
| Sales Target Comparison | IF([Sales] > [Target], "Achieved", "Missed") |
⚠️ Common DAX Mistakes
🚫 Using SUM() on text columns
🚫 Creating too many calculated columns instead of measures
🚫 Ignoring context when using CALCULATE()
🚫 Not using a proper Date Table for time intelligence
🚫 Forgetting to use DIVIDE() (safe division with 0 handling)
💡 Performance Tips for DAX
✅ Always prefer measures over calculated columns
✅ Use VAR to store intermediate results for cleaner logic
✅ Use FILTER() sparingly — it can slow things down
✅ Avoid iterators (SUMX, COUNTX) unless needed
✅ Use relationships instead of LOOKUPVALUE when possible
🧪 Advanced DAX Patterns
- Dynamic Titles:
"Sales Report for " & SELECTEDVALUE('Region'[RegionName]) - Custom Ranking:
RANKX(ALL('Product'), [Total Sales]) - Moving Average:
AVERAGEX(DATESINPERIOD(...), [Measure]) - Cumulative Totals with Filters: Use
CALCULATE()+FILTER()+ALLSELECTED()
📊 Summary
DAX is the beating heart of Power BI. With it, you can:
- Build custom KPIs
- Apply business logic
- Analyze trends over time
- Create smarter, interactive reports
Start with simple functions like SUM(), IF(), and CALCULATE() — and work your way up to advanced logic.
The more you master DAX, the more powerful and dynamic your Power BI dashboards become.