Power BI Optimization Series (Tamil) – Episode 5 | DAX Optimization Explained

DAX Optimization Explained | Improve Power BI Report Performance

Meta Description: Power BI reports slow ஆக load ஆகுதா? இந்த episode-ல நம்ம DAX Optimization Techniques பற்றி detail-ஆ பாக்கலாம் — slow measures identify பண்ணுவது, common mistakes avoid பண்ணுவது, மற்றும் performance improve பண்ண best practices.

---

🔍 அறிமுகம்

Power BI report slow ஆகுது என்றா, காரணம் பெரும்பாலும் inefficient DAX measures தான்.
ஒரு model-ல் measures அதிகமா இருந்தா, calculation time கூடும் → அதோட visual refresh நேரமும் அதிகமா ஆகும் → report performance குறையும்.

இந்த வீடியோல நம்ம:

• எந்த DAX formulas performance slow ஆக்குது
• எப்படி identify பண்ணலாம்
• எப்படி rewrite பண்ணலாம்
• என்று detail-ஆப் practical demo உடன் பாக்கலாம்.

---

⚡ Common DAX Mistakes and Fixes

1️⃣ FILTER Function – Proper vs Improper Usage

Problem:
FILTER() wrongly used on table-level instead of column-level → slow execution.

Incorrect DAX:

CALCULATE(
    SUM(Sales[TotalSales]),
    FILTER(Sales, Sales[Quantity] > 0 && Sales[TotalSales] > 0)
)

Correct DAX:

CALCULATE(
    SUM(Sales[TotalSales]),
    Sales[Quantity] > 0
)

✅ Always filter column-level (not full table).
This avoids unnecessary table scans and improves performance.

---

2️⃣ Nested IF vs SWITCH Function

Problem:
Using multiple nested IFs → slows visuals due to repeated evaluations.

Incorrect DAX:

IF([TotalSales] > 50000, "Premium",
   IF([TotalSales] > 30000, "High",
   IF([TotalSales] > 10000, "Medium", "Low")))

Optimized DAX (using VAR + SWITCH):

VAR SalesValue = [TotalSales]
RETURN
SWITCH(
    TRUE(),
    SalesValue > 50000, "Premium",
    SalesValue > 30000, "High",
    SalesValue > 10000, "Medium",
    "Low"
)

✅ Use VAR to store reusable values.
✅ SWITCH evaluates faster and cleaner than multiple IF conditions.

---

3️⃣ SELECTEDVALUE Function

Common Mistake:
Using SELECTEDVALUE() without alternate result → returns blank when multiple items selected.

Correct DAX:

SELECTEDVALUE(Products[Category], "Multiple Selected")

✅ Always provide a second argument as alternate result to avoid blank visuals.

---

4️⃣ ALL vs ALLSELECTED vs ALLEXCEPT

Understanding the Difference:

Function	Use Case	Removes Filters
ALL()	Grand Total	All filters
ALLSELECTED()	Percentage of Selected	Keeps slicer selections
ALLEXCEPT()	Conditional totals	Removes all filters except specified columns

Example:

• Use ALL() when calculating Grand Total ignoring slicers.
• Use ALLEXCEPT() when you want totals to change only by selected column (e.g., Category).
• Use ALLSELECTED() for Percentage of Total visuals.

✅ Wrong function → wrong total or wrong percentage display.

---

5️⃣ KEEPFILTERS & REMOVEFILTERS

Tip:

• Use KEEPFILTERS() only when you need existing filter context retained.
• Use REMOVEFILTERS() to clear slicers before calculating totals.

✅ Misuse leads to repeated values or wrong total results.

---

🧠 Summary of Optimized DAX Practices

Problem	Fix
Table-level filters	Use column-level filters
Nested IF conditions	Replace with SWITCH + VAR
Missing alternate in SELECTEDVALUE	Add default text
Wrong use of ALL/ALLSELECTED	Choose function as per requirement
Overusing filters	Apply only when needed

---

🧪 Demo Highlight

In the video, I demonstrated how to:

• Compare performance between correct & incorrect DAX using Performance Analyzer
• Identify slow visuals (measure duration in ms)
• Optimize step-by-step and validate faster load times

---

🚀 Benefits After Optimization

✅ Report visuals load faster
✅ Refresh time reduce ஆகும்
✅ DAX easier to maintain & debug
✅ Better overall Power BI performance

---

📈 Next Episode

Next episodeல நம்ம Data Model Size Optimization பற்றி பாக்கப் போறோம் —
Power BI model எவ்வளவு space எடுத்துக்கொள்கிறது, அதை எப்படி குறைக்கலாம் என்பதையும் real demo உடன் explain பண்ணுறோம்.

---

🏁 முடிவு

இந்த DAX Optimization techniques follow பண்ணினா,
உங்க Power BI report speed visible-ஆ improve ஆகும்.
Slow visuals, wrong totals, blank outputs — எல்லாத்தையும் fix பண்ணிக்கலாம்.

வீடியோ பிடிச்சிருந்தா Like + Subscribe பண்ணுங்க 💡
உங்க feedback commentsல சொல்லுங்க 🙌