DAX Fundamentals — The Language of Power BI

DAX (Data Analysis Expressions) Power BI ki analytical language hai — bina DAX ke Power BI sirf ek drag-and-drop tool hai. Is part mein DAX ki neev rakhenge — basic functions se lekar CALCULATE, FILTER, ALL tak, aur sabse critical concept Row Context vs Filter Context samjhenge. Data Insights par complete deep-dive.

📑 Is Part Mein Aap Kya Sikhenge:

DAX ka foundation — yeh samjhe bina advanced DAX kabhi nahi samajh aayega:

• DAX Kya Hai: Complete theory, syntax rules, aur DAX ka ecosystem
• Calculated Columns vs Measures: Kab kya use karein — most confusing topic cleared
• Basic DAX Functions: SUM, AVERAGE, COUNT, COUNTA, DISTINCTCOUNT, MIN, MAX
• CALCULATE: Power BI ka sabse important function — filter modifiers ke saath
• FILTER: Advanced row-level filtering — CALCULATE ke andar kaise use hota hai
• ALL, ALLEXCEPT, ALLSELECTED: Filter removal functions — kab kya use karein
• Row Context vs Filter Context: DAX ka MOST IMPORTANT concept — isko samjh liya toh DAX master

📋 Data Model Context: Hum apna same Star Schema model use kar rahe hain — Sales (Fact) table connected to Products, Customers, Salesperson, aur Calendar (Dimension) tables. Saare DAX examples isi model par based hain. Part 2 mein humne yeh model design kiya tha.

🔍 Definition: DAX (Data Analysis Expressions) is a formula language specifically designed for data modeling and analytics in Microsoft Power BI, Power Pivot (Excel), and SQL Server Analysis Services (SSAS). It is used to create Calculated Columns, Measures, and Calculated Tables. DAX looks similar to Excel formulas but works fundamentally differently — it operates on entire columns and tables rather than individual cells, and it is deeply context-aware (Row Context and Filter Context). DAX is not a programming language — it's a functional language where you write expressions that return a single value, a table, or modify the filter environment.

🎯 Samjho Hinglish Mein: Excel mein tum ek cell select karte ho aur formula likhte ho — =SUM(A1:A10). Power BI mein cell concept nahi hai. DAX mein tum poori column ya table par kaam karte ho — SUM(Sales[Amount]) matlab "Sales table ke Amount column ki saari values ka sum." Aur sabse badi baat — DAX context-aware hai. Matlab same formula alag-alag results dega depending on kahan use ho raha hai. Agar chart mein "Category" axis par hai, toh SUM har category ke liye alag result dega. Agar card visual mein hai toh grand total dega. Yeh magic hai — aur iska naam hai Filter Context.

💡 DAX Key Characteristics:
• Column-Based: DAX columns aur tables par kaam karta hai, individual cells par nahi. Sales[Amount] = poora column reference.
• Context-Aware: Same measure alag context mein alag result deta hai — visuals, slicers, filters sab context create karte hain.
• Two Types of Output: Scalar value (single number — measures mein) ya Table (calculated tables mein).
• Where DAX is Written: Measures → Modeling tab → New Measure. Calculated Columns → Modeling tab → New Column. Calculated Tables → Modeling tab → New Table.
• Case Insensitive: SUM = Sum = sum — sab same hai. But best practice hai UPPERCASE mein likhna (readability ke liye).
• DAX ≠ M Language: DAX model mein kaam karta hai (after load). M Language Power Query mein kaam karta hai (before load). Dono alag languages hain!

📊 DAX Syntax Rules:

// DAX Syntax Structure: // Measure/Column Name = DAX Expression
// 1. Table[Column] Reference:
SUM(Sales[Amount]) // Table = Sales, Column = Amount

// 2. Measure Name (no table prefix):
Total Sales = SUM(Sales[Amount])

// 3. String
values in double quotes:
FILTER(Products, Products[Category] = "Electronics")

// 4. Date
values with DATE() or dt format:
DATE(2024, 1, 1)

// 5. Comments:
// Single line comment (double slash)
/* Multi-line comment */

// 6. Line breaks for readability:
Total Sales =
SUM(Sales[Amount]) // Readable format

// 7. Logical operators:
// && = AND, || = OR, ! = NOT
// = (equals), <> (not equals), >, =,

📊 DAX Function Categories:

⚠️ Common Mistakes:

• Mistake: DAX ko Excel formulas jaisa treat karna. Fix: DAX mein cell reference (A1, B2) nahi hota — hamesha Table[Column] format use hota hai. DAX context-aware hai, Excel nahi.
• Mistake: DAX aur M Language confuse karna. Fix: DAX = model level (after data load). M Language = Power Query (before data load). Dono bilkul alag languages hain.
• Mistake: Measure likhte waqt = sign bhoolna. Fix: Har DAX expression = sign se start hoti hai: Total Sales = SUM(Sales[Amount])
• Mistake: Column name mein space hone par square brackets bhoolna. Fix: Sales[Order Amount] — brackets mandatory hain jab column name mein space ho.

