DAX

DAX (Data Analysis Expressions) is a formula language used in Power BI,

Excel Power Pivot, and SQL Server Analysis Services (SSAS) to perform
calculations and data analysis on data models.

It is similar to Excel formulas but is designed to work with relational

data and create powerful measures, calculated columns, and tables.

Why DAX is Used

 To perform calculations (totals, averages, ratios) on data models.

 To create measures, calculated columns, and tables in Power BI.
 To handle complex business logic that Excel formulas cannot
manage.
 To work with large datasets efficiently.
 To build interactive reports and dashboards with dynamic results.
How DAX Works in Power BI?

1. Works on Tables and Columns

o DAX always works with tables and columns (not single cells
like Excel).
o Example: SUM(Sales[Amount]) → adds up all values in the
Amount column.

2. Row Context (Row by Row Calculation)

o DAX can calculate row by row inside a table.
o Example: Sales[Total] = Sales[Qty] * Sales[Price] → creates a
new column for each row.

3. Filter Context (Based on Report Filters)

o DAX understands filters in your report (like Year, Region, and
Product).
o Example: Total Sales = SUM(Sales[Amount])
 If you select 2024 → it shows only 2024 sales.
 If you select India → it shows only India sales.

4. Calculated Columns vs Measures

o Calculated Column → adds a new column inside the table
(row-by-row calculation).
 o Measure → does not add a column, but calculates on the fly
based on filters in the report.

5. Uses Functions like Excel but More Powerful

o DAX has functions like SUM, AVERAGE, COUNT, IF, etc.
o It also has special ones like CALCULATE, FILTER, TOTALYTD
for business logic and time intelligence.

Example Dataset (Sales Table)

SaleID Product Qty Price Region Customer

1 A 2 100 India C1

2 B 1 200 India C2

3 A 3 100 USA C1

4 C 5 50 USA C3

5 B 2 200 India C4

Revenue = Qty × Price

DAX Aggregation Functions — Definition, Syntax, Example & Output

Below each function you’ll find Definition · Syntax · Example · Output ·

Notes in a student-friendly format.
1. SUM

 Definition: Adds all numbers in a column.

 Syntax: SUM(<column>)
 Example: SUM(Sales[Price])
 Output: 100 + 200 + 100 + 50 + 200 = 650
 Notes: Only works on numeric columns.

2. SUMX

 Definition: Evaluates an expression for each row of a table and

then adds the results.
 Syntax: SUMX(<table>, <expression>)
 Example: SUMX(Sales, Sales[Qty] * Sales[Price])
 Output: 200 + 200 + 300 + 250 + 400 = 1350
 Notes: Use when you need row-by-row calculation before
aggregation.

3. AVERAGE

 Definition: Returns the arithmetic mean of numbers in a column

(ignores blanks & text).
 Syntax: AVERAGE(<column>)
 Example: AVERAGE(Sales[Price])
 Output: (100 + 200 + 100 + 50 + 200) / 5 = 130
 Notes: Non-numeric values are ignored.

4. AVERAGEA

 Definition: Averages values after converting types (text → 0, TRUE

→ 1, FALSE → 0; blanks ignored).
 Syntax: AVERAGEA(<column>)
 Example: Column = {100, "abc", TRUE} → AVERAGEA(Column)
 Output: (100 + 0 + 1) / 3 = 33.6667
 Notes: Counts text as 0 (unlike AVERAGE).

5. AVERAGEX

 Definition: Evaluates an expression for each row and returns the

average of those results.
 Syntax: AVERAGEX(<table>, <expression>)
 Example: AVERAGEX(Sales, Sales[Qty] * Sales[Price])
 Output: (200 + 200 + 300 + 250 + 400) / 5 = 270
 Notes: Useful for weighted/derived row calculations.

6. COUNT
 Definition: Counts numeric (non-blank) values in a column.
 Syntax: COUNT(<column>)
 Example: COUNT(Sales[Price])
 Output: 5
 Notes: Ignores text/logical values.

