“Power BI DAX Function Differences: The
Ultimate Comparison Guide for
Interviews”
Difference between SUM and SUMX in Power BI:
Feature SUM SUMX
Function
Aggregator Iterator (Row-by-row calculation)
Type
Syntax SUM(column) SUMX(table, expression)
Adds up values from a single Evaluates a custom expression row by row
Use Case
column and then sums the result
Limited to direct column Allows complex calculations per row before
Flexibility
summation summing
Example SUM(Sales[Amount]) SUMX(Sales, Sales[Quantity] * Sales[Price])
Quick Explanation:
• SUM: Use it when you just need the total of a numeric column.
• SUMX: Use it when you need to calculate something for each row first, and then
add it all up.
Difference between CALCULATE() and CALCULATETABLE() in
Power BI
CALCULATE() vs CALCULATETABLE() in Power BI DAX
Feature CALCULATE() CALCULATETABLE()
Returns A single value (scalar) A table
Function Used for modifying filter context for Used for modifying filter context to
Type expressions return a table
Creating filtered tables for use in
Common Measures (e.g., total sales, average,
other functions like COUNTROWS,
Usage counts under filter conditions)
SUMX
Syntax CALCULATE(expression, filters...) CALCULATETABLE(table, filters...)
CALCULATE(SUM(Sales[Amount]), CALCULATETABLE(Sales,
Example
Region = "East") Sales[Region] = "East")
For use in table expressions (e.g.,
Return For use in measure or scalar
virtual relationships, dynamic
Use Case expressions
tables)
Context Changes filter context for an expression Changes filter context and returns
Behavior result a table result
Quick Summary:
• Use CALCULATE() when you're working with measures or need a
numeric/scalar result.
• Use CALCULATETABLE() when you want to return a filtered table, usually
inside table functions like SUMX, FILTER, VALUES, or COUNTROWS.
Difference between ALL, ALLSELECTED, and ALLEXCEPT in
Power BI :
ALL vs ALLSELECTED vs ALLEXCEPT in DAX
Featur
ALL() ALLSELECTED() ALLEXCEPT()
e
Removes all filters from Removes filters except Removes all filters except
Purpo
the specified column(s) or those applied by visuals those on specified
se
table like slicers or cross-filters columns
Retains filters from visuals Ignores filters except for
Ignores all filters – slicers,
Scope (like slicers), removes the columns explicitly
visuals, manual filters
others mentioned
A table that respects
A table based on selected
Retur A table with all rows, filters on specified
rows in visuals (e.g.,
ns ignoring current filters columns and removes
slicers)
others
Used to get values based Used when keeping
Use Used to calculate grand
on user selections context for some columns
Case totals or remove context
(especially in visuals) while ignoring others
CALCULATE(SUM(Sales[A
CALCULATE(SUM(Sales[A
Exam CALCULATE(SUM(Sales[A mount]),
mount]),
ple mount]), ALL(Sales)) ALLEXCEPT(Sales,
ALLSELECTED(Sales))
Sales[Region]))
Practical Explanation:
• ALL(): Ignores everything—great for total rows and comparison.
• ALLSELECTED(): Keeps user visual selections (like slicers), ignores everything
else.
• ALLEXCEPT(): Keeps filters on certain columns, removes others—useful for
grouping or subtotal logic.
Differene between FILTER, KEEPFILTERS, and REMOVEFILTERS
in Power BI :
FILTER vs KEEPFILTERS vs REMOVEFILTERS in DAX
Feature FILTER() KEEPFILTERS() REMOVEFILTERS()
Returns a
filtered table Preserves existing filters and Removes filters from one or
Purpose
based on a adds new filters on top more columns/tables
condition
Table
function
Functio Modifier function used inside Modifier function used inside
(used inside
n Type CALCULATE() CALCULATE()
CALCULATE,
SUMX, etc.)
Creates a
new row
Affects Keeps existing filter context and Clears filter context for
context →
Filters restricts it further specified columns/tables
applies
logical filter
A scalar value (when used
A filtered A scalar value (typically) after
Returns inside a measure) with
table removing filters
preserved filters
Inside
CALCULATE, Inside CALCULATE when you
Commo Inside CALCULATE to compute
SUMX, or want to respect external filters
n Usage values ignoring filters
table + apply new
expressions
CALCULATE(SUM(Sales[Amoun
FILTER(Sales, CALCULATE(SUM(Sales[Amoun
Exampl t]),
Sales[Amoun t]), KEEPFILTERS(Sales[Region]
e REMOVEFILTERS(Sales[Region])
t] > 1000) = "East"))
)
Explanation with Real-Life Use:
Scenario Use Function
You want to manually filter rows using a logical condition → FILTER()
You want to add filters without overriding slicers → KEEPFILTERS()
You want to ignore filters from visuals/slicers on a column/table → REMOVEFILTERS()
Quick Summary:
• FILTER: Returns a filtered table – used inside table expressions.
• KEEPFILTERS: Adds filters without removing existing ones – preserves slicers.
• REMOVEFILTERS: Clears filter context – ignores slicers/filters.
Difference between TOTALMTD, TOTALQTD, and TOTALYTD in
Power BI DAX:
TOTALMTD vs TOTALQTD vs TOTALYTD in Power BI DAX
Feature TOTALMTD() TOTALQTD() TOTALYTD()
Full Form Total Month-To-Date Total Quarter-To-Date Total Year-To-Date
Calculates cumulative Calculates cumulative Calculates cumulative
Purpose value from start of value from start of value from start of year
month to date quarter to date to date
Monthly cumulative Quarterly cumulative Yearly cumulative
Used For
calculations calculations calculations
TOTALYTD(<expression>,
TOTALMTD(<expression>, TOTALQTD(<expression>, <dates>,
Syntax
<dates>, [<filter>]) <dates>, [<filter>]) [<year_end_date>],
[<filter>])
Feature TOTALMTD() TOTALQTD() TOTALYTD()
TOTALMTD(SUM(Sales[A TOTALQTD(SUM(Sales[A TOTALYTD(SUM(Sales[A
Example
mount]), Date[Date]) mount]), Date[Date]) mount]), Date[Date])
Calendar
Works based on a
Depende Same Same
continuous date column
ncy
Explanation:
• TOTALMTD: Accumulates values from 1st of the month up to the current date.
• TOTALQTD: Accumulates values from 1st day of the quarter up to the current
date.
• TOTALYTD: Accumulates values from 1st Jan (or custom year start) up to the
current date.
Quick Tip:
Ensure your Date table is marked as a Date Table and has continuous dates for these
functions to work correctly.