💬 Interview Questions:

🔍 Definition: In DAX, you can create two types of calculations — Calculated Columns and Measures. A Calculated Column is computed row-by-row when data is loaded/refreshed and stored permanently in the table — it adds a new column to the table. A Measure is computed dynamically at query time based on the current filter context — it is NOT stored in the table, it calculates on-the-fly when a visual requests it. This distinction is one of the most critical concepts in DAX because choosing wrong between them leads to incorrect results, bloated models, and poor performance.

🎯 Samjho Hinglish Mein: Calculated Column ek permanent sticker hai — tum har product par ek price tag laga do, woh hamesha chipka rahega, chahe koi dekhe ya na dekhe. Memory leta hai. Measure ek calculator hai — jab koi puchta hai "total kitna hua?" tab calculate hota hai, store nahi hota. Slicers, filters, visual context ke according dynamically badalta hai. Rule of thumb: Agar value har row ke liye fixed hai aur context se nahi badlegi — Calculated Column. Agar value filters, slicers, visuals ke context ke according change honi chahiye — Measure. 90% cases mein Measure use karo!

💡 Golden Rule — When to Use What:
• Use Calculated Column When: Value row-level hai aur static hai. Jaise: Full Name = FirstName & LastName. Age Group = IF(Age>30, "Senior", "Junior"). Yeh slicer/filter mein use karna ho ya sort karna ho. Relationship key banana ho.
• Use Measure When: Value aggregation/calculation hai jo context se badlegi. Jaise: Total Sales = SUM(Amount). Average Price. YoY Growth %. Top N Products. Percentage of Total. Agar result visual context se change hona chahiye — MEASURE use karo.
• Memory Impact: Calculated Columns permanently stored hain — model size badhata hai. Measures on-the-fly calculate hain — zero storage. Isliye prefer Measures jab possible ho.

📊 Calculated Column vs Measure — Comparison:

💻 Calculated Column Examples:

// Calculated Column — Modeling tab → New Column // These
values are FIXED per row — computed once at refresh
// Example 1: Revenue per unit (row-level calculation)
Revenue Per Unit = Sales[Amount] / Sales[Qty]

// Example 2: Profit Margin category based
on row data
Margin Category =
IF(
Sales[Amount] > 50000,
"High Value",
IF(
Sales[Amount] > 10000,
"Medium Value",
"Low Value"
)
)

// Example 3: Get Product Category
from related Dimension table
Product Category = RELATED(Products[Category])

// These columns will appear in Data View as new columns
// and can be used in slicers, filters, relationships

💻 Measure Examples:

// Measures — Modeling tab → New Measure // These
values are DYNAMIC — change with every filter/slicer
// Example 1: Total Sales (changes by region, product, date)
Total Sales = SUM(Sales[Amount])

// Example 2: Average
Order Value
Avg
Order Value = AVERAGE(Sales[Amount])

// Example 3: Total number of orders

Order Count = COUNTROWS(Sales)

// These measures DON'T appear as columns in Data View
// They only show
values
when dragged into a visual
// Their value AUTOMATICALLY changes with filter context!

⚠️ Common Mistakes:

• Mistake: Har calculation ke liye Calculated Column banana. Fix: 90% calculations Measures honi chahiye. Calculated Columns sirf row-level fixed values ke liye.
• Mistake: Measure banayi lekin slicer mein drag kiya — kaam nahi kiya. Fix: Measures slicer mein nahi aa sakti. Agar slicer mein chahiye toh Calculated Column banao.
• Mistake: Calculated Column banayi jisme SUM use kiya — har row mein grand total aa gaya. Fix: Calculated Columns mein aggregation functions (SUM, AVERAGE) se bachho — woh Row Context mein unexpected behave karte hain. Aggregations ke liye Measures use karo.
• Mistake: Bahut saari Calculated Columns banakar model size bloat karna. Fix: Power Query mein transformations karo (free — storage nahi badhta) ya Measures use karo. Calculated Columns hamesha storage lete hain.

💬 Interview Questions:

🔍 Definition: These are the foundational aggregation functions in DAX. They take a column as input and return a single scalar value. SUM adds all values, AVERAGE calculates the mean, COUNT counts numeric values, COUNTA counts non-blank values (any type), DISTINCTCOUNT counts unique values, MIN returns the smallest value, and MAX returns the largest. These functions are context-aware — their result automatically adjusts based on the current Filter Context (slicers, filters, visual axes).

🎯 Samjho Hinglish Mein: Yeh sab tumhari building blocks hain — jaise cooking mein namak, mirch, tel basic ingredients hain, waise DAX mein SUM, COUNT, AVERAGE basic functions hain. Har advanced formula ultimately in basic functions ke upar built hai. CALCULATE ke andar SUM jaata hai, TOTALYTD ke andar SUM jaata hai. Pehle yeh foundations pakke karo — baaki sab easy ho jaayega.