7. COUNTA

 Definition: Counts all non-blank values (numbers, text,

TRUE/FALSE).
 Syntax: COUNTA(<column>)
 Example: COUNTA(Sales[Product])
 Output: 5
 Notes: Useful to count entries when column has mixed types.

8. COUNTAX

 Definition: Evaluates an expression for each row and counts non-

blank results.
 Syntax: COUNTAX(<table>, <expression>)
 Example: COUNTAX(Sales, Sales[Qty] * Sales[Price])
 Output: 5
 Notes: Counts non-blank evaluation results — works with
text/numeric outcomes.
9. COUNTBLANK

 Definition: Counts blank (empty / null) values in a column.

 Syntax: COUNTBLANK(<column>)
 Example: COUNTBLANK(Sales[Region]) (if one row blank)
 Output: 1
 Notes: Good for data quality checks.

10. COUNTROWS

 Definition: Returns the number of rows in a table (after applied

filters).
 Syntax: COUNTROWS(<table>)
 Example: COUNTROWS(Sales)
 Output: 5
 Notes: Filters/slicers affect the count.

11. COUNTX
 Definition: Evaluates an expression row by row and counts
numeric (non-blank) results.
 Syntax: COUNTX(<table>, <expression>)
 Example: COUNTX(Sales, Sales[Qty] * Sales[Price])
 Output: 5
 Notes: If expression returns non-numeric/blanks, they are treated
accordingly.

12. DISTINCTCOUNT

 Definition: Counts unique (distinct) values in a column.

 Syntax: DISTINCTCOUNT(<column>)
 Example: DISTINCTCOUNT(Sales[Product]) for {A,B,C,A}
 Output: 3
 Notes: Includes blanks unless handled separately.

13. DISTINCTCOUNTNOBLANK

 Definition: Counts unique values but ignores blanks.

 Syntax: DISTINCTCOUNTNOBLANK(<column>)
 Example: Column {A,B,C,blank}
 Output: 3
 Notes: Useful when blanks should not be counted as a distinct
value.
14. APPROXIMATEDISTINCTCOUNT

 Definition: Fast approximate count of distinct values (good for

very large datasets).
 Syntax: APPROXIMATEDISTINCTCOUNT(<column>)
 Example: APPROXIMATEDISTINCTCOUNT(Sales[Customer])
 Output: ≈ 4 (approximate)
 Notes: Very fast and memory-efficient but may be approximate.

15. MAX

 Definition: Returns the largest numeric value in a column.

 Syntax: MAX(<column>)
 Example: MAX(Sales[Price])
 Output: 200
 Notes: Ignores non-numeric values.

16. MAXA

 Definition: Like MAX, but evaluates TRUE=1, FALSE=0, text=0.

 Syntax: MAXA(<column>)
 Example: Column {200, TRUE, "abc"} → MAXA(Column)
 Output: 200
 Notes: Use when mixed types exist and you want conversions.

17. MAXX

 Definition: Evaluates an expression per row and returns the

maximum result.
 Syntax: MAXX(<table>, <expression>)
 Example: MAXX(Sales, Sales[Qty] * Sales[Price])
 Output: max({200,200,300,250,400}) = 400
 Notes: Good for complex row-level comparisons.

18. MIN

 Definition: Returns the smallest numeric value in a column.

 Syntax: MIN(<column>)
 Example: MIN(Sales[Price])
 Output: 50
 Notes: Ignores text.

19. MINA

 Definition: Like MIN, but treats TRUE=1, FALSE=0, text=0.

 Syntax: MINA(<column>)
 Example: Column {50, FALSE, "abc"} → MINA(Column)
 Output: 0
 Notes: Text or FALSE can lower the minimum to 0.

20. MINX

 Definition: Evaluates an expression row by row and returns the

minimum result.
 Syntax: MINX(<table>, <expression>)
 Example: MINX(Sales, Sales[Qty] * Sales[Price])
 Output: min({200,200,300,250,400}) = 200
 Notes: Works with calculated row expressions.

