DAX Fundamentals: Measures, CALCULATE, Filter Context | Excel & Power BI S2 Ep4

📂 Download the AtlasParts dataset and the Episode 10 Power BI starter file: https://github.com/GoCelesteAI/excel-powerbi-for-finance Episode 4 of Season 2 of *Excel & Power BI for Finance* — DAX fundamentals. Episode 9 left us with the AtlasParts star schema fully drawn. Now we write the language that brings it to life. DAX looks like Excel formulas at first glance, and that resemblance is helpful for the first ten minutes. After that, filter context shows up, and DAX starts behaving in ways Excel never did. This episode gets the foundation right: calculated column versus measure, your first measures on the journal, filter context explained, and why CALCULATE is the most important function in the language. SUMX and VAR/RETURN round it out. What You'll Learn: - Calculated columns vs measures — same formula bar, completely different evaluation timing. Columns run once at refresh and live in Vertipaq. Measures run every time a visual asks, under the user's filter context. Finance models lean on measures. - Your first measure — Total Debits = SUM(gl_journal[debit_amount]). One line. Drop it on a card, drop a region slicer next to it, watch the number react. The filter context does the work. - Filter context — the concept that makes DAX hard. The combined set of filters every measure runs under. The star schema from Episode 9 is what makes the filter actually reach the fact. - CALCULATE — the function for modifying filter context. Add filters, strip them with ALL, do percent-of-total. The most-used function in DAX past basic sums. - SUM vs SUMX — when the column you want to sum doesn't exist yet, SUMX is the row-wise iterator. The X-suffix family exists for exactly this pattern. - VAR / RETURN — the readability win. Variables evaluate once even when referenced multiple times. RETURN a VAR to debug intermediate values directly in your visuals. Timestamps: 0:00 - Intro — DAX fundamentals 0:42 - Five ideas to land today 1:28 - Calculated column vs measure 2:39 - Your first measure — Total Debits + slicer reactivity 3:52 - Filter context — the concept that makes DAX hard 5:30 - CALCULATE — modify filter context 7:14 - SUM vs SUMX — the iterator pattern 9:10 - VAR / RETURN — keep complex measures readable 10:39 - Recap — four working measures 11:39 - Up next — Time Intelligence Key Takeaways: 1. The decision between a calculated column and a measure is one of timing. Columns evaluate once at refresh and live in storage; measures evaluate at query time under the user's filter context. If the value depends on what the user has clicked — slicer, date range, anything — make it a measure. Almost every interesting finance value depends on context, so finance models lean on measures. 2. Filter context is the combination of every filter applied to a measure at the moment it runs. Slicer clicks, matrix rows and columns, cross-filtering from another visual, page filters, report filters — all combine into one filter context. DAX narrows every Table to the matching rows, then runs the arithmetic on what remains. That's why the same measure shows different numbers in different visuals. 3. CALCULATE is the function for modifying filter context. Its first arg is an expression; subsequent args are filters to add, remove, or replace. The two patterns most measures need: add a filter (CALCULATE([Total Revenue], customers[is_new] = "Y")) or strip one (CALCULATE([Total Revenue], ALL(customers))). Most non-trivial measures involve a CALCULATE somewhere. 4. SUMX — and the X-suffix iterator family — exists for the case where the column you want to sum doesn't exist yet. Quantity times unit price, computed row-by-row, summed across the fact, stored nowhere. The classic rookie mistake is using SUM(quantity * unit_price), which errors out because SUM takes a column reference, not an expression. Use SUMX for row-wise calculation. 5. VAR / RETURN keeps measures readable past three lines. Variables evaluate once even when referenced multiple times, so the savings are real. They also help debugging — RETURN a VAR instead of the final expression to see its intermediate value in your visuals. Use VAR freely; any measure with more than three lines is clearer with it. #PowerBI #DAX #FilterContext #CALCULATE #FinanceAnalytics #PowerBIDesktop #ExcelToPowerBI #DataModeling #FinancialReporting #BusinessIntelligence --- Generated by GoCelesteAI · part of the Excel & Power BI for Finance series