💡 COUNT vs COUNTA vs COUNTROWS vs DISTINCTCOUNT:
• COUNT(column): Sirf numeric values count karta hai — text aur blanks skip.
• COUNTA(column): Non-blank values count karta hai — numbers + text dono. Sirf blanks skip.
• COUNTROWS(table): Table ki total rows count karta hai — columns ki value se matlab nahi, sirf rows count.
• DISTINCTCOUNT(column): Unique values count karta hai — duplicates remove karke count. Blanks ko bhi ek unique value maanta hai.
• COUNTBLANK(column): Sirf blank/null values count karta hai.

💻 All Basic DAX Measures — Complete:

// ═══════════════════════════════════════════ // SUM — Total of all
values in a column // ═══════════════════════════════════════════ Total Sales = SUM(Sales[Amount]) // If no filter: Grand Total of all sales // If "North" selected in slicer: Only North sales total // Result adapts to filter context AUTOMATICALLY!
Total Quantity = SUM(Sales[Qty])

// ═══════════════════════════════════════════
// AVERAGE — Mean of all
values
// ═══════════════════════════════════════════
Avg Sales = AVERAGE(Sales[Amount])
// Returns average
order value
// Blanks/nulls are EXCLUDED
from calculation

Avg Quantity = AVERAGE(Sales[Qty])

// ═══════════════════════════════════════════
// MIN & MAX — Smallest & Largest value
// ═══════════════════════════════════════════
Lowest Sale = MIN(Sales[Amount])
Highest Sale = MAX(Sales[Amount])
First
Order Date = MIN(Sales[OrderDate])
Last
Order Date = MAX(Sales[OrderDate])

// ═══════════════════════════════════════════
// COUNT — Count numeric
values only
// ═══════════════════════════════════════════
Amount Count = COUNT(Sales[Amount])
// Counts only rows
where Amount has a numeric value
// Skips blanks and text
values

// ═══════════════════════════════════════════
// COUNTA — Count non-blank
values (any type)
// ═══════════════════════════════════════════
Customer Entries = COUNTA(Sales[CustomerID])
// Counts all non-blank CustomerID
values
// Works with text, numbers, dates — anything non-blank

// ═══════════════════════════════════════════
// COUNTROWS — Count total rows in a table
// ═══════════════════════════════════════════
Total Orders = COUNTROWS(Sales)
// Counts all rows in Sales table (within current filter)
// Most reliable row counter — doesn't depend
on any column

// ═══════════════════════════════════════════
// DISTINCTCOUNT — Count unique
values
// ═══════════════════════════════════════════
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])
// If 100 orders
from 25 unique customers → returns 25

Unique Products Sold = DISTINCTCOUNT(Sales[ProductID])

Regions Covered = DISTINCTCOUNT(Customers[Region])

// ═══════════════════════════════════════════
// COUNTBLANK — Count empty/null
values
// ═══════════════════════════════════════════
Missing Emails = COUNTBLANK(Customers[Email])

📊 Expected Results — Context Examples:

Measure: Total Sales = SUM(Sales[Amount])
Context 1: Card Visual (no filters)
→ Result: ₹16,45,000 (Grand Total of all sales)

Context 2: Card Visual + Slicer "Region = North"
→ Result: ₹5,20,000 (Only North region sales)

Context 3: Bar Chart (Category on X-axis)
→ Electronics: ₹8,30,000
→ Accessories: ₹2,15,000
→ Furniture: ₹6,00,000
(Same formula, different result per category — Filter Context!)

Context 4: Matrix (Region × Category)
→ North-Electronics: ₹3,10,000
→ North-Accessories: ₹85,000
→ South-Electronics: ₹2,40,000
→ ... (each cell has its own filter context)

SAME MEASURE — DIFFERENT CONTEXTS — DIFFERENT RESULTS!
This is the POWER of DAX.

📊 COUNT Functions — Quick Comparison:

⚠️ Common Mistakes:

• Mistake: COUNT use karna text column par — 0 return hota hai. Fix: Text columns ke liye COUNTA use karo. COUNT sirf numbers count karta hai.
• Mistake: Total customers count karne ke liye COUNT(Sales[CustomerID]) — duplicates include hote hain. Fix: Unique customers ke liye DISTINCTCOUNT(Sales[CustomerID]) use karo.
• Mistake: AVERAGE mein blanks include ho rahe hain samajhna. Fix: AVERAGE automatically blanks exclude karta hai — sirf non-blank values ka mean calculate karta hai. Agar blanks ko 0 treat karna hai toh pehle blanks replace karo.
• Mistake: SUM text column par use karna — error aata hai. Fix: SUM sirf numeric columns par kaam karta hai. Text column par aggregation chahiye toh CONCATENATEX use karo.

💬 Interview Questions:

🔍 Definition: CALCULATE is the most powerful and most important function in DAX. It evaluates an expression (typically a measure/aggregation) in a modified filter context. The key word is "modified" — CALCULATE takes the existing filter context (whatever slicers, filters, visual axes are active) and then ADDS, REMOVES, or REPLACES filters on top of it. Syntax: CALCULATE(expression, filter1, filter2, ...). Without CALCULATE, you cannot override or manipulate the filter context — which means you cannot do YoY comparisons, percentage of total, filtered KPIs, or any conditional aggregation. CALCULATE is to DAX what SELECT is to SQL — everything revolves around it.

🎯 Samjho Hinglish Mein: SUM(Sales[Amount]) tumhe current context ka total deta hai. Lekin agar tum chahte ho ki "sirf Electronics category ka total dikhao chahe user ne koi bhi slicer select kiya ho" — toh tumhe context OVERRIDE karna padega. CALCULATE yahi karta hai. CALCULATE(SUM(Sales[Amount]), Products[Category] = "Electronics") — yeh kehta hai: "Pehle sab existing filters laga (jo visuals/slicers se aa rahe hain), phir upar se ek AUR filter lagao ki Category = Electronics, phir SUM karo." CALCULATE = "Calculate this expression, BUT with these additional/modified filters."

💡 CALCULATE Syntax Deep Dive:
CALCULATE(expression, filter1, filter2, ...)

• expression: Koi bhi measure ya aggregation — SUM, AVERAGE, COUNTROWS, etc. Yeh woh value hai jo calculate honi hai.
• filter1, filter2, ...: Optional filter arguments — yeh existing filter context ko modify karte hain. Multiple filters comma se separate hote hain — sab AND logic se combine hote hain.
• Filter types: Simple boolean (Table[Column] = "value"), FILTER function, ALL function, ALLEXCEPT, ALLSELECTED, KEEPFILTERS, USERELATIONSHIP, CROSSFILTER, etc.
• Key Behavior: CALCULATE ek naya filter context CREATE karta hai — pehle existing context copy karta hai, phir filter arguments apply karta hai, phir expression evaluate karta hai.

💻 CALCULATE — Examples Basic to Advanced:

// ═══════════════════════════════════════════ // Example 1: Simple Filter — Single Condition // ═══════════════════════════════════════════ Electronics Sales = CALCULATE( SUM(Sales[Amount]), Products[Category] = "Electronics" ) // Always returns Electronics total — regardless of Category slicer // But STILL respects other slicers (Region, Date, etc.)
// ═══════════════════════════════════════════
// Example 2: Multiple Filters — AND Logic
// ═══════════════════════════════════════════
North Electronics =
CALCULATE(
SUM(Sales[Amount]),
Products[Category] = "Electronics",
Customers[Region] = "North"
)
// Returns: Sales
where Category = Electronics AND Region = North
// Multiple filter arguments are ALWAYS combined with AND

// ═══════════════════════════════════════════
// Example 3: Filter with comparison operator
// ═══════════════════════════════════════════
High Value Sales =
CALCULATE(
SUM(Sales[Amount]),
Sales[Amount] > 50000
)
// Total of only those orders
where individual amount > 50,000

// ═══════════════════════════════════════════
// Example 4: Remove ALL filters (Grand Total always)
// ═══════════════════════════════════════════
Grand Total Sales =
CALCULATE(
SUM(Sales[Amount]),
ALL(Sales)
)
// Removes ALL filters
from Sales table
// Always returns grand total — no matter what slicer is selected
// Essential for Percentage of Total calculations!

// ═══════════════════════════════════════════
// Example 5: Percentage of Total
// ═══════════════════════════════════════════
Sales % of Total =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(SUM(Sales[Amount]), ALL(Sales)),
0
)
// Numerator: Current context sales (e.g., "Electronics")
// Denominator: CALCULATE removes all filters → Grand Total
// Result: Electronics% = Electronics Sales / Grand Total

// ═══════════════════════════════════════════
// Example 6: CALCULATE with FILTER function
// ═══════════════════════════════════════════
Premium Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
Sales,
Sales[Amount] > 50000 && Sales[Qty] > 2
)
)
// FILTER iterates row-by-row and keeps rows matching condition
// Use FILTER
when you need complex multi-column conditions

📊 CALCULATE — How It Modifies Filter Context:

Scenario: User has Region slicer = "North" active
Measure: Total Sales = SUM(Sales[Amount])
→ Engine evaluates: SUM
where Region = North
→ Result: ₹5,20,000

Measure: Electronics Sales = CALCULATE(SUM(Sales[Amount]),
Products[Category] = "Electronics")
→ Engine evaluates: SUM
where Region = North AND Category = Electronics
→ Note: Existing filter (North) is KEPT, new filter (Electronics) is ADDED
→ Result: ₹3,10,000

