DAX Advanced — Time Intelligence, Ranking, Iterators & More

Part 3 mein humne DAX ki neev rakhi — CALCULATE, Filter Context, Row Context. Ab uske upar advanced DAX build karenge — Time Intelligence se YoY/MoM comparisons, RANKX se rankings, SUMX se row-level calculations, Variables se clean code — sab kuch ek jagah, Data Insights par.

📑 Is Part Mein Aap Kya Sikhenge:

DAX ka advanced arsenal — har function deep theory + real examples ke saath:

• Time Intelligence: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESYTD, DATESBETWEEN, PARALLELPERIOD
• RELATED vs RELATEDTABLE: Cross-table data access — kab kya use karein
• IF, SWITCH: Conditional logic in DAX
• DIVIDE: Safe division — error-free calculations
• RANKX: Dynamic rankings — Dense vs Skip
• TOPN: Top N analysis
• VAR / RETURN: Variables for clean, efficient DAX
• Iterator Functions: SUMX, AVERAGEX, MAXX, MINX, COUNTX — row-by-row power

📋 Reminder: Same Star Schema model — Sales (Fact) connected to Products, Customers, Salesperson, aur Calendar (Date Table). Date Table marked hai aur continuous dates hain. Part 2 & 3 mein yeh sab set kiya tha.

🔍 Definition: Time Intelligence functions are a set of DAX functions specifically designed to perform calculations over time periods — Year-to-Date totals, Same Period Last Year comparisons, Moving Averages, Period-over-Period growth, and more. These functions manipulate the Date filter context to shift, expand, or compare date ranges. They REQUIRE a properly configured Date Table (continuous dates, marked as date table) connected to the Fact table. Without a Date Table, Time Intelligence functions will either error out or return incorrect results.

🎯 Samjho Hinglish Mein: Boss kehta hai — "Mujhe batao ki is saal ab tak kitni sales hui (YTD)? Pichle saal same time pe kitni thi (SPLY)? Month-over-Month growth kya hai? Last 3 months ka rolling average kya hai?" — yeh sab Time Intelligence functions se hota hai. Yeh functions date filters ko manipulate karte hain — "current month ki jagah pichle saal ka same month dikhao" ya "January se ab tak ka total dikhao." Yeh DAX ki sabse powerful capabilities mein se ek hai aur real-world dashboards mein sabse zyada use hoti hai.

💡 Prerequisites for Time Intelligence:
• Date Table: MANDATORY — continuous dates, no gaps, marked as Date Table.
• Relationship: Calendar[Date] → Sales[OrderDate] (1:N active relationship).
• These functions go inside CALCULATE: Most Time Intelligence functions return a TABLE of dates — they act as filter modifiers inside CALCULATE.
• Key Functions Covered: TOTALYTD, TOTALQTD, TOTALMTD, SAMEPERIODLASTYEAR, DATEADD, DATESYTD, DATESBETWEEN, PARALLELPERIOD.

💻 TOTALYTD / TOTALQTD / TOTALMTD:

// ═══════════════════════════════════════════ // TOTALYTD — Year-to-Date Running Total // ═══════════════════════════════════════════ // Syntax: TOTALYTD(expression, dates_column, [filter], [year_end_date])
YTD Sales =
TOTALYTD(
SUM(Sales[Amount]),
Calendar[Date]
)
// If current month is March 2024:
// Returns SUM of Jan + Feb + Mar 2024
// If current month is July 2024:
// Returns SUM of Jan + Feb + Mar + Apr + May + Jun + Jul 2024
// Accumulates from Jan 1st of current year

// For Financial Year (April to March):
FY YTD Sales =
TOTALYTD(
SUM(Sales[Amount]),
Calendar[Date],
"3/31" // Year ends on March 31
)

// ═══════════════════════════════════════════
// TOTALQTD — Quarter-to-Date
// ═══════════════════════════════════════════
QTD Sales =
TOTALQTD(
SUM(Sales[Amount]),
Calendar[Date]
)
// If current month is Feb (Q1): Returns Jan + Feb total
// If current month is May (Q2): Returns Apr + May total

