MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
DAX Basics (Calculated Columns, Measures, SUMX, CALCULATE)0%
DAX Basics (Calculated Columns, Measures, SUMX, CALCULATE)
Beginner12 min read•Updated: Jul 11, 2026
DAX Basics (Calculated Columns, Measures, SUMX, CALCULATE)
DAX (Data Analysis Expressions) is the formula language of Power Pivot and Microsoft Power BI. While DAX resembles standard Excel formulas, it operates on a fundamentally different paradigm based on tables, columns, row context, and filter context.
1. Calculated Columns vs. DAX Measures
Visual Architecture Blueprint
+-----------------------------------+-----------------------------------+ | Calculated Column | Explicit DAX Measure | +-----------------------------------+-----------------------------------+ | Evaluated row-by-row during data | Evaluated on the fly inside the | | load / refresh. | Pivot Table based on filters. | | Consumes valuable RAM memory. | Consumes zero RAM when idle. | | Visible in the table grid. | Lives in the Calculation Area. | | Example: =[Price] * [Quantity] | Example: =SUM(Sales[Revenue]) | +-----------------------------------+-----------------------------------+
Best Practice Rule: Whenever possible, use Measures instead of Calculated Columns. Measures keep your file sizes tiny and respond dynamically to user slicers and filters!
Click to Zoom2. Essential Aggregations: SUM vs. SUMX (Iterators)
- Standard Aggregator (SUM): Adds up a single physical column:
Total Sales := SUM(FactSales[Revenue])
- Iterator Function (SUMX): What if your table has 'Quantity' and 'UnitPrice', but no 'Revenue' column? In standard Excel, you’d create a new column. In DAX, SUMX calculates row-by-row in memory and sums the results without wasting RAM:
Total Revenue := SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice])
(Iterates through FactSales, multiplies Quantity by Price for each row, and sums the total!)
3. The King of DAX: The CALCULATE Function
CALCULATE is the single most powerful function in business intelligence. It evaluates an expression while modifying or overriding the current filter context:
Syntax:
CALCULATE(Expression, Filter1, [Filter2], ...)
Scenario A: Calculating Sales for a Specific Category
Tech Sales := CALCULATE([Total Sales], DimProducts[Category] = "Technology")
Even if a user slices the table by "Furniture", this measure evaluates strictly for "Technology"!
Scenario B: Percentage of All Sales (Overriding Filters with ALL)
To calculate an employee's contribution to total nationwide sales, you must divide their sales by the grand total, ignoring employee filters:
All Sales := CALCULATE([Total Sales], ALL(FactSales))
Sales Contribution % := DIVIDE([Total Sales], [All Sales], 0)
(The ALL() function strips away all filters, providing the true denominator grand total! The DIVIDE() function handles division by zero gracefully).
Interactive Quiz
Multiple Choice Questions
Question 1
What is the key performance advantage of creating an Explicit DAX Measure over a Calculated Column?
Question 2
Which DAX function iterates through a table row-by-row, executes an expression, and sums the grand total in memory?
Question 3
What does the CALCULATE function do in DAX?
Question 4
What does the DAX expression '=CALCULATE([Total Sales], ALL(DimProducts))' return?
Question 5
Why is the '=DIVIDE(Numerator, Denominator, [AlternateResult])' function preferred over the standard '/' forward slash operator in DAX?
Next Lesson
Developer Tab & Macro Security Settings
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
Related Lessons
| Previous Lesson | Next Lesson |
|---|---|
| Using Relationships (Star Schema & Cardinality) | Developer Tab & Macro Security Settings |
Practice Quiz
Test your understanding of this lesson with 5 questions. Each question has one correct answer.