Measure: Grand Total = CALCULATE(SUM(Sales[Amount]), ALL(Sales))
→ Engine evaluates: SUM
where ALL filters REMOVED
→ North slicer IGNORED! ALL overrides existing context
→ Result: ₹16,45,000 (Grand Total always)

CALCULATE = Modify Context + Evaluate Expression

⚠️ Common Mistakes:

• Mistake: CALCULATE ke bina filter modify karna chahna. Fix: DAX mein filter context modify karne ka SIRF ek tarika hai — CALCULATE. Bina CALCULATE ke SUM, COUNT etc. sirf current context mein kaam karenge.
• Mistake: CALCULATE mein filter argument mein complex condition likhna bina FILTER ke. Fix: Simple conditions directly likh sakte ho (Table[Col] = "value"). Multi-column conditions ke liye FILTER function use karo (FILTER(Table, condition1 && condition2)).
• Mistake: CALCULATE samajhna ki yeh "calculate karo" ka short form hai — simple wrapper hai. Fix: CALCULATE bahut complex function hai — yeh filter context ko modify karta hai. Har CALCULATE call ek naya filter context create karta hai. Isko deeply samajhna zaroori hai.
• Mistake: CALCULATE ke filter mein OR condition directly likhna. Fix: CALCULATE ke comma-separated filters AND hote hain. OR ke liye: FILTER(Table, condition1 || condition2) ya Table[Col] IN {"value1", "value2"} use karo.

💬 Interview Questions:

🔍 Definition: FILTER is a table function that iterates over a table row-by-row and returns a subset of rows that satisfy the specified condition. It does NOT return a scalar value — it returns a filtered table. FILTER is most commonly used inside CALCULATE as a filter argument, but it can also be used inside COUNTROWS, SUMX, AVERAGEX, and other iterator functions. Syntax: FILTER(table, condition). The table parameter can be a physical table, another FILTER, ALL(table), VALUES(column), or any table expression.

🎯 Samjho Hinglish Mein: FILTER ek chhalni (sieve) hai. Tum poori Sales table daal do isme, aur ek condition do — "sirf woh rows rakho jahan Amount > 50000." FILTER har row check karega — condition true hai toh row rakhega, false hai toh hatayega. Jo rows bach gayi — woh ek nayi filtered table ban gayi. Ab is filtered table ko CALCULATE, SUMX, COUNTROWS ke andar use karo calculations ke liye.

💡 FILTER Key Points:
• Returns: Table (not a number) — isliye akele use nahi hota, kisi aggregation ke andar use hota hai.
• Row Context: FILTER internally Row Context create karta hai — har row ko individually evaluate karta hai.
• Complex Conditions: Multi-column conditions possible — FILTER(Sales, Sales[Amount] > 50000 && Sales[Qty] > 2)
• Nested FILTER: FILTER ke andar FILTER — progressive filtering.
• Performance: FILTER slow hai kyunki row-by-row iterate karta hai. Simple single-column conditions ke liye CALCULATE mein direct filter prefer karo.

💻 FILTER — Examples:

// ═══════════════════════════════════════════ // Example 1: FILTER inside CALCULATE // ═══════════════════════════════════════════ High Value Orders Total = CALCULATE( SUM(Sales[Amount]), FILTER(Sales, Sales[Amount] > 50000) )
// ═══════════════════════════════════════════
// Example 2: Multi-column condition
// ═══════════════════════════════════════════
Premium North Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
Sales,
Sales[Amount] > 50000
&& RELATED(Customers[Region]) = "North"
)
)
// RELATED is needed because Region is in Customers table
// FILTER creates Row Context → RELATED works inside FILTER

// ═══════════════════════════════════════════
// Example 3: FILTER inside COUNTROWS
// ═══════════════════════════════════════════
High Value
Order Count =
COUNTROWS(
FILTER(Sales, Sales[Amount] > 50000)
)
// Counts rows
where Amount > 50000

// ═══════════════════════════════════════════
// Example 4: FILTER
on ALL table (ignore existing filters)
// ═══════════════════════════════════════════
All Products Above 10K =
CALCULATE(
COUNTROWS(Sales),
FILTER(
ALL(Sales),
Sales[Amount] > 10000
)
)
// ALL(Sales) removes existing filters first
//
Then FILTER applies condition
on unfiltered data
// Result: Count of ALL orders > 10000 regardless of slicers

// ═══════════════════════════════════════════
// Example 5: Nested FILTER
// ═══════════════════════════════════════════
VIP Customers Orders =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
FILTER(Sales, Sales[Amount] > 20000),
Sales[Qty] >= 3
)
)
// First FILTER: rows
where Amount > 20000
// Second FILTER:
from those, rows
where Qty >= 3
// Progressive filtering — like nested
WHERE in SQL

⚠️ Common Mistakes:

• Mistake: FILTER alone measure mein use karna — error aata hai "table returned." Fix: FILTER table return karta hai, scalar nahi. Isko CALCULATE, COUNTROWS, SUMX ke andar wrap karo.
• Mistake: Simple conditions ke liye bhi FILTER use karna. Fix: Single column equality check (Col = "value") ke liye direct CALCULATE filter use karo — faster hai.
• Mistake: FILTER mein related table ka column directly reference karna. Fix: FILTER Row Context create karta hai. Related table ke columns access karne ke liye RELATED() function use karo.

💬 Interview Questions:

🔍 Definition: ALL, ALLEXCEPT, and ALLSELECTED are filter modifier functions used primarily inside CALCULATE. They control which filters to REMOVE from the filter context. ALL removes all filters from a table or specific columns. ALLEXCEPT removes all filters EXCEPT the specified columns (keeps those filters active). ALLSELECTED removes filters applied by the visual's own axes/groupings but PRESERVES filters from external slicers and page/report level filters. These three functions are essential for creating Percentage of Total, Percentage of Category, and other relative measures.

🎯 Samjho Hinglish Mein: Socho tum ek room mein ho aur bahut saari lights on hain (filters active hain). ALL = saari lights band karo (saare filters hatao). ALLEXCEPT = saari lights band karo EXCEPT kitchen ki light (sab hatao except specified columns ke filters). ALLSELECTED = sirf woh lights band karo jo is room (visual) ne khud on ki hain, baaki rooms (slicers) ki lights jaisi hain waisi rehne do. Percentage of Total ke liye ALL use hota hai, Percentage of Category ke liye ALLEXCEPT, aur dynamic user-driven analytics ke liye ALLSELECTED.

💡 ALL vs ALLEXCEPT vs ALLSELECTED — Quick Guide:
• ALL(Table): Removes ALL filters from entire table → Grand Total always. Use: % of Grand Total.
• ALL(Table[Column]): Removes filter from specific column only → other filters remain. Use: specific column filter override.
• ALLEXCEPT(Table, Table[Col1], Table[Col2]): Removes all filters EXCEPT Col1 and Col2 filters. Use: % within a category group.
• ALLSELECTED(Table): Removes visual-level filters but keeps slicer/page/report filters. Use: Dynamic % that respects user's slicer selections.
• Memory Aid: ALL = "Ignore everything." ALLEXCEPT = "Ignore everything except these." ALLSELECTED = "Ignore what this visual added, but respect what user selected."

💻 ALL — Examples:

// ═══════════════════════════════════════════ // ALL(Table) — Remove ALL filters from entire table // ═══════════════════════════════════════════ Grand Total = CALCULATE( SUM(Sales[Amount]), ALL(Sales) ) // Always returns grand total — ignores ALL slicers & filters
// Percentage of Grand Total:
% of Grand Total =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(SUM(Sales[Amount]), ALL(Sales)),
0
)

// ═══════════════════════════════════════════
// ALL(Column) — Remove filter from ONE column only
// ═══════════════════════════════════════════
Sales All Regions =
CALCULATE(
SUM(Sales[Amount]),
ALL(Customers[Region])
)
// Removes ONLY the Region filter
// Other filters (Category, Date, etc.) still active!

💻 ALLEXCEPT — Examples:

// ═══════════════════════════════════════════ 
// ALLEXCEPT — Remove all EXCEPT specified columns 
// ═══════════════════════════════════════════ 
// Percentage within each Category: % Within Category = DIVIDE( SUM(Sales[Amount]), CALCULATE( SUM(Sales[Amount]), ALLEXCEPT(Products, Products[Category]) ), 0 ) 
// Denominator: Removes all filters EXCEPT Category 
// So for "Laptop" in "Electronics": 
// Numerator = Laptop Sales 
// Denominator = All Electronics Sales (category filter kept) 
// Result = Laptop's % within Electronics

// Another Example: % within Region
% Within Region =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(
SUM(Sales[Amount]),
ALLEXCEPT(Customers, Customers[Region])
),
0
)

// For "Delhi" in "North":

// Numerator = Delhi Sales

// Denominator = All North Sales

💻 ALLSELECTED — Examples:

// ═══════════════════════════════════════════ 
// ALLSELECTED — Respect slicer, ignore visual grouping 
// ═══════════════════════════════════════════ % of Filtered Total = DIVIDE( SUM(Sales[Amount]), CALCULATE( SUM(Sales[Amount]), ALLSELECTED(Sales) ), 0 ) 
// If user selects "North" in Region slicer: 
// ALL version: denominator = Grand Total (all regions) 
// ALLSELECTED version: denominator = North Total only 
// ALLSELECTED respects the user's slicer choice! 
// 
// This makes the percentage DYNAMIC based on user selection: 
// "What % of the user's selected data does this row represent?"

📊 ALL vs ALLEXCEPT vs ALLSELECTED — Scenario Comparison:

Scenario: Bar chart shows Products on X-axis, Sales on Y-axis User has Region slicer = "North" selected
Current row in chart: "Laptop"
Laptop (North) Sales = ₹3,10,000