// ═══════════════════════════════════════════
// TOTALMTD — Month-to-Date
// ═══════════════════════════════════════════
MTD Sales =
TOTALMTD(
SUM(Sales[Amount]),
Calendar[Date]
)
// Accumulates from 1st of current month to current date

💻 SAMEPERIODLASTYEAR — Year-over-Year Comparison:

// ═══════════════════════════════════════════ 
// SAMEPERIODLASTYEAR — Same Period, Previous Year 
// ═══════════════════════════════════════════ 
// Returns a table of dates shifted back by exactly 1 year
Last Year Sales =
CALCULATE(
SUM(Sales[Amount]),
SAMEPERIODLASTYEAR(Calendar[Date])
)

// If current filter = March 2024

// → Returns March 2023 sales

// If current filter = Q1 2024

// → Returns Q1 2023 sales

// If current filter = Year 2024

// → Returns Year 2023 sales

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

// YoY Growth % — Complete Measure

// ═══════════════════════════════════════════
YoY Growth % =
VAR CurrentSales = SUM(Sales[Amount])
VAR PreviousYearSales =
CALCULATE(
SUM(Sales[Amount]),
SAMEPERIODLASTYEAR(Calendar[Date])
)
RETURN
DIVIDE(
CurrentSales - PreviousYearSales,
PreviousYearSales,
0
)

// Returns percentage growth: (Current - Previous) / Previous

// Format this measure as Percentage in Power BI

💻 DATEADD — Flexible Date Shifting:

// ═══════════════════════════════════════════ 
// DATEADD — Shift dates by any interval 
// ═══════════════════════════════════════════ 
// Syntax: DATEADD(dates_column, number_of_intervals, interval) 
// Intervals: DAY, MONTH, QUARTER, YEAR

// Previous Month Sales:
Previous Month Sales =
CALCULATE(
SUM(Sales[Amount]),
DATEADD(Calendar[Date], -1, MONTH)
)

// March 2024 → shifts to February 2024

// Previous Quarter Sales:
Previous Quarter Sales =
CALCULATE(
SUM(Sales[Amount]),
DATEADD(Calendar[Date], -1, QUARTER)
)

// Same Month Last Year (alternative to SAMEPERIODLASTYEAR):
SMLY Sales =
CALCULATE(
SUM(Sales[Amount]),
DATEADD(Calendar[Date], -1, YEAR)
)

// MoM Growth %:
MoM Growth % =
VAR CurrentMonth = SUM(Sales[Amount])
VAR PrevMonth =
CALCULATE(
SUM(Sales[Amount]),
DATEADD(Calendar[Date], -1, MONTH)
)
RETURN
DIVIDE(CurrentMonth - PrevMonth, PrevMonth, 0)

💻 DATESYTD, DATESBETWEEN, PARALLELPERIOD:

// ═══════════════════════════════════════════ // DATESYTD — Returns YTD date table (used inside CALCULATE) // ═══════════════════════════════════════════ YTD Sales v2 = CALCULATE( SUM(Sales[Amount]), DATESYTD(Calendar[Date]) ) // Equivalent to TOTALYTD — returns dates from Jan 1 to current date // For Financial Year: DATESYTD(Calendar[Date], "3/31")
// ═══════════════════════════════════════════
// DATESBETWEEN — Custom date range
// ═══════════════════════════════════════════
Q1 2024 Sales =
CALCULATE(
SUM(Sales[Amount]),
DATESBETWEEN(
Calendar[Date],
DATE(2024, 1, 1),
DATE(2024, 3, 31)
)
)
// Returns sales between Jan 1 - Mar 31, 2024
// Hard-coded dates — useful for specific period analysis

// ═══════════════════════════════════════════
// PARALLELPERIOD — Shift entire period
// ═══════════════════════════════════════════
Previous Year Full =
CALCULATE(
SUM(Sales[Amount]),
PARALLELPERIOD(Calendar[Date], -1, YEAR)
)
// Difference from DATEADD:
// DATEADD shifts exact dates back
// PARALLELPERIOD shifts and returns FULL period
// If filter = Feb 15, 2024:
// DATEADD(-1, MONTH): Jan 15, 2024 (shifted date)
// PARALLELPERIOD(-1, MONTH): Full January 2024 (entire month)

📊 Time Intelligence Functions — Quick Reference:

⚠️ Common Mistakes:

• Mistake: Time Intelligence functions use karna bina Date Table ke. Fix: Pehle Calendar table banao (CALENDAR/CALENDARAUTO), Mark as Date Table karo, relationship create karo. Bina iske sab fail hoga.
• Mistake: SAMEPERIODLASTYEAR mein BLANK aana kyunki previous year ka data nahi hai. Fix: Date Table ka range ensure karo ki previous year cover ho. IF(ISBLANK()) se handle karo display mein.
• Mistake: TOTALYTD mein Financial Year handle nahi karna. Fix: Third parameter mein year-end date do: TOTALYTD(SUM(Sales[Amount]), Calendar[Date], "3/31")
• Mistake: Time Intelligence function CALCULATE ke bahar use karna. Fix: SAMEPERIODLASTYEAR, DATEADD, etc. table return karte hain — inhein CALCULATE ke andar filter modifier ke roop mein use karo.

💬 Interview Questions:

🔍 Definition: RELATED fetches a single value from a related table on the "One" side of a 1:N relationship. It works in Row Context (Calculated Columns, iterators) and follows the relationship from the Many side (Fact) to the One side (Dimension). RELATEDTABLE returns a table of related rows from the "Many" side of a relationship. It works when you are on the One side (Dimension) and want to access multiple matching rows from the Many side (Fact). Think of RELATED as "look up one value" and RELATEDTABLE as "get all matching rows."

🎯 Samjho Hinglish Mein: RELATED = VLOOKUP jaisa. Tum Sales table ki ek row par ho aur puchte ho "is ProductID ka ProductName kya hai?" — RELATED Products table mein jaake look up karta hai aur ek value laata hai. RELATEDTABLE = ulta. Tum Products table ki ek row par ho aur puchte ho "is Product ki kitni sales rows hain?" — RELATEDTABLE Sales table se saari matching rows laata hai ek table ke roop mein. RELATED: Many → One (one value). RELATEDTABLE: One → Many (table of rows).

💡 Key Differences:
• RELATED: Row Context required. Direction: Many → One. Returns: Single scalar value. Used in: Calculated Columns, inside FILTER/SUMX.
• RELATEDTABLE: Row Context required. Direction: One → Many. Returns: Table. Used in: Calculated Columns on Dimension tables, inside COUNTROWS/SUMX.
• Important: Dono Row Context mein kaam karte hain — measures mein directly use karna tricky hai (context transition needed ya iterator ke andar use karo).

💻 RELATED Examples:

// ═══════════════════════════════════════════ // RELATED — Fetch value from Dimension (Many → One) // ═══════════════════════════════════════════
// Calculated Column in Sales table:
Product Category = RELATED(Products[Category])
// Looks up ProductID in Sales → finds matching row in Products
// → returns Category value for that product

Customer Region = RELATED(Customers[Region])
// Brings Region from Customers table into Sales table

Product Price = RELATED(Products[Price])
// Brings list price from Products dimension

// Inside FILTER (Row Context exists):
North Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
Sales,
RELATED(Customers[Region]) = "North"
)
)

💻 RELATEDTABLE Examples:

// ═══════════════════════════════════════════ // RELATEDTABLE — Get related rows (One → Many) // ═══════════════════════════════════════════
// Calculated Column in Products table:
Order Count = COUNTROWS(RELATEDTABLE(Sales))
// For each product, counts how many Sales rows reference it
// Laptop: 15 orders, Mouse: 42 orders, etc.

Total Revenue = SUMX(RELATEDTABLE(Sales), Sales[Amount])
// For each product, sums Amount from all its Sales rows

// Calculated Column in Customers table:
Customer Orders = COUNTROWS(RELATEDTABLE(Sales))
// How many orders each customer has placed

📊 RELATED vs RELATEDTABLE:

⚠️ Common Mistakes:

• Mistake: Measure mein RELATED directly use karna — error aata hai. Fix: RELATED Row Context chahta hai. Measures Filter Context mein hote hain. Solution: SUMX/FILTER ke andar use karo jahan Row Context available ho.
• Mistake: RELATED ko One side se Many side access karne ke liye use karna. Fix: RELATED sirf Many → One direction mein kaam karta hai. One → Many ke liye RELATEDTABLE use karo.
• Mistake: RELATEDTABLE directly measure mein assign karna — "table cannot be converted to scalar." Fix: RELATEDTABLE table return karta hai. Isko COUNTROWS, SUMX, AVERAGEX ke andar wrap karo.

