DAX FUNCTIONS & INTERVIEW GUIDE
Power BI | Data Analysis Expressions | Complete Reference
PART 1: DAX FUNCTION REFERENCE
1. Aggregation Functions
SUM(column) SUMX(table, expression)
AVERAGE(column) AVERAGEX(table,
expression)
MIN(column) MAX(column)
MINX(table, expression) MAXX(table, expression)
COUNT(column) COUNTA(column) COUNTROWS(table)
DISTINCTCOUNT(column)
2. Mathematical Functions
ABS(number) DIVIDE(num, denom, alt)
ROUND(number, digits) ROUNDUP(number, digits) ROUNDDOWN(number, digits)
INT(number) MOD(number, divisor) POWER(number, power)
SQRT(number)
3. Logical Functions
IF(test, true, false) SWITCH(expr, val1, res1,
...)
AND(logical1, logical2) OR(logical1, logical2) NOT(logical)
IFERROR(value, if_error)
4. Filter & Context Functions
CALCULATE(expr, filter1, FILTER(table,
...) condition)
ALL(table_or_column) ALLEXCEPT(table, col1, ALLSELECTED(table_or_col)
...)
REMOVEFILTERS(table_or_col) KEEPFILTERS(filter)
VALUES(column) SELECTEDVALUE(column,
alt)
HASONEVALUE(column) ISFILTERED(column) ISCROSSFILTERED(column)
5. Table Functions
ADDCOLUMNS(table, name, SUMMARIZE(table, col1,
expr) ..., name, expr)
SUMMARIZECOLUMNS(col1, GROUPBY(table, col, name,
...) expr)
DISTINCT(column) UNION(table1, table2)
INTERSECT(table1, table2) EXCEPT(table1, table2)
6. Time Intelligence Functions
DATE(year, month, day) TODAY() NOW()
DATESYTD(date_col) DATESMTD(date_col) DATESQTD(date_col)
TOTALYTD(expr, date_col) TOTALMTD(expr, TOTALQTD(expr, date_col)
date_col)
SAMEPERIODLASTYEAR(date_col) DATEADD(date_col, n, PARALLELPERIOD(date_col,
interval) n, interval)
STARTOFMONTH(date_col) ENDOFMONTH(date_col)
STARTOFYEAR(date_col) ENDOFYEAR(date_col)
7. Ranking & Window Functions
RANKX(table, expr, value, TOPN(n, table, orderBy, EARLIER(column)
order, ties) order)
8. Relationship Functions
RELATED(column) RELATEDTABLE(table)
USERELATIONSHIP(col1, CROSSFILTER(col1, col2,
col2) dir)
9. Text Functions
CONCATENATE(text1, text2) CONCATENATEX(table, expr,
delim)
LEFT(text, n) RIGHT(text, n) MID(text, start, n)
LEN(text) UPPER(text) LOWER(text)
TRIM(text) SEARCH(find, within,
start, not_found)
10. Information Functions
ISBLANK(value) ISNUMBER(value) ISTEXT(value)
BLANK() FORMAT(value,
format_string)
PART 2: DAX INTERVIEW Q&A
Core Difference Questions
Q1: SUM() vs SUMX()
SUM() SUMX()
Aggregates a single column directly Iterator — evaluates expression row by row
Works only on a numeric column Can use expressions (multiply, divide, logic per
row)
No row-by-row calculation Loops through every row before summing
Faster performance Slightly slower due to row iteration
Example: SUM(Sales[Amount]) Example: SUMX(Sales, Sales[Qty] *
Sales[Price])
Q2: AVERAGE() vs AVERAGEX()
Same concept as SUM vs SUMX:
• AVERAGE(column) → Direct column average, faster
• AVERAGEX(table, expression) → Row-by-row calculated average, flexible
Q3: COUNT() vs COUNTA() vs COUNTROWS()
Function Behavior
COUNT(column) Counts only numeric values; ignores blanks
COUNTA(column) Counts all non-blank values (text + numbers)
COUNTROWS(table) Counts total rows in a table — preferred in
measures
Q4: DISTINCT() vs VALUES()
DISTINCT() VALUES()
Returns unique values Returns unique values
Does NOT return a blank row automatically Can return a blank row if relationship issues exist
Not context-aware Context-aware — more powerful in measures
Q5: ALL() vs REMOVEFILTERS()
ALL() REMOVEFILTERS()
Removes filters AND returns a table Only removes filters (no table returned)
Can affect totals behavior unexpectedly Cleaner and more explicit for filter removal
Older approach Recommended in modern DAX
Q6: ALL() vs ALLEXCEPT()
ALL() removes all filters from the table/column.
ALLEXCEPT(table, col1, col2) removes all filters EXCEPT the specified columns.
→ Used in percentage-of-total calculations.
Q7: ALL() vs ALLSELECTED()
ALL() ALLSELECTED()
Ignores ALL filters including slicers Respects slicer selections
Completely removes context Ignores only visual-level filters
Use for grand total context Use for dynamic % calculations within slicer
selection
Q8: CALCULATE() vs CALCULATETABLE()
CALCULATE() CALCULATETABLE()
Returns a scalar value Returns a table
Modifies filter context for a single value Used when a virtual table is needed
Used in measures Used inside table functions
Q9: FILTER() inside CALCULATE() vs Direct Filter
Direct Filter (faster) FILTER() (flexible)
Evaluated at the column/value level Evaluated row by row — more flexible
Works for simple equality conditions Needed for complex logic and expressions
Always prefer this when possible Slower on large datasets — use only when
required
→ Interview Tip: Avoid FILTER() unless the condition cannot be expressed as a direct filter.
Q10: HASONEVALUE() vs ISFILTERED()
HASONEVALUE(column) ISFILTERED(column)
Returns TRUE if exactly one value in context Returns TRUE if column has any filter applied
Checks for single selection Checks if filtering exists at all
Context Questions
Q1: What is Row Context?
• Exists in calculated columns and iterator functions (SUMX, AVERAGEX, etc.)
• Processes one row at a time — each row knows its own values
• Created automatically in calculated columns
Q2: What is Filter Context?
• Filters applied from slicers, visuals, page filters, and CALCULATE()
• Determines what subset of data is visible to a measure
• Changes dynamically based on user interactions
Q3: What is Context Transition?
• When row context is automatically converted into filter context
• Happens when CALCULATE() is used inside an iterator (like SUMX)
• Critical concept for understanding how measures behave inside calculated columns
Q4: Calculated Column vs Measure
Calculated Column Measure
Stored in memory (increases model size) Calculated at query runtime (dynamic)
Calculated row by row — uses row context Responds to filter context from visuals/slicers
Static — does not respond to filters Efficient — does not bloat the model
Use for row-level attributes Use for aggregations and KPIs
→ Always prefer Measures over Calculated Columns when possible.
Time Intelligence Questions
Q1: DATEADD() vs SAMEPERIODLASTYEAR()
DATEADD() SAMEPERIODLASTYEAR()
Flexible — can shift by day, month, quarter, or Fixed — always shifts exactly 1 year back
year
DATEADD(Dates[Date], -1, YEAR) SAMEPERIODLASTYEAR(Dates[Date])
More versatile for custom periods Simpler syntax for year-over-year comparison
Q2: TOTALYTD() vs DATESYTD()
TOTALYTD() DATESYTD()
Returns an aggregated scalar value (e.g., sum) Returns a date table for YTD range
Used directly in measures Used inside CALCULATE() for custom
aggregations
TOTALYTD(SUM(Sales[Amt]), Dates[Date]) CALCULATE(SUM(Sales[Amt]),
DATESYTD(Dates[Date]))
Q3: Why is a Date Table Mandatory?
• Time intelligence functions require a continuous, unbroken date sequence
• The table must be marked as a Date Table in Power BI
• Must have a proper relationship to the fact table's date column
• Without it, functions like TOTALYTD, SAMEPERIODLASTYEAR will not work correctly
Relationship & Model Questions
Q1: RELATED() vs LOOKUPVALUE()
RELATED() LOOKUPVALUE()
Requires an existing relationship between tables Works WITHOUT a relationship
Faster — uses the model's relationship engine Slower — scans the lookup table
Used in calculated columns on the many side Used when no relationship exists
Q2: USERELATIONSHIP()
• Activates an inactive relationship inside CALCULATE()
• Useful when multiple relationships exist between two tables (e.g., Order Date vs Ship Date)
CALCULATE(SUM(Sales[Amt]), USERELATIONSHIP(Sales[ShipDate], Dates[Date]))
Iterator & Advanced Questions
Q1: What are Iterator Functions?
• Functions ending in X: SUMX, AVERAGEX, MAXX, MINX, RANKX, COUNTX
• They iterate row by row over a table, evaluating an expression for each row
• Then aggregate all row-level results into a single value
Q2: What is EARLIER()?
• References the outer row context in a nested calculated column scenario
• Mostly replaced by VAR (variables) in modern DAX — more readable
VAR CurrentSales = Sales[Amount] -- modern replacement for EARLIER
Q3: What is a Virtual Table in DAX?
• A temporary, in-memory table created inside a measure — not stored in the model
• Created using: SUMMARIZE, ADDCOLUMNS, FILTER, CALCULATETABLE
• Used to perform intermediate calculations before returning a final scalar value
Performance & Optimization Questions
Q1: Why is FILTER() sometimes slow?
• FILTER() scans the entire table row by row — an O(n) operation
• On large tables, this creates significant query overhead
• Use direct filter arguments in CALCULATE() instead whenever possible
Q2: Why avoid Calculated Columns in large datasets?
• Calculated columns are materialized — stored in memory (VertiPaq compression)
• They increase model file size and memory footprint
• They are NOT dynamic — do not respond to filter context at runtime
• Measures are computed on-demand and are far more memory efficient
Q3: Import Mode vs DirectQuery — DAX Behavior
Import Mode DirectQuery
Data is loaded into memory (VertiPaq engine) Queries go directly to the source database
Fastest performance for DAX measures Slower — every interaction queries the source
Full DAX function support Limited DAX support (no time intelligence in
some sources)
Data is refreshed on schedule Always shows live/current data
DAX Quick Reference Card
Power BI Interview Preparation Guide