Denominator calculations:

Laptop % Results:
ALL: 3,10,000 / 16,45,000 = 18.8% (of everything)
ALLEXCEPT: 3,10,000 / 8,30,000 = 37.3% (within Electronics)
ALLSELECTED: 3,10,000 / 5,20,000 = 59.6% (within North selection)

⚠️ Common Mistakes:

• Mistake: ALL aur ALLSELECTED mein confuse hona. Fix: ALL = ignore EVERYTHING (grand total). ALLSELECTED = ignore visual grouping but KEEP slicer filters. Test karo — slicer change karke dekho denominator change hota hai ya nahi.
• Mistake: ALLEXCEPT mein wrong table reference dena. Fix: ALLEXCEPT ka first argument woh table hai jisse filters hatane hain. Columns woh hain jinke filters RAKHNE hain. ALLEXCEPT(Products, Products[Category]) = Products table se sab hatao except Category.
• Mistake: ALL function CALCULATE ke bahar use karna. Fix: ALL CALCULATE ke andar filter modifier ke roop mein use hota hai. CALCULATE ke bahar ALL ek table function hai — table return karta hai (jaise COUNTROWS ke andar use ho sakta hai).

💬 Interview Questions:

🔍 Definition: Evaluation Context is the environment in which a DAX expression is evaluated. There are two types: Row Context — exists when DAX is iterating through individual rows of a table (created by Calculated Columns, iterator functions like SUMX, FILTER, ADDCOLUMNS). In Row Context, you can access individual column values of the current row. Filter Context — exists when visuals, slicers, filters, or CALCULATE modify which rows are "visible" for aggregation. In Filter Context, aggregation functions (SUM, COUNT) operate on the filtered subset of data. Understanding these two contexts — when they exist, how they interact, and how CALCULATE performs "Context Transition" from Row Context to Filter Context — is THE MOST IMPORTANT concept in DAX. Without this understanding, every complex DAX formula will be a mystery.

🎯 Samjho Hinglish Mein: Row Context = tum ek register ki ek-ek line padh rahe ho. Har line par tum us row ki values access kar sakte ho — "is row mein product kya hai? Amount kitna hai?" Yeh tab hota hai jab tum Calculated Column banate ho ya SUMX/FILTER use karte ho — DAX ek ek row visit karta hai. Filter Context = tum register ke upar ek filter laga dete ho — "sirf North region dikhao." Ab jo rows dikhti hain unpar SUM lagta hai. Yeh tab hota hai jab visuals, slicers, ya CALCULATE filter set karta hai. Context Transition = jab Row Context ke andar CALCULATE call hota hai, toh current row ka data ek naye Filter Context mein convert ho jaata hai — yeh DAX ka sabse powerful aur sabse tricky concept hai!

💡 Where Each Context Exists:
• Row Context Created By: Calculated Columns (each row), Iterator functions (SUMX, AVERAGEX, MAXX, FILTER, ADDCOLUMNS), each row of a table expression.
• Filter Context Created By: Visual axes (Category on X-axis), Slicers (Region = North), Page/Report filters, CALCULATE function, Row-level security.
• Context Transition: When CALCULATE is called inside a Row Context, it takes all current row values and converts them into equivalent filter conditions — creating a new Filter Context. This is how measures work correctly inside iterator functions.
• Golden Rule: "Row Context = I'm looking at ONE specific row. Filter Context = I'm looking at a FILTERED SET of rows."

📊 Row Context — Detailed Explanation:

 ROW CONTEXT — "I'm visiting each row one by one" ═════════════════════════════════════════════════
Created by: Calculated Columns, SUMX, AVERAGEX, FILTER, ADDCOLUMNS

Example: Calculated Column
Revenue Per Unit = Sales[Amount] / Sales[Qty]

How DAX processes this:
┌─────────────────────────────────────────────────────┐
│ Row 1: Amount = 110000, Qty = 2 → 110000/2 = 55000 │ ← Row Context
│ Row 2: Amount = 5000, Qty = 10 → 5000/10 = 500 │ ← Row Context
│ Row 3: Amount = 24000, Qty = 3 → 24000/3 = 8000 │ ← Row Context
│ Row 4: Amount = 18000, Qty = 1 → 18000/1 = 18000 │ ← Row Context
│ Row 5: Amount = 7500, Qty = 5 → 7500/5 = 1500 │ ← Row Context
└─────────────────────────────────────────────────────┘

In Row Context:
✅ Sales[Amount] → returns current row's amount
✅ Sales[Qty] → returns current row's quantity
❌ SUM(Sales[Amount]) → returns GRAND TOTAL (no filter!)
(because Row Context ≠ Filter Context — SUM needs Filter Context)

📊 Filter Context — Detailed Explanation:

FILTER CONTEXT — "I'm looking at a filtered subset of rows" ════════════════════════════════════════════════════════════
Created by: Visuals, Slicers, CALCULATE