💬 Interview Questions:

🔍 Definition: IF evaluates a condition and returns one value if TRUE and another if FALSE. SWITCH evaluates an expression against a list of values and returns the matching result — like a multi-case IF statement. SWITCH is cleaner and more readable than nested IFs when you have 3+ conditions on the same column/expression.

🎯 Samjho Hinglish Mein: IF ek simple agar-toh-warna hai: "Agar sales 50000 se zyada hai toh High, warna Low." SWITCH ek menu card hai: "Value 1 hai toh yeh do, Value 2 hai toh woh do, Value 3 hai toh yeh karo, warna default do." Jab 2 conditions hain — IF use karo. Jab 5-6 conditions ek column par hain — SWITCH use karo (nested IF se bahut cleaner hai).

💻 IF Examples:

// Syntax: IF(condition, true_result, false_result)

// Simple IF — Calculated Column:
Sales Category =
IF(
Sales[Amount] > 50000,
"High Value",
"Standard"
)

// Nested IF — Calculated Column:
Sales Tier =
IF(
Sales[Amount] > 100000, "Premium",
IF(
Sales[Amount] > 50000, "High",
IF(
Sales[Amount] > 10000, "Medium",
"Low"
)
)
)

// IF in Measure — Conditional KPI:
Sales Status =
IF(
SUM(Sales[Amount]) > 500000,
"Target Achieved ✅",
"Below Target ❌"
)

💻 SWITCH Examples:

// Syntax: SWITCH(expression, value1, result1, value2, result2, ..., else_result)
// SWITCH — Cleaner than nested IF:
Region
Group =
SWITCH(
Customers[Region],
"North", "Zone A",
"South", "Zone B",
"East", "Zone C",
"West", "Zone D",
"Other Zone" // Default / else
)

// SWITCH with TRUE() — Range-based conditions:
Amount Bucket =
SWITCH(
TRUE(),
Sales[Amount] > 100000, "Premium",
Sales[Amount] > 50000, "High",
Sales[Amount] > 10000, "Medium",
"Low"
)
// SWITCH(TRUE()) evaluates conditions top-to-bottom
// Returns first TRUE match —
order matters!
// Much cleaner than nested IF for range categorization

// SWITCH in Measure — Dynamic metric selection:
Selected Metric =
SWITCH(
SELECTEDVALUE(MetricTable[Metric]),
"Sales", SUM(Sales[Amount]),
"Quantity", SUM(Sales[Qty]),
"Orders", COUNTROWS(Sales),
BLANK()
)
// User selects metric
from a slicer → chart shows that metric!

⚠️ Common Mistakes:

• Mistake: 5 levels ka nested IF likhna — unreadable code. Fix: SWITCH(TRUE()) use karo — same result, much cleaner.
• Mistake: SWITCH(TRUE()) mein conditions ka order galat rakhna. Fix: Most specific condition pehle rakho: >100000 pehle, phir >50000, phir >10000. Warna >10000 pehla TRUE ho jayega har baar.
• Mistake: IF mein false_result bhoolna — BLANK return hota hai. Fix: Hamesha explicitly false result do. Debugging mein asaani hogi.

💬 Interview Questions:

🔍 Definition: DIVIDE performs safe division by handling division-by-zero errors gracefully. Syntax: DIVIDE(numerator, denominator, alternate_result). If the denominator is zero or BLANK, it returns the alternate_result (default is BLANK) instead of throwing an error. This is the recommended way to divide in DAX — never use the / operator for measures that might encounter zero denominators.

🎯 Samjho Hinglish Mein: Normal division A / B mein agar B = 0 hai toh error aata hai — "Infinity" ya "NaN" dikhai deta hai visuals mein. DIVIDE function smart hai — agar 0 se divide hone wala hai toh error ki jagah tumhara specified alternate value return karta hai (jaise 0 ya BLANK ya "N/A"). Har percentage, ratio, growth measure mein DIVIDE use karo — life easy ho jaayegi.

