13
CALCULATE and FILTER Mechanics
🇬🇧 English Definition
CALCULATE is the most powerful function in DAX. It evaluates an expression in a modified filter context. FILTER returns a table of matching rows and is passed into CALCULATE for complex filters.
🇮🇳 Hinglish Definition
CALCULATE existing filter context ko change ya override karke formula calculate karta hai. FILTER function condition matching rows ki table return karta hai.
// CALCULATE Simple Filter Modification
Mohali Sales = CALCULATE([Total Sales], Sales[City] = "Mohali")
// Expected Output: ₹1,30,000
// CALCULATE with FILTER Function
High Value Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[Sales] > 10000))
14
Filter Modifiers & Sales Share %
| Function | Purpose / Kaam |
|---|---|
| CALCULATE | Evaluate expression in modified filter context |
| FILTER | Return table matching logical condition |
| ALL | Clear all filters from table or column |
| REMOVEFILTERS | Explicitly clear filters from specified table/column |
| ALLEXCEPT | Clear all filters except specified columns |
| KEEPFILTERS | Intersect new filter with existing filter context |
| ALLSELECTED | Respect visual slicers while ignoring row grouping filters |
// Sales Share % (City Contribution to Total Sales)
Sales Share % = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(Sales[City])), 0)
🔍 REMOVEFILTERS Behavior
15
Row Context vs Filter Context vs Context Transition
| Concept | Example | Explanation |
|---|---|---|
| Row context | Profit = Sales[Sales] - Sales[Cost] | Knows current row values automatically during iteration |
| Filter context | Total Sales = SUM(Sales[Sales]) | Active slicers, report filters, and visual rows filtering data |
| Context transition | CALCULATE() inside row context | Converts current row context into equivalent filter context |
City Filter Context Breakdown Table
| Selection / City | Total Sales Output |
|---|---|
| No city filter (Overall) | ₹2,87,000 |
| Mohali | ₹1,30,000 |
| Chandigarh | ₹97,500 |
| Ludhiana | ₹54,500 |
| Ropar | ₹5,000 |
16
VAR and RETURN & Virtual Tables
// Variables for readability & performance
Profit Margin % =
VAR Revenue = [Total Sales]
VAR ProfitValue = [Total Profit]
RETURN DIVIDE(ProfitValue, Revenue, 0)
| Virtual Table Function | Purpose / Kaam |
|---|---|
| VALUES / DISTINCT | Return distinct single-column table |
| ADDCOLUMNS | Add calculated columns to a table expression |
| SUMMARIZE | Create a grouped summary table (like SQL GROUP BY) |
| SELECTCOLUMNS | Return selected or renamed columns |
| TOPN | Return top N rows based on expression |
| CROSSJOIN | Create Cartesian product combinations between tables |
| UNION / INTERSECT / EXCEPT | Combine or compare table rows |
Product Summary = SUMMARIZE(Sales, Sales[Product], "Product Sales", SUM(Sales[Sales]))