Example: Measure in a Bar Chart
Total Sales = SUM(Sales[Amount])

Chart has Category on X-axis:
┌───────────────────────────────────────────────┐
│ Category = "Electronics" (Filter Context) │
│ → SUM evaluates on rows WHERE │
│ Category = Electronics │
│ → Result: ₹8,30,000 │
├───────────────────────────────────────────────┤
│ Category = "Accessories" (Filter Context) │
│ → SUM evaluates on rows WHERE │
│ Category = Accessories │
│ → Result: ₹2,15,000 │
├───────────────────────────────────────────────┤
│ Category = "Furniture" (Filter Context) │
│ → SUM evaluates on rows WHERE │
│ Category = Furniture │
│ → Result: ₹6,00,000 │
└───────────────────────────────────────────────┘

Each bar in the chart creates its own Filter Context!
Same formula → different results → because different contexts.

In Filter Context:
✅ SUM(Sales[Amount]) → returns filtered total (correct!)
❌ Sales[Amount] alone → ERROR (which row? Filter Context
doesn't know individual rows — it knows filtered SETS)

📊 Context Transition — The Bridge:

💻 Context Examples — Side by Side:

// ═══════════════════════════════════════════ // ROW CONTEXT — Calculated Column // ═══════════════════════════════════════════
// ✅ CORRECT — Row Context, accessing current row
values:
Revenue Per Unit = Sales[Amount] / Sales[Qty]
// Each row gets its own calculation: Row 1 = 55000, Row 2 = 500...

// ❌ WRONG — SUM in Row Context without CALCULATE:
Bad Column = SUM(Sales[Amount])
// Every row shows GRAND TOTAL! SUM ignores Row Context.
// SUM needs Filter Context — Row Context doesn't provide it.

// ✅ FIXED — CALCULATE triggers Context Transition:
Fixed Column = CALCULATE(SUM(Sales[Amount]))
// Now CALCULATE converts Row Context → Filter Context
// Each row's SUM = only that row's amount (context transition)

// ═══════════════════════════════════════════
// FILTER CONTEXT — Measures
// ═══════════════════════════════════════════

// ✅ CORRECT — Filter Context, SUM on filtered set:
Total Sales = SUM(Sales[Amount])
// In card: Grand Total. In chart per category: Category Total.
// Filter Context automatically adjusts result!

// ═══════════════════════════════════════════
// CONTEXT TRANSITION — SUMX with Measure
// ═══════════════════════════════════════════

// Calling a measure inside an iterator triggers context transition:
Test =
SUMX(
Products,
[Total Sales] // [Total Sales] is a measure
)
// SUMX creates Row Context (iterating Products table)
// [Total Sales] is a measure → implicit CALCULATE wraps it
// Context Transition: each row's ProductID → Filter Context
// Result: For each product, Total Sales of THAT product
// Sum of all = Grand Total (correct!)

📊 Complete Context Summary:

⚠️ Common Mistakes:

• Mistake: Calculated Column mein SUM likhna — har row mein grand total aa gaya. Fix: Row Context mein SUM filter context nahi dekhta. Ya toh direct column reference karo (Sales[Amount]) ya CALCULATE lagao context transition ke liye.
• Mistake: Measure mein RELATED use karna — error aata hai. Fix: RELATED Row Context chahta hai. Measures Filter Context mein kaam karte hain. Measure mein related table ka data chahiye toh CALCULATE + filter modifiers ya SUMX + RELATED use karo.
• Mistake: Context Transition ko samjhe bina SUMX ke andar measure call karna aur confused hona results pe. Fix: Jab iterator (SUMX) ke andar measure call hota hai, context transition hoti hai — har row ka data filter context mein convert hota hai. Yeh expected behavior hai aur correct results deta hai — isko samajhna zaroori hai.
• Mistake: Row Context aur Filter Context ko same cheez samajhna. Fix: Dono fundamentally different hain. Row Context = "main is waqt is row par khada hoon." Filter Context = "mere paas in rows ka filtered set hai." CALCULATE bridge hai Row → Filter.

💬 Interview Questions:

DAX Fundamentals ke saare core concepts ek nazar mein:

Next: Power BI Masterclass — Part 4

Agle part mein hum cover karenge: DAX Advanced — Time Intelligence Functions (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESYTD, DATESBETWEEN, PARALLELPERIOD), RELATED vs RELATEDTABLE, IF/SWITCH Logic, DIVIDE, RANKX, TOPN, Variables (VAR/RETURN), aur Iterator Functions (SUMX, AVERAGEX, MAXX, MINX, COUNTX). Part 3 ki foundation ke upar yeh advanced DAX build hoga. Previously completed: MySQL, Pandas, NumPy, Data Cleaning, Matplotlib, Seaborn, Plotly, Excel Masterclass — sab Data Insights par available hai.

Happy Learning & Keep Analyzing! 🚀