21. PRODUCT

 Definition: Multiplies all numbers in a column.

 Syntax: PRODUCT(<column>)
 Example: PRODUCT(Sales[Qty]) for {2,1,3,5,2}
 Output: 2 * 1 * 3 * 5 * 2 = 60
 Notes: Watch for overflow with many or large numbers.

22. PRODUCTX
 Definition: Evaluates an expression row by row and multiplies all
results.
 Syntax: PRODUCTX(<table>, <expression>)
 Example: PRODUCTX(Sales, Sales[Qty] * Sales[Price]) for
{200,200,300,250,400}
 Output: 200 × 200 × 300 × 250 × 400 = 1,200,000,000,000
 Notes: Results can grow extremely large — use with caution.

DAX Statistical Aggregation Functions

1. MEDIAN

 Definition: Returns the middle value from a column (ignores

blanks and text).
 Syntax:
 MEDIAN(<column>)
 Example: Column {10, 20, 30, 40, 50}
MEDIAN(Column) = 30
 Output: 30

2. MEDIANX

 Definition: Evaluates an expression row by row for a table, then

returns the median value.
 Syntax:
 MEDIANX(<table>, <expression>)
 Example:
MEDIANX(Sales, Sales[Qty] * Sales[Price])
Values = {200, 200, 300, 250, 400}
👉 Median = 250

3. [Link]

 Definition: Returns the value at a given percentile (inclusive

method).
 Syntax:
 [Link](<column>, <k>)
o <k> = percentile value (between 0 and 1).
 Example: Column {10, 20, 30, 40, 50}
👉 [Link](Column, 0.5) = 30 (50th percentile = median).

4. [Link]

 Definition: Returns the value at a given percentile (exclusive

method).
 Syntax:
 [Link](<column>, <k>)
 Example: Column {10, 20, 30, 40, 50}
👉 [Link](Column, 0.5) = 30
 (For larger datasets, INC vs EXC give slightly different results at the
boundaries.)

5. RANKX

 Definition: Returns the rank of a value in a list, based on

evaluation of an expression.
 Syntax:
 RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])
o <order>: 0 = Descending (default), 1 = Ascending.
 Example:
RANKX(Sales,Sales[Qty]*Sales[Price])
Values = {200, 200, 300, 250, 400}
👉 Ranks = {4, 4, 2, 3, 1}

6. STDEV.P

 Definition: Returns the standard deviation of an entire population

(numeric column).
 Syntax:
 STDEV.P(<column>)
 Example: Column {10, 20, 30, 40, 50}
👉 STDEV.P(Column) = 14.14

7. STDEV.S
 Definition: Returns the sample standard deviation (uses n-1).
 Syntax:
 STDEV.S(<column>)
 Example: Column {10, 20, 30, 40, 50}
👉 STDEV.S(Column) = 15.81

8. STDEVX.P

 Definition: Evaluates expression row by row, returns population

standard deviation.
 Syntax:
 STDEVX.P(<table>, <expression>)
 Example:
STDEVX.P(Sales,Sales[Qty]*Sales[Price])
Values = {200, 200, 300, 250, 400}
👉 Output = 70.71

9. STDEVX.S

 Definition: Row by row standard deviation for a sample.

 Syntax:
 STDEVX.S(<table>, <expression>)
 Example: Same dataset as above
👉 Output = 79.06
 10. VAR.P

 Definition: Returns variance of entire population.

 Syntax:
 VAR.P(<column>)
 Example: Column {10, 20, 30, 40, 50}
👉 Output = 200

11. VAR.S

 Definition: Returns variance of a sample.

 Syntax:
 VAR.S(<column>)
 Example: Column {10, 20, 30, 40, 50}
👉 Output = 250

12. VARX.P

 Definition: Variance of a row-by-row expression (population).

 Syntax:
 VARX.P(<table>, <expression>)
 Example:
VARX.P(Sales,Sales[Qty]*Sales[Price])
Values = {200, 200, 300, 250, 400}
👉 Output = 5000
 13. VARX.S

 Definition: Variance of a row-by-row expression (sample).

 Syntax:
 VARX.S(<table>, <expression>)
 Example:
Same dataset → {200, 200, 300, 250, 400}
Output = 6250

