What DAX is and how filter context works
Want to learn DAX on real Indian business datasets with a mentor? The Data Analytics course in Kerala covers Power BI, DAX, SQL and Python across three capstone projects.
Quick answer: DAX (Data Analysis Expressions) is the formula language of Power BI, Power Pivot and Analysis Services. A DAX measure is evaluated inside a filter context: the set of rows left after the slicers, the visual's axes and any filters in the formula itself have been applied. Understanding that context is what makes DAX click.
In Excel a formula points at cells. In DAX a formula points at columns and tables, and the same measure returns different numbers in different places on the report because the filters around it differ. Total Sales in a card shows the grand total; the same measure in a matrix by region shows each region's total; with a year slicer it shows that year only. You never wrote a region or year filter; the visual supplied it. CALCULATE is the function that lets you change those filters from inside the formula, which is why it matters so much.
All examples below use a simple model: a Sales table (OrderDate, CustomerKey, ProductKey, Quantity, UnitPrice, Cost), a Product table (ProductKey, Category, Brand), a Customer table (CustomerKey, City, State) and a Date table marked as the date table.
DAX measures vs calculated columns: which to use
Quick answer: Use a measure when the result should respond to slicers and visuals (totals, ratios, year-to-date). Use a calculated column when you need a value stored per row to slice, group or relate by (a price band, a full name, a fiscal year). Measures are calculated at query time and keep the model small; calculated columns are calculated at refresh and consume memory.
Beginners reach for calculated columns because they look like Excel. The habit costs performance and produces wrong totals: a calculated column Profit = Sales[Revenue] - Sales[Cost] is fine per row, but a column for Margin percent cannot be summed meaningfully, while a measure Margin % = DIVIDE([Total Profit], [Total Sales]) is correct at every level. Rule of thumb: if it is a number you would put in a card or a chart, make it a measure; if it is a label you would put in a slicer or an axis, make it a column, and prefer to create it in Power Query if it does not need DAX.
Aggregation functions: SUM, AVERAGE, COUNTROWS, DISTINCTCOUNT
Quick answer: SUM adds a column, AVERAGE averages it, COUNTROWS counts the rows of a table in the current filter context, and DISTINCTCOUNT counts unique values in a column. These four build the base measures every other formula references.
1. SUM
SUM(<column>)
Adds all values in a numeric column within the current filter context.
Example: Total Quantity = SUM(Sales[Quantity])
2. AVERAGE
AVERAGE(<column>)
Arithmetic mean of a numeric column, ignoring blanks.
Example: Avg Unit Price = AVERAGE(Sales[UnitPrice])
3. COUNTROWS
COUNTROWS(<table>)
Counts the rows of a table or table expression. Prefer it over COUNT for counting transactions.
Example: Order Lines = COUNTROWS(Sales)
4. DISTINCTCOUNT
DISTINCTCOUNT(<column>)
Counts unique values, for example customers who bought in the period.
Example: Active Customers = DISTINCTCOUNT(Sales[CustomerKey])
Name base measures clearly (Total Sales, Total Cost) and build everything else from them. A measure that references another measure keeps the logic in one place; when the definition of sales changes, you change it once.
Iterators: SUMX and the X functions
Quick answer: SUMX evaluates an expression row by row over a table and then sums the results. Use it when the value to add does not exist as a column, such as Quantity times UnitPrice. AVERAGEX, MAXX, MINX and COUNTX work the same way.
5. SUMX
SUMX(<table>, <expression>)
Iterates the table in the current filter context, evaluates the expression for each row, and adds up the results.
Example: Total Sales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
SUMX is why you rarely need a Revenue calculated column. It also introduces row context: inside the expression, Sales[Quantity] means the value in the current row of the iteration. Mixing row context and filter context is where most DAX confusion lives, and SUMX is the gentlest place to learn it.
CALCULATE, FILTER and ALL: changing filter context
Quick answer: CALCULATE evaluates a measure under modified filters. Simple filters (Product[Category] = "SAP Books") add or replace a filter on one column; FILTER builds a filtered table for complex conditions; ALL removes filters so you can compute shares and grand totals. CALCULATE is the most important function in DAX.
6. CALCULATE
CALCULATE(<expression>, <filter1>, <filter2>, ...)
Changes the filter context, then evaluates the expression. Column filters in the argument replace any existing filter on that column.
Example: Kochi Sales = CALCULATE([Total Sales], Customer[City] = "Kochi")
7. FILTER
FILTER(<table>, <condition>)
Returns a table of rows that meet a condition, evaluated row by row. Used inside CALCULATE when the condition involves a measure or several columns.
Example: Large Order Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[Quantity] * Sales[UnitPrice] > 50000))
8. ALL
ALL(<table or column>)
Removes filters from a table or column. Combine with CALCULATE to get a grand total for percentage-of-total measures. ALLSELECTED respects slicers but ignores the visual's own filters; REMOVEFILTERS is the readable modern alias for ALL as a filter modifier.
Example: Sales % of All Cities = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Customer[City])))
Two habits save hours. Keep the simple column filter form whenever you can; FILTER over a large fact table is slow. And read a CALCULATE from the inside out: first the filters change, then the measure is evaluated.
You can see how DAX is taught alongside SQL and Python on the Cokonet Academy website, and compare the analytics track with Power BI and Excel options on our all courses page.
DIVIDE, IF and SWITCH: safe ratios and logic
Quick answer: DIVIDE returns a safe result when the denominator is zero or blank, so use it for every ratio. IF handles one condition; SWITCH handles many without nesting. Together they cover margins, growth flags and bands.
9. DIVIDE
DIVIDE(<numerator>, <denominator>, [alternate result])
Division that returns blank (or the alternate result) instead of an error when the denominator is zero.
Example: Margin % = DIVIDE([Total Profit], [Total Sales])
10. IF
IF(<condition>, <result if true>, [result if false])
Returns one of two values based on a condition.
Example: Growth Flag = IF([Sales YoY %] > 0, "Up", "Down")
11. SWITCH
SWITCH(<expression>, <value1>, <result1>, ..., [else])
Compares an expression against a list of values and returns the matching result. SWITCH(TRUE(), ...) replaces long nested IFs for bands.
Example: Order Size = SWITCH(TRUE(), [Total Sales] >= 100000, "Large", [Total Sales] >= 25000, "Medium", "Small")
RELATED and RELATEDTABLE: working across relationships
Quick answer: RELATED pulls a value from the one side of a relationship into a row on the many side (Product category onto a sales row). RELATEDTABLE goes the other way, returning the related rows from the many side for a row on the one side (all sales of a product). Both need a relationship in the model.
12. RELATED
RELATED(<column>)
Fetches a single value from a related table, used in calculated columns and inside iterators.
Example: Category Sales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice] * IF(RELATED(Product[Category]) = "Electronics", 1, 0))
13. RELATEDTABLE
RELATEDTABLE(<table>)
Returns the rows of a related table that match the current row, used in calculated columns on dimension tables.
Example: Orders per Product = COUNTROWS(RELATEDTABLE(Sales)) -- as a calculated column on Product
If RELATED is greyed out, the relationship is missing or points the wrong way. Fix the model before writing more DAX; good relationships remove most LOOKUPVALUE formulas beginners write.
Time intelligence DAX: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD
Quick answer: Time intelligence functions compare periods: TOTALYTD accumulates from the start of the year, SAMEPERIODLASTYEAR shifts the current filter back one year, DATEADD shifts by any number of days, months, quarters or years. They need a proper Date table marked as the date table, with one row per day and no gaps.
14. TOTALYTD
TOTALYTD(<expression>, <dates>, [filter], [year end date])
Year-to-date total of a measure. Pass "31/3" as the year end date for an Indian financial year.
Example: Sales FYTD = TOTALYTD([Total Sales], 'Date'[Date], "31/3")
15. SAMEPERIODLASTYEAR and DATEADD
SAMEPERIODLASTYEAR(<dates>) and DATEADD(<dates>, <number>, <interval>)
Return a shifted set of dates to use as a CALCULATE filter for prior-period comparisons.
Example: Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Sales YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])
Sales Prev Month = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH))
Most time intelligence problems are date table problems. Build the table with CALENDARAUTO or in Power Query, add Year, Quarter, Month and Fiscal Year columns, mark it as the date table, and relate it to every fact table. Then the functions above work in any visual.
Stuck on a DAX measure in your own project? Book a free consultation or call +91 8075 400 500; our mentors review learner dashboards every week.
How to practise DAX and where it takes your career
Quick answer: Build one sales dashboard with ten measures: Total Sales, Total Cost, Profit, Margin %, Active Customers, Sales YTD, Sales LY, YoY %, Sales % of Total and Average Order Value. Publish it, then rebuild it from memory a week later. That cycle, repeated on two more datasets, is enough DAX for a fresher data analyst interview.
Employers in Kochi, Trivandrum and Bengaluru test DAX in a short practical: here is a model, build a YoY comparison and a share-of-total visual. The fifteen functions above cover almost everything such a test asks. Public salary ranges for fresher data analysts in India sit around Rs 3 to 5 lakh a year, with Power BI skill and PL-300 certification helping candidates move toward the upper end and into Bengaluru product roles.
Next reads: our Power BI certification guide for PL-300, data analyst interview questions for freshers, and how to write a data analyst resume that gets the practical test in the first place.
Why Cokonet Academy for Power BI and data analytics training
Cokonet Academy is an NSDC partner training institute with campuses in Trivandrum and Kochi. Find us on Google: Cokonet Academy, Trivandrum and Cokonet Academy, Kochi.
- NSDC partner training institute with campuses in Trivandrum and Kochi, plus a Bengaluru centre and online-live batches
- 11,200+ successful placements and 50+ corporate hiring partners
- Expert trainers in SAP (FICO, MM, ABAP), AWS, Python with AI, Data Analytics and Power BI
- Placement support until you are hired, with mock interviews and resume reviews
The Data Analytics course teaches DAX on real Indian business datasets, builds three published capstone dashboards per learner and prepares you for the Microsoft PL-300 path, with resume and mock interview support.
Course details
- Course: Data Analytics Course (Excel, SQL, Power BI, Python), Cokonet Academy
- Duration: about 4 months, 204+ hours across 10 modules with 3 capstones
- Mode: classroom in Trivandrum, Kochi and Bengaluru, or online-live across Kerala and the Gulf; English with Malayalam support
- Covers: Excel and Power Query, SQL, Power BI data modelling and DAX, Python with pandas, statistics, storytelling with data and AI tools for analysts
- Certification path: Microsoft PL-300; Cokonet completion certificate included
- Placement: resume and LinkedIn rebuild, mock interviews and support until you are hired
Syllabus, batch dates and trainer profiles: Data Analytics course in Kerala.
Visit our training centres
Book a free consultation and sit in on a live session before you decide. We are open Monday to Saturday, 9:30am to 10:00pm.
Enrol now
Write DAX with confidence on real projects. Join the Data Analytics course, visit Cokonet Academy, or call and WhatsApp +91 8075 400 500.