💻 DIVIDE Examples:

// Syntax: DIVIDE(numerator, denominator, alternate_result)

// Basic safe division:
Avg Price Per Unit =
DIVIDE(
SUM(Sales[Amount]),
SUM(Sales[Qty]),
0
)

// If total Qty = 0 → returns 0 instead of error

// Percentage of Total (safe):
Sales % =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(SUM(Sales[Amount]), ALL(Sales)),
0
)

// YoY Growth (safe):
YoY % =
VAR Current = SUM(Sales[Amount])
VAR Previous = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))
RETURN
DIVIDE(Current - Previous, Previous, 0)

// ❌ BAD — Using / operator:
Bad Ratio = SUM(Sales[Amount]) / SUM(Sales[Qty])

// If Qty = 0 → ERROR! Infinity in visuals!

// ✅ GOOD — Using DIVIDE:
Good Ratio = DIVIDE(SUM(Sales[Amount]), SUM(Sales[Qty]), 0)

⚠️ Common Mistakes:

• Mistake: / operator use karna production measures mein. Fix: Hamesha DIVIDE() use karo — zero division safe.
• Mistake: DIVIDE ka third argument nahi dena — default BLANK aata hai. Fix: Explicitly 0 ya meaningful value do. BLANK visuals mein confusing ho sakta hai.

💬 Interview Questions:

🔍 Definition: RANKX returns the rank of a value within a table based on a specified expression. Syntax: RANKX(table, expression, [value], [order], [ties]). It evaluates the expression for each row in the table, sorts the results, and returns the rank of the current context's value. The order parameter controls ascending (ASC) or descending (DESC) ranking. The ties parameter controls how tied values are handled — SKIP (default, like 1,2,2,4) or DENSE (like 1,2,2,3).

🎯 Samjho Hinglish Mein: Boss bola "Mujhe top selling products ka ranking dikhao." RANKX yehi karta hai — saare products ko sales ke basis par rank karta hai. #1 sabse zyada sales, #2 doosra, etc. Agar do products ki sales equal hain toh "ties" ka sawaal aata hai — SKIP mein dono #2 milega aur next #4 hoga (3 skip). DENSE mein dono #2 aur next #3 hoga (gap nahi). RANKX dynamic hai — slicer change karo toh ranking update ho jaati hai.

💻 RANKX Examples:

// ═══════════════════════════════════════════ 
// Basic RANKX — Rank products by sales 
// ═══════════════════════════════════════════ Product Rank = RANKX( ALL(Products[ProductName]), 
// Table to rank over [Total Sales], 
// Expression to evaluate , 
// Value (optional, auto) DESC, 
// Order (highest = #1) Dense 
// Ties handling ) 
// ALL(Products[ProductName]) → evaluates rank across ALL products 
// even if a slicer filters some out 
// [Total Sales] → measure used for ranking

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

// RANKX — Rank customers by order count

// ═══════════════════════════════════════════
Customer Rank =
RANKX(
ALL(Customers[CustomerName]),
[Order Count],
,
DESC,
Dense
)

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

// Dynamic Rank — Respects slicers

// ═══════════════════════════════════════════
Dynamic Product Rank =
RANKX(
ALLSELECTED(Products[ProductName]),
[Total Sales],
,
DESC,
Dense
)

// ALLSELECTED → ranks only within user's slicer selection

// If user selects "Electronics" → ranks only Electronics products

📊 SKIP vs DENSE Ranking:

SKIP (default): Tied items share rank, next rank SKIPS
→ 1, 2, 2, 4, 5 (rank 3 skipped)
DENSE: Tied items share rank, next rank is CONSECUTIVE
→ 1, 2, 2, 3, 4 (no gap)

⚠️ Common Mistakes:

• Mistake: RANKX ka first argument mein filtered table dena — ranking change hota hai slicers se. Fix: ALL() ya ALLSELECTED() use karo depending on requirement. ALL = absolute ranking, ALLSELECTED = dynamic within selection.
• Mistake: RANKX measure total row mein bhi rank show karta hai (meaningless). Fix: IF(HASONEVALUE(Products[ProductName]), [Product Rank], BLANK()) — total row mein BLANK dikhao.
• Mistake: DESC/ASC confuse karna. Fix: DESC = highest value gets Rank 1 (sabse zyada sales = #1). ASC = lowest value gets Rank 1 (sabse kam = #1).

💬 Interview Questions:

🔍 Definition: TOPN returns a table containing the top N rows based on a specified expression. Syntax: TOPN(n_value, table, expression, [order]). It does NOT return a scalar value — it returns a table. It is used inside CALCULATE, SUMX, COUNTROWS, or other functions that accept tables. TOPN is commonly used to answer questions like "Total sales of top 5 products" or "Average revenue of top 10 customers."

🎯 Samjho Hinglish Mein: Boss bole "Sirf top 5 products ki total sales batao" — TOPN Products table se top 5 rows nikalta hai (sales ke basis par), phir un 5 rows ke sales ka SUM karte ho. TOPN ek filtered table deta hai — akela use nahi hota, hamesha kisi aggregation ke andar wrap karo.

💻 TOPN Examples:

// ═══════════════════════════════════════════ // Top 5 Products by Sales — Total // ═══════════════════════════════════════════ Top 5 Products Sales = CALCULATE( [Total Sales], TOPN( 5, Products, [Total Sales], DESC ) ) // TOPN returns top 5 product rows by Total Sales // CALCULATE evaluates [Total Sales] filtered to those 5 products
// ═══════════════════════════════════════════
// Count of Top 10 Customers
// ═══════════════════════════════════════════
Top 10 Customer Count =
COUNTROWS(
TOPN(
10,
Customers,
[Total Sales],
DESC
)
)

// ═══════════════════════════════════════════
// Bottom 3 Products (lowest sales)
// ═══════════════════════════════════════════
Bottom 3 Sales =
CALCULATE(
[Total Sales],
TOPN(
3,
Products,
[Total Sales],
ASC // ASC = lowest first = bottom N
)
)

// ═══════════════════════════════════════════
// Top N with Dynamic Parameter (slicer-driven)
// ═══════════════════════════════════════════
// Create a "TopN Parameter" table: {3, 5, 10, 20}
// User selects value
from slicer
Dynamic Top N Sales =
CALCULATE(
[Total Sales],
TOPN(
SELECTEDVALUE(TopNTable[Value], 5),
Products,
[Total Sales],
DESC
)
)

⚠️ Common Mistakes:

• Mistake: TOPN akele measure mein use karna — "cannot convert table to scalar." Fix: TOPN table return karta hai. CALCULATE, SUMX, COUNTROWS ke andar wrap karo.
• Mistake: Ties handling — agar 5th aur 6th product ka same sales hai toh TOPN 6 rows return kar sakta hai. Fix: TOPN ties mein extra rows include karta hai by default. Agar exactly N chahiye toh secondary sort column add karo.

💬 Interview Questions:

🔍 Definition: VAR (Variable) in DAX allows you to store intermediate results and reuse them within the same expression. Variables are declared with VAR variableName = expression and the final result is returned with RETURN expression. Variables improve readability, debugging, and performance — the expression is evaluated ONCE and stored, even if referenced multiple times. Variables are immutable (cannot be changed after assignment) and their scope is limited to the measure/column they are defined in.

🎯 Samjho Hinglish Mein: Bina variables ke tumhe same calculation baar baar likhna padta hai — jaise SUM(Sales[Amount]) ek measure mein 3 jagah use ho raha hai. Variables se tum ek baar calculate karke store kar lo — VAR TotalSales = SUM(Sales[Amount]) — phir TotalSales naam se baar baar use karo. Code clean hota hai, DAX engine ek hi baar calculate karta hai (performance better), aur debugging easy hoti hai (variable ki value check kar sakte ho). VAR/RETURN DAX ki best practices mein #1 par hai.

💻 VAR/RETURN Examples:

// ═══════════════════════════════════════════ // Example 1: YoY Growth % (Clean with Variables) // ═══════════════════════════════════════════ YoY Growth % = VAR CurrentSales = SUM(Sales[Amount]) VAR PreviousYearSales = CALCULATE( SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]) ) VAR GrowthAmount = CurrentSales - PreviousYearSales RETURN DIVIDE(GrowthAmount, PreviousYearSales, 0)
// ═══════════════════════════════════════════
// Example 2: Conditional KPI with Variables
// ═══════════════════════════════════════════
Sales KPI =
VAR ActualSales = SUM(Sales[Amount])
VAR Target = 500000
VAR Achievement = DIVIDE(ActualSales, Target, 0)
RETURN
IF(
Achievement >= 1,
"Target Achieved ✅ (" & FORMAT(Achievement, "0%") & ")",
"Below Target ❌ (" & FORMAT(Achievement, "0%") & ")"
)

// ═══════════════════════════════════════════
// Example 3: Complex Measure — Multiple Variables
// ═══════════════════════════════════════════
Sales Performance Card =
VAR TotalSales = SUM(Sales[Amount])
VAR TotalOrders = COUNTROWS(Sales)
VAR AvgOrderValue = DIVIDE(TotalSales, TotalOrders, 0)
VAR UniqueCustomers = DISTINCTCOUNT(Sales[CustomerID])
VAR SalesPerCustomer = DIVIDE(TotalSales, UniqueCustomers, 0)
RETURN
"Sales: ₹" & FORMAT(TotalSales, "#,##0") &
" | Orders: " & TotalOrders &
" | AOV: ₹" & FORMAT(AvgOrderValue, "#,##0")

⚠️ Common Mistakes:

• Mistake: RETURN likhna bhool jaana — syntax error. Fix: Har VAR block ke end mein RETURN mandatory hai. RETURN ke baad final expression/value likho.
• Mistake: VAR ko reassign karna chahna — error aata hai. Fix: DAX variables immutable hain. Naya value chahiye toh naya VAR banao.
• Mistake: Variables ka context samajhna — VAR definition time par evaluate hota hai, RETURN time par nahi. Fix: Yeh advanced point hai — VAR jis context mein define hota hai usi context mein evaluate hota hai. RETURN mein context change hone par bhi VAR ki value same rehti hai.

💬 Interview Questions:

🔍 Definition: Iterator functions (X-functions) iterate over a table row-by-row, evaluate an expression for each row (in Row Context), and then aggregate the results. SUMX sums the row-level results. AVERAGEX averages them. MAXX finds the maximum. MINX finds the minimum. COUNTX counts rows where the expression is not blank. Syntax: SUMX(table, expression). The key difference from SUM is that iterators allow row-level calculations BEFORE aggregation — you can multiply columns, apply IF logic, or reference related tables at each row.

🎯 Samjho Hinglish Mein: SUM(Sales[Amount]) seedha column ka total karta hai — koi row-level calculation nahi. Lekin agar tumhe har row par pehle Qty × Price calculate karna hai AUR phir unka total chahiye — SUM kaam nahi karega kyunki SUM sirf ek column le sakta hai. SUMX har row par Qty × Price karega (Row Context), phir sab ka sum — SUMX(Sales, Sales[Qty] * Sales[Price]). Iterator = "pehle har row par yeh karo, phir sab ka aggregate karo."

💡 SUM vs SUMX — Key Difference:
• SUM(column): Single column ka direct total. Fast. No row-level calculation possible. SUM(Sales[Amount]).
• SUMX(table, expression): Row-by-row expression evaluate, then sum. Slower but powerful. SUMX(Sales, Sales[Qty] * RELATED(Products[Price])).
• When to use SUM: Jab sirf ek column ka total chahiye — SUM fast hai.
• When to use SUMX: Jab row-level calculation chahiye aggregation se pehle — multiplication, IF logic, RELATED values, complex expressions.
• All X functions: SUMX, AVERAGEX, MAXX, MINX, COUNTX, CONCATENATEX, PRODUCTX.

💻 Iterator Functions — All Examples:

// ═══════════════════════════════════════════ // SUMX — Row-level calculation
then sum // ═══════════════════════════════════════════
// Calculate revenue using Qty × Price
from related table:
Calculated Revenue =
SUMX(
Sales,
Sales[Qty] * RELATED(Products[Price])
)
// Row 1: 2 * 55000 = 110000
// Row 2: 10 * 500 = 5000
// Row 3: 3 * 8000 = 24000
// SUMX result: 110000 + 5000 + 24000 + ... = Total

// Conditional sum — only high value rows:
High Value Revenue =
SUMX(
Sales,
IF(Sales[Amount] > 50000, Sales[Amount], 0)
)

// Weighted Average Price:
Weighted Avg Price =
DIVIDE(
SUMX(Sales, Sales[Qty] * RELATED(Products[Price])),
SUM(Sales[Qty]),
0
)

// ═══════════════════════════════════════════
// AVERAGEX — Row-level calculation
then average
// ═══════════════════════════════════════════
Avg Revenue Per
Order =
AVERAGEX(
Sales,
Sales[Qty] * RELATED(Products[Price])
)
// Calculates Qty×Price for each row,
then averages all results

// ═══════════════════════════════════════════
// MAXX / MINX — Row-level calculation
then max/min
// ═══════════════════════════════════════════
Highest Single
Order Revenue =
MAXX(
Sales,
Sales[Qty] * RELATED(Products[Price])
)
// Finds the single
order with highest Qty × Price

Smallest
Order =
MINX(Sales, Sales[Amount])
// Equivalent to MIN(Sales[Amount]) for simple cases

// Find the product name with highest sales:
Top Product Name =
MAXX(
TOPN(1, Products, [Total Sales], DESC),
Products[ProductName]
)
// TOPN gets top 1 product row, MAXX extracts the name

// ═══════════════════════════════════════════
// COUNTX — Count rows
where expression is non-blank
// ═══════════════════════════════════════════
High Value
Order Count =
COUNTX(
Sales,
IF(Sales[Amount] > 50000, 1, BLANK())
)
// For each row: if Amount > 50000 → returns 1,
else BLANK
// COUNTX counts non-blank results = count of high value orders

// ═══════════════════════════════════════════
// CONCATENATEX — Concatenate text row-by-row
// ═══════════════════════════════════════════
Product List =
CONCATENATEX(
Products,
Products[ProductName],
", ", // Delimiter
Products[ProductName], ASC // Sort order
)
// Result: "Chair, Keyboard, Laptop, Monitor, Mouse"

📊 SUM vs SUMX Comparison:

Sales Data:

= 7,500 → Total: ₹1,64,500 → Row-level calculation then sum. Powerful. Slightly slower. Same result here, but SUMX is needed when: Amount column doesn't exist (calculate from Qty × Price) Row-level IF logic needed before summing RELATED table values needed in calculation

⚠️ Common Mistakes:

• Mistake: Simple column total ke liye SUMX use karna. Fix: SUMX(Sales, Sales[Amount]) aur SUM(Sales[Amount]) same result dete hain, lekin SUM fast hai. Simple totals ke liye SUM use karo.
• Mistake: SUMX ke andar SUM likhna (aggregation inside iterator). Fix: SUMX Row Context mein kaam karta hai. Uske andar individual column references use karo (Sales[Qty]), aggregation functions nahi (SUM(Sales[Qty]) — yeh har row par grand total dega).
• Mistake: Iterator ka first argument mein wrong table dena. Fix: SUMX ka pehla argument woh table hai jis par iterate karna hai. Agar Sales table ki rows par iterate karna hai toh SUMX(Sales, ...). Agar Products par iterate karna hai toh SUMX(Products, ...).
• Mistake: RELATED function SUMX ke bahar use karna (no Row Context). Fix: RELATED Row Context chahta hai. SUMX Row Context create karta hai — isliye RELATED SUMX ke andar perfectly kaam karta hai.

💬 Interview Questions:

DAX Advanced ke saare functions ek nazar mein:

Next: Power BI Masterclass — Part 5

Agle part mein hum cover karenge: DAX Real-World Scenarios — Running Total / Cumulative Sum, Year-over-Year (YoY) Growth %, Month-over-Month (MoM) Comparison, Rolling Average (3-month, 12-month), Percentage of Total Calculations, Dynamic Ranking with Ties, aur Handling Blanks & Errors in DAX. Part 3-4 ke functions ko real dashboards mein apply karenge. Previously completed: MySQL, Pandas, NumPy, Data Cleaning, Matplotlib, Seaborn, Plotly, Excel Masterclass — sab Data Insights par available hai.

Happy Learning & Keep Analyzing! 🚀