DAX Real-World Scenarios — Production-Ready Measures

Part 3 mein DAX fundamentals aur Part 4 mein advanced functions sikhe. Ab unhe real dashboards mein apply karenge — Running Totals, YoY Growth, MoM Comparison, Rolling Averages, Percentage of Total, Dynamic Rankings aur Error Handling. Yeh woh measures hain jo har professional Power BI dashboard mein hote hain. Data Insights par complete guide.

📑 Is Part Mein Aap Kya Sikhenge:

Real dashboards mein use hone wale 7 production-ready DAX patterns — har ek deep theory + multiple variations ke saath:

• Running Total: Cumulative sum — line charts mein growth trajectory dikhana
• YoY Growth %: Year-over-Year comparison — complete measure with edge cases
• MoM Comparison: Month-over-Month analysis — absolute & percentage change
• Rolling Average: 3-month & 12-month moving averages — trend smoothing
• % of Total: Grand total, category-level, & dynamic user-selection percentages
• Dynamic Ranking: Rank with ties, conditional ranking, Top N filter
• Blanks & Errors: ISBLANK, IFERROR, BLANK(), COALESCE — bulletproof measures

📋 Reminder: Same Star Schema model — Sales (Fact) + Products, Customers, Salesperson, Calendar (Dimensions). Calendar table marked as Date Table. Sab measures assume karte hain ki [Total Sales] = SUM(Sales[Amount]) aur [Order Count] = COUNTROWS(Sales) already created hain as base measures.

🔍 Definition: A Running Total (Cumulative Sum) is a measure that accumulates values over time — each period's value includes the sum of all previous periods plus the current period. In a line chart showing monthly sales, January shows January sales, February shows January + February, March shows Jan + Feb + Mar, and so on. It's used to visualize the growth trajectory and track progress toward annual targets. In DAX, a running total is built using CALCULATE with a date filter that expands from a fixed start point to the current date.

🎯 Samjho Hinglish Mein: Socho tumhari monthly savings hain — January mein ₹10,000 bachaye, February mein ₹15,000, March mein ₹12,000. Running Total: January = ₹10,000, February = ₹25,000 (10K+15K), March = ₹37,000 (10K+15K+12K). Har month mein pichle saare months ka total bhi include hai. Line chart mein yeh ek upward growing curve dikhata hai — progress track karne ke liye perfect hai. Power BI mein yeh CALCULATE + FILTER on dates se banta hai.

💡 Running Total vs YTD:
• Running Total: Continuously accumulates — never resets. Jan 2023 se shuru hota hai aur Dec 2024 tak badhta jaata hai. Cross-year bhi accumulate karta hai.
• YTD (Year-to-Date): Har saal January mein RESET hota hai. Jan-Mar 2024 ka YTD = Jan+Feb+Mar 2024. January 2025 mein phir se zero se start.
• When to use Running Total: Overall business growth trajectory, lifetime revenue tracking, target achievement progress.
• When to use YTD: Annual performance measurement, year-wise comparison.

💻 Running Total — All Variations:

// ════ // Method 1: Running Total using FILTER + MAX // ══ Running Total = CALCULATE( [Total Sales],
FILTER
( ALL
(Calendar), Calendar[Date] <= //MAX(Calendar[Date]) ) )
// How it works:
// 1. ALL(Calendar) removes existing date filters
// 2. FILTER keeps only dates <= current period's max date
// 3. SUM runs
on this expanded date range //
// For March 2024: sums ALL sales
from beginning to Mar 31, 2024 // For July 2024: sums ALL sales
from beginning to Jul 31, 2024
// ═══════════════════════════════════════════
// Method 2: Running Total with VAR (Cleaner)
// ═══════════════════════════════════════════
Running Total v2 =
VAR CurrentDate = MAX(Calendar[Date])
RETURN
CALCULATE(
[Total Sales],
FILTER(
ALL(Calendar),
Calendar[Date] // ═══════════════════════════════════════════
// Method 3: YTD Running Total (resets each year)
// ═══════════════════════════════════════════
YTD Running Total =
TOTALYTD(
[Total Sales],
Calendar[Date]
)
// Resets every January 1st — Year-to-Date accumulation

// ═══════════════════════════════════════════
// Method 4: Running Total within Year (DATESYTD)
// ═══════════════════════════════════════════
YTD Running v2 =
CALCULATE(
[Total Sales],
DATESYTD(Calendar[Date])
)

// ═══════════════════════════════════════════
// Method 5: Running Order Count
// ═══════════════════════════════════════════
Cumulative Orders =
VAR CurrentDate = MAX(Calendar[Date])
RETURN
CALCULATE(
[Order Count],
FILTER(
ALL(Calendar),
Calendar[Date] <= CurrentDate
)
)

📊 Expected Output — Running Total in Line Chart:

Running Total: Never resets — keeps growing
YTD Total: Resets every January — annual tracking

⚠️ Common Mistakes:

• Mistake: ALL(Calendar) bhoolna — running total sirf current month ka total dikhata hai. Fix: ALL(Calendar) zaroori hai dates ka filter hatane ke liye — phir FILTER mein range set karo. Bina ALL ke existing month filter SUM ko restrict kar deta hai.
• Mistake: Running total card visual mein meaningless hai — hamesha grand total dikhayega. Fix: Running total sirf time-based visuals (line chart, matrix with months) mein meaningful hai. Card mein Total Sales use karo.
• Mistake: Running Total aur YTD confuse karna. Fix: Running Total never resets. YTD har saal January mein reset hota hai. Dono alag measures banao.

💬 Interview Questions:

🔍 Definition: Year-over-Year (YoY) Growth measures the percentage change between the current period and the same period in the previous year. Formula: (Current Year Value - Previous Year Value) / Previous Year Value × 100. It is the most commonly used KPI in business dashboards — every executive wants to know "Are we growing compared to last year?" YoY can be applied at any granularity — monthly (Mar 2024 vs Mar 2023), quarterly (Q1 2024 vs Q1 2023), or yearly (2024 vs 2023).

🎯 Samjho Hinglish Mein: March 2024 mein sales ₹5,00,000 aur March 2023 mein ₹4,00,000. YoY Growth = (5L - 4L) / 4L = 25%. Matlab pichle saal se 25% zyada sales hui. Agar negative aaye toh sales giri. Har dashboard mein yeh measure hota hai — mostly KPI card mein ya line chart mein monthly YoY trend dikhate hain.

💡 YoY Variations:
• YoY Growth %: (Current - Previous) / Previous — percentage change.
• YoY Absolute Change: Current - Previous — raw difference in value.
• Previous Year Value: SAMEPERIODLASTYEAR ya DATEADD(-1, YEAR).
• YoY Arrow/Icon: IF logic se ▲ (growth) ya ▼ (decline) dikhao — dashboards mein professional dikhta hai.

💻 YoY — Complete Measure Set:

// ═══════════════════════════════════════════
// Base: Previous Year Sales
// ═══════════════════════════════════════════
PY Sales =
CALCULATE( [Total Sales],
SAMEPERIODLASTYEAR(Calendar[Date]) )

// ═══════════════════════════════════════════
// YoY Absolute Change
// ═══════════════════════════════════════════
YoY Change =
VAR CurrentSales = [Total Sales]
VAR PreviousSales = [PY Sales]
RETURN
CurrentSales - PreviousSales
// Returns raw difference: ₹5,00,000 - ₹4,00,000 = ₹1,00,
000

// ═══════════════════════════════════════════
// YoY Growth % (Production-Ready)
// ═══════════════════════════════════════════
YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PreviousSales = [PY Sales]
VAR Growth = CurrentSales - PreviousSales
RETURN
DIVIDE(Growth, PreviousSales, 0)
// Format as Percentage in Power BI
// Result: 0.25 → displayed as 25%

// ═══════════════════════════════════════════
// YoY with Arrow Indicator (for KPI cards)
// ═══════════════════════════════════════════
YoY Indicator =
VAR GrowthPct = [YoY Growth %]
RETURN
SWITCH(
TRUE(),
ISBLANK(GrowthPct), "N/A",
GrowthPct > 0, "▲ " & FORMAT(GrowthPct, "0.0%"),
GrowthPct 0, "▼ " & FORMAT(GrowthPct, "0.0%"),
"— 0.0%"
)
// Returns: "▲ 25.0%" or "▼ -12.5%" or "N/A"

// ═══════════════════════════════════════════
// YoY using DATEADD (Alternative)
// ═══════════════════════════════════════════
PY Sales v2 =
CALCULATE(
[Total Sales],
DATEADD(Calendar[Date], -1, YEAR)
)
// Same result as SAMEPERIODLASTYEAR
// But DATEADD is more flexible (can shift by any interval)

📊 Expected Output:

+₹7,500 N/A (no PY data)

⚠️ Common Mistakes:

• Mistake: YoY % calculate karte waqt / operator use karna — zero division error jab PY = 0. Fix: Hamesha DIVIDE use karo with 0 ya BLANK as alternate.
• Mistake: PY Sales BLANK aane par confuse hona. Fix: Calendar table ka date range check karo — kya previous year ke dates exist karte hain? ISBLANK se handle karo.
• Mistake: YoY Growth % ko format nahi karna — 0.25 dikhta hai 25% ki jagah. Fix: Measure select karo → Measure Tools → Format → Percentage.

💬 Interview Questions:

🔍 Definition: Month-over-Month (MoM) analysis compares a metric's value in the current month with the previous month. It reveals short-term trends — is performance improving or declining month by month? MoM is more granular than YoY and is used for operational dashboards where quick performance changes need to be tracked. DAX uses DATEADD with -1 MONTH to shift to the previous month.

🎯 Samjho Hinglish Mein: March ki sales ₹18,000 aur February ki ₹24,000. MoM Change = ₹18,000 - ₹24,000 = -₹6,000. MoM Growth = -6000/24000 = -25%. Matlab pichle month se 25% sales giri. Operational managers daily yeh check karte hain — "Is month kaise ja raha hai compared to last month?" Weekly reviews mein MoM sabse important metric hai.

💻 MoM — Complete Measure Set:

// ═══════════════════════════════════════════
// Previous Month Sales
// ═══════════════════════════════════════════
PM Sales =
CALCULATE( [Total Sales], DATEADD(Calendar[Date],
-1,
MONTH) )
// ═══════════════════════════════════════════
// MoM Absolute Change
// ═══════════════════════════════════════════
MoM Change = [Total Sales] - [PM Sales]

// ═══════════════════════════════════════════
// MoM Growth % (Production-Ready)
// ═══════════════════════════════════════════
MoM Growth % =
VAR CurrentMonth = [Total Sales]
VAR PreviousMonth = [PM Sales]
RETURN
DIVIDE(
CurrentMonth - PreviousMonth,
PreviousMonth,
0
)

// ═══════════════════════════════════════════
// MoM with Indicator
// ═══════════════════════════════════════════
MoM Status =
VAR Change = [MoM Growth %]
RETURN
SWITCH(
TRUE(),
ISBLANK(Change), "—",
Change > 0.1, "🟢 Strong Growth",
Change > 0, "🟡 Slight Growth",
Change > -0.1, "🟠 Slight Decline",
"🔴 Sharp Decline"
)

// ═══════════════════════════════════════════
// Previous Quarter Sales (Bonus)
// ═══════════════════════════════════════════
PQ Sales =
CALCULATE(
[Total Sales],
DATEADD(Calendar[Date], -1, QUARTER)
)

QoQ Growth % =
DIVIDE(
[Total Sales] - [PQ Sales],
[PQ Sales],
0
)

📊 Expected Output:

+500.0% 🟢 Strong Growth

⚠️ Common Mistakes:

• Mistake: January ke PM Sales ke liye previous year December ka data nahi aana. Fix: Calendar table mein December dates honi chahiye. DATEADD(-1, MONTH) automatically cross-year shift karti hai.
• Mistake: MoM % ko YoY jaisa treat karna. Fix: MoM highly volatile hoti hai (seasonal patterns). Sirf MoM par business decisions mat lo — YoY ke saath compare karo.
• Mistake: MoM % bahut bada aana (jaise 500%) aur confuse hona. Fix: Small base numbers se bade percentages aate hain (₹7,500 → ₹45,000 = 500%). Absolute change bhi dikhao % ke saath.

💬 Interview Questions:

🔍 Definition: A Rolling Average (Moving Average) smooths out short-term fluctuations and highlights long-term trends. A 3-month rolling average for March 2024 = average of January + February + March 2024 sales. A 12-month rolling average for December 2024 = average of Jan-Dec 2024 sales. As each month progresses, the window "rolls" forward — dropping the oldest month and adding the newest. Rolling averages are essential for trend analysis, forecasting, and removing seasonal noise from data.

🎯 Samjho Hinglish Mein: Monthly sales bahut up-down hoti hain — December mein high, January mein low. Yeh zigzag pattern line chart mein confusing dikhta hai. Rolling Average ek smooth line banata hai — 3 months ka average le-leke aage badhta hai. Jaise tum 3 months ki window se dekhte ho aur har baar ek month aage slide karte ho. Result: trend clearly dikhai deta hai — "overall sales badh rahi hain ya ghatt rahi hain" bina seasonal noise ke.

💡 Rolling Average Logic:
• 3-Month Rolling: Current month + 2 previous months / 3. Short-term trend.
• 12-Month Rolling: Current month + 11 previous months / 12. Long-term trend, removes seasonality completely.
• DAX Approach: DATESINPERIOD ya DATEADD se date window define karo, CALCULATE mein use karo, phir DIVIDE by number of months.
• Dashboard Use: Line chart mein actual monthly sales ke saath rolling average line overlay karo — smooth trend line ban jaati hai.

💻 Rolling Average — All Variations:

// ═══════════════════════════════════════════
// 3-Month Rolling Average (Using DATESINPERIOD)
// ═══════════════════════════════════════════
Rolling Avg 3M =
VAR
LastDate =
MAX(Calendar[Date]) VAR
SalesLast3M =
CALCULATE( [Total Sales],
DATESINPERIOD( Calendar[Date], LastDate,
-3,
MONTH ) )
RETURN
DIVIDE(SalesLast3M, 3, 0)
// DATESINPERIOD:
from LastDate, go back 3 months
// Returns total sales in that 3-month window
// DIVIDE by 3 = average per month
// ═══════════════════════════════════════════
// 12-Month Rolling Average
// ═══════════════════════════════════════════
Rolling Avg 12M =
VAR LastDate = MAX(Calendar[Date])
VAR SalesLast12M =
CALCULATE(
[Total Sales],
DATESINPERIOD(
Calendar[Date],
LastDate,
-12,
MONTH
)
)
RETURN
DIVIDE(SalesLast12M, 12, 0)

// ═══════════════════════════════════════════
// Dynamic Rolling Average (counts actual months)
// ═══════════════════════════════════════════
Rolling Avg 3M Dynamic =
VAR LastDate = MAX(Calendar[Date])
VAR PeriodDates =
DATESINPERIOD(Calendar[Date], LastDate, -3, MONTH)
VAR SalesInPeriod =
CALCULATE([Total Sales], PeriodDates)
VAR MonthsWithData =
CALCULATE(
DISTINCTCOUNT(Calendar[MonthNum]),
PeriodDates,
Sales
)
RETURN
DIVIDE(SalesInPeriod, MonthsWithData, 0)
// Counts only months that actually had sales
// Useful for early periods
where 3 months of data don't exist yet

📊 Expected Output:

First 2 months: 3M avg may use less than 3 months data
3M Avg for Mar: (1,15,000 + 24,000 + 18,000) / 3 = ₹52,333
3M Avg for Apr: (24,000 + 18,000 + 7,500) / 3 = ₹16,500

⚠️ Common Mistakes:

• Mistake: First few months mein 3-month average galat aana (kyunki 3 months data nahi hai). Fix: Dynamic version use karo jo actual months with data count kare, ya IF logic lagao: IF(month_count < 3, BLANK(), rolling_avg).
• Mistake: DIVIDE mein hard-coded 3 ya 12 dena jab actual months kam hain. Fix: DISTINCTCOUNT se actual months count karo — especially new datasets ke liye.
• Mistake: 12-month rolling average aur YTD average confuse karna. Fix: 12M Rolling hamesha pichle 12 months ka window hai. YTD sirf current year ka Jan se current month tak hai. Alag concepts hain.

💬 Interview Questions:

🔍 Definition: Percentage of Total shows how much a specific item contributes to the overall total. There are three common variations: % of Grand Total (item / everything), % Within Category (item / its category total), and % of User Selection (item / total of what user has filtered). Each variation uses a different filter removal function — ALL, ALLEXCEPT, or ALLSELECTED — in the denominator. This is one of the most frequently requested KPIs in dashboards.

🎯 Samjho Hinglish Mein: "Laptop ki sales poori company ki sales ka kitna percent hai?" — % of Grand Total. "Laptop ki sales Electronics category ki sales ka kitna percent hai?" — % Within Category. "User ne North region select kiya hai — Laptop ki sales North ki total sales ka kitna percent hai?" — % of User Selection. Teeno alag questions hain — teeno ke liye alag measures chahiye.

💻 All Three Percentage Variations:

// ═══════════════════════════════════════════ 

// 1. % of Grand Total (ALL)

// ═══════════════════════════════════════════
% of Grand Total =
VAR
CurrentSales = [Total Sales]
VAR 
GrandTotal = CALCULATE([Total Sales], 
ALL(Sales)) RETURN 
DIVIDE(CurrentSales, GrandTotal,
0) 
// Denominator ignores ALL filters → always grand total

// Even if user selects "North" — denominator stays grand total

// ═══════════════════════════════════════════

// 2. % Within Category (ALLEXCEPT)

// ═══════════════════════════════════════════
% Within Category =
VAR CurrentSales = [Total Sales]
VAR CategoryTotal =
CALCULATE(
[Total Sales],
ALLEXCEPT(Products, Products[Category])
)
RETURN
DIVIDE(CurrentSales, CategoryTotal, 0)

// Denominator: removes all Product filters EXCEPT Category

// For Laptop (Electronics): Laptop Sales / All Electronics Sales

// For Mouse (Accessories): Mouse Sales / All Accessories Sales

// ═══════════════════════════════════════════

// 3. % of User Selection (ALLSELECTED)

// ═══════════════════════════════════════════
% of Selection =
VAR CurrentSales = [Total Sales]
VAR SelectedTotal =
CALCULATE([Total Sales], ALLSELECTED(Sales))
RETURN
DIVIDE(CurrentSales, SelectedTotal, 0)

// Denominator: respects slicer selections

// If user selects "North" → denominator = North total

// If user selects "Electronics" → denominator = Electronics total

// Most user-friendly for interactive dashboards!

// ═══════════════════════════════════════════

// 4. % Within Region (Bonus)

// ═══════════════════════════════════════════
% Within Region =
DIVIDE(
[Total Sales],
CALCULATE(
[Total Sales],
ALLEXCEPT(Customers, Customers[Region])
),
0
)

📊 Expected Output — All Three Compared:

Slicer: Region = "North" selected

Denominators:
% Grand Total: ₹16,45,000 (everything, ignores North filter)
% Category: Electronics→₹8,30,000, Accessories→₹2,15,000
% Selection: ₹5,20,000 (only North total — respects slicer)

⚠️ Common Mistakes:

• Mistake: ALL(Sales) ki jagah ALL(Products) likhna — different behavior. Fix: ALL(Sales) fact table se saare filters hatata hai. ALL(Products) sirf Products table ke filters hatata hai. Grand total ke liye ALL(Sales) ya ALL() use karo carefully.
• Mistake: % format nahi lagana — 0.067 dikhta hai 6.7% ki jagah. Fix: Measure → Measure Tools → Format → Percentage.
• Mistake: % of Total matrix ke total row mein 100% se different aana. Fix: Rounding differences ho sakti hain. 2 decimal places dikhao — 100.0% ke close hoga.

💬 Interview Questions:

🔍 Definition: Dynamic Ranking creates ranks that automatically update based on user's slicer selections, handle tied values appropriately, and can filter visuals to show only Top N items. This goes beyond basic RANKX — it includes conditional ranking (rank only if data exists), slicer-driven ranking (rank within user's selection), Top N filtering (show only Top 5 in a table), and multi-criteria ranking (break ties with secondary sort).

🎯 Samjho Hinglish Mein: Basic RANKX sirf rank number deta hai. Production dashboard mein zyada chahiye — "Sirf top 5 products dikhao" (TOPN filter), "User ne Electronics select kiya toh sirf Electronics mein ranking dikhao" (ALLSELECTED), "Do products ki same sales hai toh alphabetically sort karo" (secondary criteria), "Agar product ki sales 0 hai toh rank mat dikhao" (conditional). Yeh sab patterns real dashboards mein daily use hote hain.

💻 Dynamic Ranking — Production Patterns:

// ═══════════════════════════════════════════
// 1. Basic Dynamic Rank (respects slicers)
// ═══════════════════════════════════════════
Product Rank =
IF
( HASONEVALUE(Products[ProductName]),
RANKX( ALLSELECTED(Products[ProductName]), [Total Sales],
, DESC,
Dense ),
BLANK() )
// HASONEVALUE: prevents rank showing
on total row
// ALLSELECTED: ranks within user's slicer selection
// Dense: no gaps in ranking (1,2,2,3 instead of 1,2,2,4)
// ═══════════════════════════════════════════
// 2. Top N Filter (Show only Top 5 in table)
// ═══════════════════════════════════════════
Show Top 5 =
VAR CurrentRank = [Product Rank]
RETURN
IF(
CurrentRank 5 && CurrentRank > 0,
[Total Sales],
BLANK()
)
// Returns sales only for top 5 ranked products
// BLANK for others — table visual won't show BLANK rows

// ═══════════════════════════════════════════
// 3. Conditional Rank (skip blanks/zeros)
// ═══════════════════════════════════════════
Conditional Rank =
IF(
HASONEVALUE(Products[ProductName])
&& NOT(ISBLANK([Total Sales]))
&& [Total Sales] > 0,
RANKX(
FILTER(
ALLSELECTED(Products[ProductName]),
[Total Sales] > 0
),
[Total Sales],
,
DESC,
Dense
),
BLANK()
)
// Only ranks products that actually have sales > 0
// Products with zero/blank sales get no rank (BLANK)

// ═══════════════════════════════════════════
// 4. Rank with Medal Emojis (Visual Enhancement)
// ═══════════════════════════════════════════
Rank Display =
VAR R = [Product Rank]
RETURN
SWITCH(
R,
1, "🥇 #1",
2, "🥈 #2",
3, "🥉 #3",
IF(NOT(ISBLANK(R)), "#" & R, BLANK())
)

⚠️ Common Mistakes:

• Mistake: Total row mein rank number dikhna. Fix: HASONEVALUE check lagao — total row mein BLANK dikhao.
• Mistake: Zero-sales products ko bhi rank dena. Fix: FILTER se zero/blank sales products hatao RANKX ke first argument se.
• Mistake: Slicer change karne par ranks update na hona. Fix: ALL ki jagah ALLSELECTED use karo — dynamic ranking milegi.

💬 Interview Questions:

🔍 Definition: In real-world data, BLANK values (Power BI's equivalent of NULL) and errors are inevitable. DAX provides several functions to detect, handle, and replace blanks and errors gracefully: BLANK() returns a blank value, ISBLANK() checks if a value is blank, IF(ISBLANK()) replaces blanks, IFERROR() handles calculation errors, COALESCE() returns the first non-blank value from a list, and DIVIDE() handles division-by-zero. Proper error handling ensures dashboards never show confusing errors to end users.

🎯 Samjho Hinglish Mein: Real data mein kuch products ki sales nahi hui (BLANK), kuch customers ka email missing hai (BLANK), pichle saal ka data nahi hai toh YoY BLANK aayega. Agar tum yeh handle nahi karte toh dashboard mein "(Blank)", "NaN", "Error" dikhta hai — bahut unprofessional lagta hai. ISBLANK se check karo, IF se replace karo, COALESCE se fallback value do, IFERROR se errors trap karo. Production dashboard mein har measure bulletproof hona chahiye — koi bhi scenario mein ugly error nahi dikhna chahiye.

💡 Key Functions for Error Handling:
• BLANK(): Returns a blank value. Use: IF(condition, value, BLANK()) — intentionally blank return karo.
• ISBLANK(value): Returns TRUE if value is blank/null. Use: IF(ISBLANK([Sales]), "No Data", [Sales])
• IFERROR(expression, alternate): If expression throws error → returns alternate. Use: error-prone calculations wrap karo.
• COALESCE(value1, value2, ...): Returns first non-blank value from the list. Use: COALESCE([PY Sales], [Sales], 0) — cascading fallback.
• DIVIDE(num, den, alt): Safe division — handles zero denominator. Already covered in Part 4.

💻 Error Handling — Complete Examples:

// ═══════════════════════════════════════════
// BLANK() — Return intentional blank
// ═══════════════════════════════════════════
Sales Display =
IF( [Total Sales] = 0,
BLANK(),
// Show nothing instead of 0 [Total Sales] )
// Visuals hide BLANK rows — cleaner tables
// ═══════════════════════════════════════════
// ISBLANK — Check and replace blanks
// ═══════════════════════════════════════════
Safe YoY % =
VAR Current = [Total Sales]
VAR Previous = [PY Sales]
RETURN
IF(
ISBLANK(Previous) || Previous = 0,
BLANK(), // Can't calculate YoY without PY data
DIVIDE(Current - Previous, Previous, 0)
)

// ═══════════════════════════════════════════
// IFERROR — Trap calculation errors
// ═══════════════════════════════════════════
Safe Calculation =
IFERROR(
SUM(Sales[Amount]) / SUM(Sales[Qty]),
0
)
// If division error occurs → returns 0 instead of error
// Note: DIVIDE is better for division — IFERROR is for other errors

// ═══════════════════════════════════════════
// COALESCE — First non-blank value (cascading fallback)
// ═══════════════════════════════════════════
Best Available Sales =
COALESCE(
[Total Sales], // Try current sales first
[PY Sales], // If blank, try previous year
0 // If both blank, return 0
)
// COALESCE checks
values left-to-right
// Returns first non-blank value it finds

// ═══════════════════════════════════════════
// Complete Bulletproof KPI Card Measure
// ═══════════════════════════════════════════
KPI Display =
VAR Sales = [Total Sales]
VAR PYSales = [PY Sales]
VAR Growth =
IF(
ISBLANK(PYSales) || PYSales = 0,
BLANK(),
DIVIDE(Sales - PYSales, PYSales, 0)
)
VAR GrowthText =
IF(
ISBLANK(Growth),
"N/A",
FORMAT(Growth, "+0.0%;-0.0%")
)
VAR Icon =
SWITCH(
TRUE(),
ISBLANK(Growth), "⚪",
Growth > 0, "🟢",
Growth 0, "🔴",
"🟡"
)
RETURN
Icon & " ₹" & FORMAT(COALESCE(Sales, 0), "#,##0")
& " (" & GrowthText & ")"
// Output: "🟢 ₹5,00,000 (+25.0%)" or "⚪ ₹0 (N/A)"

📊 Error Handling — Quick Reference:

⚠️ Common Mistakes:

• Mistake: Har jagah IFERROR lagana bina samjhe ki error kyun aa rahi hai. Fix: Pehle root cause fix karo (data type, relationship). IFERROR last resort hai — errors mask karta hai.
• Mistake: BLANK aur "" (empty string) confuse karna. Fix: BLANK() actual null hai. "" ek empty string hai — ISBLANK("") = FALSE. Visuals "" ko ek value treat karte hain. BLANK use karo, "" nahi.
• Mistake: COALESCE mein alag data types mix karna (number + text). Fix: COALESCE ke saare arguments same type ke hone chahiye. Mix karne par type conversion errors aa sakte hain.

💬 Interview Questions:

DAX Real-World Scenarios ke saare patterns ek nazar mein:

Next: Power BI Masterclass — Part 6

Agle part mein hum cover karenge: Visualizations — Conditions Focus. Card, KPI, Multi-row Card, Table vs Matrix, Bar vs Column, Line vs Area, Pie vs Donut vs Treemap, Scatter vs Bubble, Gauge vs KPI Visual, aur Map Visuals — har visual ke liye "KAB use karein" conditions focus ke saath. Previously completed: MySQL, Pandas, NumPy, Data Cleaning, Matplotlib, Seaborn, Plotly, Excel Masterclass — sab Data Insights par available hai.

Happy Learning & Keep Analyzing! 🚀