DAX Logical Functions

1. IF

 Definition: Returns one value if a condition is TRUE, and another

value if it is FALSE.
 Syntax:
 IF(<logical_test>, <value_if_true>[, <value_if_false>])
 Example:
 Result = IF(Sales[Amount] > 1000, "High", "Low")
o If Amount = 1500 → "High"
o If Amount = 800 → "Low"

2. IFERROR
 Definition: Returns a specified value if an expression results in an
error; otherwise returns the expression.
 Syntax:
 IFERROR(<value>, <value_if_error>)
 Example:
 SafeDivision = IFERROR(Sales[Amount]/Sales[Qty], 0)
o If Qty = 0 → Output = 0
o If Qty = 5, Amount = 100 → Output = 20

3. SWITCH

 Definition: Evaluates an expression against multiple possible

values and returns the corresponding result.
 Syntax:
 SWITCH(<expression>, <value1>, <result1>[, <value2>, <result2>]
…[, <else>])
 Example:

Grade = SWITCH(Sales[Score],

90, "A",

80, "B",

70, "C",
 "Fail")

If Score = 90 → "A"

If Score = 75 → "Fail"

4. AND

 Definition: Returns TRUE if both conditions are TRUE.

 Syntax:
 AND(<logical1>, <logical2>)
 Example:
 Result = IF(AND(Sales[Amount] > 1000, Sales[Qty] > 5), "Valid",
"Invalid")
o If Amount = 1200 and Qty = 10 → "Valid"
o If Amount = 900 and Qty = 10 → "Invalid"

5. OR

 Definition: Returns TRUE if at least one of the conditions is TRUE.

 Syntax:
 OR(<logical1>, <logical2>)
 Example:
 Result = IF(OR(Sales[Amount] > 1000, Sales[Qty] > 5), "Pass",
"Fail")
 o If Amount = 1200 and Qty = 3 → "Pass"
o If Amount = 800 and Qty = 2 → "Fail"

6. NOT

 Definition: Reverses the result of a logical test (TRUE → FALSE,

FALSE → TRUE).
 Syntax:
 NOT(<logical>)
 Example:
 Result = IF(NOT(Sales[Amount] > 1000), "Below 1000", "Above
1000")
o If Amount = 900 → "Below 1000"
o If Amount = 1200 → "Above 1000"

DAX Filter Functions

1. FILTER

 Definition: Returns a table containing only rows that meet a given

condition.
 Syntax:
 FILTER(<table>, <filter_expression>)
 Example:
 HighSales = FILTER(Sales, Sales[Amount] > 1000)
 o Returns only rows where Amount > 1000.

2. ALL

 Definition: Removes filters from a column or table.

 Syntax:
 ALL(<table or column>)
 Example:
 TotalSales = CALCULATE(SUM(Sales[Amount]), ALL(Sales))
o Ignores slicers/filters → always shows total sales.

3. ALLEXCEPT

 Definition: Removes all filters in a table except the ones you

specify.
 Syntax:
 ALLEXCEPT(<table>, <column>)
 Example:
 SalesByRegion=CALCULATE(SUM(Sales[Amount]),
ALLEXCEPT(Sales, Sales[Region]))
o Ignores all filters except Region.

4. ALLSELECTED
 Definition: Removes filters but keeps the filters applied by the
user selection.
 Syntax:
 ALLSELECTED(<table or column>)
 Example:
 SelectedSales=CALCULATE(SUM(Sales[Amount]),
ALLSELECTED(Sales))
o Shows total sales within the selection made by
slicers/filters.

5. CALCULATE

 Definition: Changes the filter context of a calculation.

 Syntax:
 CALCULATE(<expression>, <filter1>, <filter2>, …)
 Example:
 HighValueSales = CALCULATE(SUM(Sales[Amount]),
Sales[Amount] > 1000)
o Adds a condition and returns only sales above 1000.

6. CALCULATETABLE
 Definition: Same as CALCULATE, but returns a table instead of a
single value.
 Syntax:
 CALCULATETABLE(<table>, <filter1>, <filter2>, …)
 Example:
 HighSalesTable = CALCULATETABLE(Sales, Sales[Amount] > 1000)
o Returns a table with only high sales.

7. VALUES

 Definition: Returns a one-column table of unique values from a

column.
 Syntax:
 VALUES(<column>)
 Example:
 UniqueProducts = VALUES(Sales[Product])
o Returns all distinct products in the Sales table.

8. DISTINCT

 Definition: Similar to VALUES, but always removes blanks.

 Syntax:
 DISTINCT(<column>)
 Example:
 DistinctRegions = DISTINCT(Sales[Region])
o Returns unique regions (no blank values).

DAX Time Intelligence Functions

1. TOTALYTD (Year-to-Date)

 Definition: Calculates a running total from the start of the year up

to a selected date.
 Syntax:
 TOTALYTD(<expression>, <dates>[, <filter>])
 Example:
 SalesYTD = TOTALYTD(SUM(Sales[Amount]), Sales[Date])
o Shows cumulative sales from Jan 1st to current date.

2. TOTALMTD (Month-to-Date)

 Definition: Calculates a running total from the start of the month

to the selected date.
 Syntax:
 TOTALMTD(<expression>, <dates>[, <filter>])
 Example:
 SalesMTD = TOTALMTD(SUM(Sales[Amount]), Sales[Date])
o Shows sales from 1st of the month to today.
 3. TOTALQTD (Quarter-to-Date)

 Definition: Calculates cumulative total from start of quarter up to

a date.
 Syntax:
 TOTALQTD(<expression>, <dates>[, <filter>])
 Example:
 SalesQTD = TOTALQTD(SUM(Sales[Amount]), Sales[Date])
o Shows sales from quarter start → current date.

4. SAMEPERIODLASTYEAR

 Definition: Shifts the dates by -1 year to compare with last year’s

same period.
 Syntax:
 SAMEPERIODLASTYEAR(<dates>)
 Example:
 SalesLastYear = CALCULATE(SUM(Sales[Amount]),
SAMEPERIODLASTYEAR(Sales[Date]))
o Compares this year’s sales vs last year same period.

5. DATEADD
 Definition: Shifts a date column by given intervals (days, months,
quarters, years).
 Syntax:
 DATEADD(<dates>, <number_of_intervals>, <interval>)
 Example:
 SalesPrevMonth = CALCULATE(SUM(Sales[Amount]),
DATEADD(Sales[Date], -1, MONTH))
o Moves back 1 month to calculate previous month’s sales.

6. DATESYTD, DATESMTD, DATESQTD

 Definition: Return a table of dates up to today (YTD/MTD/QTD).

 Syntax:
 DATESYTD(<dates>)
 DATESMTD(<dates>)
 DATESQTD(<dates>)
 Example:
 SalesYTD_Table = CALCULATE(SUM(Sales[Amount]),
DATESYTD(Sales[Date]))
o Returns all sales dates from start of year to today.

7. STARTOFYEAR, ENDOFYEAR
 Definition: Return the first or last date of the year.
 Syntax:
 STARTOFYEAR(<dates>)
 ENDOFYEAR(<dates>)
 Example:
 FirstDateYear = STARTOFYEAR(Sales[Date])
 LastDateYear = ENDOFYEAR(Sales[Date])

8. STARTOFMONTH, ENDOFMONTH

 Definition: Return the first or last date of a month.

 Syntax:
 STARTOFMONTH(<dates>)
 ENDOFMONTH(<dates>)
 Example:
 FirstDateMonth = STARTOFMONTH(Sales[Date])
 LastDateMonth = ENDOFMONTH(Sales[Date])

9. PARALLELPERIOD

 Definition: Shifts a date by n intervals (month, quarter, year) but

keeps the same length as original period.
 Syntax:
 PARALLELPERIOD(<dates>, <number_of_intervals>, <interval>)
  Example:
 SalesParallel = CALCULATE(SUM(Sales[Amount]),
PARALLELPERIOD(Sales[Date], -1, YEAR))
o Returns sales for the entire last year.

10. PREVIOUSYEAR, PREVIOUSMONTH, PREVIOUSQUARTER

 Definition: Built-in shortcuts for shifting to previous periods.

 Syntax:
 PREVIOUSYEAR(<dates>)
 PREVIOUSMONTH(<dates>)
 PREVIOUSQUARTER(<dates>)
 Example:
 SalesPrevYear = CALCULATE(SUM(Sales[Amount]),
PREVIOUSYEAR(Sales[Date]))
 SalesPrevMonth = CALCULATE(SUM(Sales[Amount]),
PREVIOUSMONTH(Sales[Date]))

SalesPrevQuarter = CALCULATE(SUM(Sales[Amount]),
PREVIOUSQUARTER(Sales[Date]))
 Math & Trig Functions

ABS

 Returns the absolute (positive) value of a number.

ABS(-25) → 25

CEILING

 Rounds up to nearest integer (or multiple of significance).

CEILING(4.3, 1) → 5

FLOOR

 Rounds down to nearest integer (or multiple).

FLOOR(4.7, 1) → 4

ROUND

 Rounds a number to specified decimal places.

ROUND(3.14159, 2) → 3.14

ROUNDUP

 Always rounds number up.

 ROUNDUP(5.22, 0) → 6

ROUNDDOWN

 Always rounds number down.

ROUNDDOWN(5.99, 0) → 5

POWER

 Returns a number raised to a power.

POWER(2, 3) → 8

EXP

 Returns e^n (exponential).

EXP(1) → 2.718

LN

 Natural logarithm (base e).

LN(7.389) → 2

LOG

 Logarithm to given base (default = 10).

 LOG(100, 10) → 2

SQRT

 Square root of a number.

SQRT(25) → 5

MOD

 Returns remainder of division.

MOD(10, 3) → 1

DIVIDE

 Division with error handling (avoids divide by zero).

DIVIDE(10, 2) → 5

DIVIDE(10, 0, "NA") → "NA"

Text Functions

CONCATENATE

 Joins two text strings.

CONCATENATE("Power", " BI") → "Power BI"

 CONCATENATEX

 Joins values from a column with a separator.

CONCATENATEX(VALUES(Sales[Product]), Sales[Product], ", ")

→ "Laptop, Phone, Tablet"

LEFT

 Returns leftmost characters.

LEFT("PowerBI", 5) → "Power"

RIGHT

 Returns rightmost characters.

RIGHT("PowerBI", 2) → "BI"

MID

 Returns substring from middle.

MID("PowerBI", 2, 3) → "owe"

UPPER

 Converts text to uppercase.

 UPPER("power") → "POWER"

LOWER

 Converts text to lowercase.

LOWER("POWER") → "power"

TRIM

 Removes extra spaces.

TRIM(" Power BI ") → "Power BI"

REPLACE

 Replaces part of text with another.

REPLACE("Power BI", 7, 2, "Apps") → "Power Apps"

LEN

 Returns length of text.

LEN("Power BI") → 8

SEARCH

 Returns position of text (case-insensitive).

 SEARCH("BI", "Power BI") → 7

FIND

 Returns position of text (case-sensitive).

FIND("BI", "Power bi") → Error (case mismatch)

Information Functions

ISBLANK

 Checks if value is blank.

ISBLANK( BLANK() ) → TRUE

ISERROR

 Checks if value is an error.

ISERROR(1/0) → TRUE

ISNUMBER

 Checks if value is a number.

ISNUMBER(123) → TRUE

ISTEXT
 Checks if value is text.

ISTEXT("Hello") → TRUE

ISNONTEXT

 TRUE if not text (number/blank/error).

ISNONTEXT(123) → TRUE

ISEMPTY

 Returns TRUE if table is empty.

ISEMPTY( VALUES(Sales[Product]) ) → depends on data

ISFILTERED

 Returns TRUE if column is filtered.

ISFILTERED(Sales[Year]) → TRUE (if slicer applied)

HASONEVALUE

 Returns TRUE if column has a single distinct value.

HASONEVALUE(Sales[Year]) → TRUE (if one year selected in slicer)