DAX FUNCTIONS

What is a Dax :
DAX or data analysis expression includes all the calculations you can perform in power bi.
It allows you to create new fields , tables in your model.

DAX formulas are made up of 3 core components:

1. Syntax : Proper DAX syntax is made up of a variety of elements, some of which are
common to all formulas.
2. Functions : DAX functions are predefined formulas that take some parameters and
perform a specific calculation.
3. Context : DAX uses context to determine which rows should be used to perform a
calculation.

Where does DAX formula’s are used :

There are 3 ways where we can use DAX formulas :
1. Calculated Tables : These calculations will add an additional table to the report based on
a formula.
2. Calculated Columns : These calculations will add an additional column to a table based on
a formula. These columns are treated like any other field in the table.
3. Measures : These calculations will add a summary or aggregated measure to a table
based on a formula.
How to write a DAX formula :
1. The name of the measure or calculated column.
2. The equal-to operator (“=”) indicates the start of the formula.
3. A DAX function Opening (and closing) parentheses (“()”).
4. Column and/or table references.
5. Note that each subsequent parameter in a function is separated by a comma (“,”).

Eg:
Total Sales = SUM (Financials[sales])

Some of the most common DAX functions used in reports are:

1. Simple calculations: COUNT, DISTINCTCOUNT, SUM, AVERAGE, MIN, MAX.

2. SUMMARISE: Returns a table used to apply aggregations over different groupings.
3. CALCULATE: Performs an aggregation along with one or more filters. When you specify
more than one filter, the function will perform the calculation where all filters are true.
4. IF: Based on a logical condition, it will return a different value if it is true or false. This is
similar to the CASE WHEN operation in SQL.
5. IFERROR: Looks for any errors for an inner function and returns a specified result
6. ISBLANK: This function checks if the rows in a column are blank and returns true or false.
It is useful in conjunction with other functions like IF.
7. EOMONTH: Returns the last day of the month of a given date (column reference in a date
format) for as many months in the past or the future.
8. DATEDIFF: returns the difference between two dates (both as column references in date
formats) in days, months, quarters, years, etc.
Understanding Context in DAX Formulas :

There are two main types of context in DAX:

Row context :

1. This refers to the current row across all the columns of a table and extends to all the
columns in related tables.

2. This type of context lets the DAX formula know which rows to use for a specific formula.

Eg:

Cost Price Per Unit = Financials[COGS]/ Financials[Units Sold]

Here we are creating a calculated column (Cost Price Per Unit) , in that we are accessing
row-wise calculations by considering the columns (COGS,Units Sold) from Financials table.

The output will be the row-wise calculations.

filter context :

Filter context is applied on top of a row context and refers to a subset of rows or columns
that are specified as filters in the report. Filters can be applied in a few ways:

1. Directly in a DAX formula

2. Using the filters pane
3. Using a slicer visual
4. Through the fields that make up a visual (such as the rows and columns in a matrix)

Eg:
USA Profit Margin = CALCULATE ( SUM ( Financials[Profit] ) / SUM ( Financials[Sales] ) ,
Financials[Country] = “United States of America” )
 Sales ID Date Product Quantity Unit Customer Region
Price

1 2024-01- A 3 10 John East

05

2 2024-01- B 2 20 Mary East

06

3 2024-02- A 5 10 Alex West

01

4 2024-02- C 1 50 Sara West

07

5 2024-02- B 4 20 Mary East

10

Maths & Statistical Functions

1. SUM(<column>) : Adds all values in a column.

Eg : Total Quantity = SUM(Sales[Quantity])
Result: 3 + 2 + 5 + 1 + 4 = 15

2. SUMX(<table>, <expression>) : Evaluates expression row-by-row then sums.

Eg : Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])
Result: 30 + 40 + 50 + 50 + 80 = 250

3. AVERAGE(<column>) : Simple column average.

Eg : Average Quantity = AVERAGE(Sales[Quantity])
Result: 15 / 5 = 3
4. AVERAGEX(<table>, <expression>) : Average of an expression.
Eg : Average Sales = AVERAGEX(Sales, Sales[Quantity] * Sales[Unit Price])
Result: (30 + 40 + 50 + 50 + 80) / 5 = 50

5. MEDIAN(<column>) : It sort the values and the gives the arithmetic value.
Eg : Median Quantity = MEDIAN(Sales[Quantity])
Result: 1,2,3,4,5 = 3

6. MEDIANX(<table>,<expression>) : It evaluates the expression 1st and then return median

Eg : Median Sales = MEDIANX(Sales, Sales[Quantity] * Sales[Unit Price])
Result: 30,40,50,50,80 = 50

7. GEOMEAN(<column>) : Geometric mean of Quantity

Eg : Geo Mean Qty = GEOMEAN(Sales[Quantity])
Result : (3 × 2 × 5 × 1 × 4)^(1/5) ≈ 2.99

8. GEOMEANX(<table>, <expression>) : It evaluates the expression 1st and then return

geometric mean
Eg : Geo Mean Sales = GEOMEANX(Sales, Sales[Quantity] * Sales[Unit Price])
Result : Geometric mean of {30, 40, 50, 50, 80} ≈ 47.43

9. COUNT(<column>) : Counts non-blanks

Eg : Count Products = COUNT(Sales[Product])
Result : 5

10. COUNTX(<table>, <expression>) : Counts non-blank results of an expression.

Eg : Count NonZero Sales = COUNTX(Sales, Sales[Quantity] * Sales[Unit Price])
Result : All rows are non-blank → 5

11. DIVIDE(<numerator>, <denominator>)

 Eg : Average Price per Quantity = DIVIDE(SUM(Sales[Amount]),
SUM(Sales[Quantity]))

12. MIN(<column>) : Returns the min value from a particular column

Eg : Min Qty = MIN(Sales[Quantity])
Result = 1

13. MAX(<column>) : Returns the max value from a particular column

Eg : Max Qty = MAX(Sales[Quantity])
Result= 5

14. COUNTROWS(<table>) : Counts the total no of rows from entire table

Eg : Row Count = COUNTROWS(Sales)
Result= 5

15. DISTINCTCOUNT(<column>) : Counts the unique no of rows from the given column
Eg : Distinct Products = DISTINCTCOUNT(Sales[Product])
Result : Products = {A, B, A, C, B} → 3 distinct

16. RANKX(<table>, <expression>) : Assign ranks based on the final output of the
expression
Eg: Rank products by total sales.
Product Rank =RANKX(ALL(Sales[Product]),CALCULATE(SUM(Sales[Amount])), DESC)
Result :
Totals:
A = 80
B = 120
C = 50
Ranks:
B = 1, A = 2, C = 3
 Filter Functions

1. FILTER(<table>, <filter>) : Returns a filtered table, not a number.

Eg : Get only East region rows
East Sales = FILTER(Sales, Sales[Region] = "East")
Result : SalesID 1, 2, 5

2. CALCULATE(<expression>, <filters>) : Changes the filter context and returns a measure

value.
Eg: Total Quantity for East region only
Total Qty East = CALCULATE(SUM(Sales[Quantity]),Sales[Region] = "East")
Result : This forces the measure to ignore slicers unless they match East.

3. HASONEVALUE(<column>) : Returns TRUE/FALSE.

Eg : Check if a single product is selected
Is One Product Selected = HASONEVALUE(Sales[Product])
Useful for dynamic titles:
If TRUE → show selected product
If FALSE → show "All Products"

4. ALLNOBLANKROW(<table>) : Returns a table without the blank row added for many-to-
one relationships.
Eg: Get all non-empty Product rows
All Products No Blank =ALLNOBLANKROW(Sales[Product])
Result : remove auto-created blank row in relationships.

5. ALL(<table> | <column>) : Removes all filters on the table or columns provided.

Eg: Total quantity ignoring all filters
Total Qty All =CALCULATE(SUM(Sales[Quantity]), ALL(Sales))
Result : Even if the report filters Region = East, this measure shows the grand total (15).
Eg: Remove filter from Product only
Qty Ignore Product =CALCULATE(SUM(Sales[Quantity]), ALL(Sales[Product]))
Result : Everything else stays filtered except Product.

6. ALLEXCEPT(<table>, <column>) : Clears all filters except the specified columns.

Eg: Sum of quantity by Region but ignoring Product
Total Qty Except Product =CALCULATE(SUM(Sales[Quantity]),ALLEXCEPT(Sales,
Sales[Region]))

Result : Region filter stay and All other filters removed

7. REMOVEFILTERS(<table> | <column>) : Newer version of ALL() but more flexible.

Equivalent to ALL() but more intuitive.
Eg: Calculate total quantity ignoring Region only
Qty Ignore Region =CALCULATE(SUM(Sales[Quantity]),REMOVEFILTERS(Sales[Region]))
Result : This removes only Region-related filters.

Logical Functions
1. IF(<logical_test>, <value_if_true>, <value_if_false>) : Returns one value if the condition
is TRUE, another if FALSE.
Eg: Categorize Quantity as High / Low
Qty Category =IF(Sales[Quantity] > 3, "High", "Low")

2. AND(<logical1>, <logical2>) : TRUE only when both conditions are TRUE.

Eg: Return rows where Product = B AND Region = East
Is BEast Sale = AND(Sales[Product] = "B", Sales[Region] = "East")
Results: TRUE for SalesID 2 and 5.
3. OR(<logical1>, <logical2>) : TRUE if either condition is TRUE.
Eg: Check if Quantity > 4 OR Unit Price > 20
High Qty or Price = OR(Sales[Quantity] > 4, Sales[Unit Price] > 20)

4. NOT(<logical>) : Reverses TRUE/FALSE.

Eg: Check if NOT West region
Is Not West = NOT(Sales[Region] = "West")
Result : TRUE for East rows.

5. SWITCH(<expression>, <value>, <result>, …, <else>) : Cleaner alternative to many nested

IFs.
Eg: Label Products
Product Label = SWITCH(Sales[Product], "A", "Category Alpha","B", "Category Beta", "C",
"Category Gamma","Other")

6. IFERROR(<value>, <value_if_error>) : Returns alternate result if expression errors.

Eg: Safe Division (Qty / Price)
Safe Ratio = IFERROR(Sales[Quantity] / Sales[Unit Price],0)
Result : If Unit Price = 0, returns 0 instead of error.

Date & Time Functions

CALENDAR(<start_date>, <end_date>) : Creates a continuous date table from the start

date to the end date.
Eg : Calendar = CALENDAR("2024-01-01", "2024-12-31")
Result : A table with 365 rows, from Jan 1, 2024 to Dec 31, 2024.
2. DATE(<year>, <month>, <day>) : Builds a valid date value from year, month, and day
numbers.
Eg : PurchaseDate = DATE(2024, 1, 5)
Result : 2024-01-05

3. DATEDIFF(<date1>, <date2>, <interval>) : Returns the difference between two dates in

the chosen interval (days, months, years, etc.).
Eg : Days Between = DATEDIFF(Sales[Date], TODAY(), DAY)
Result : Assume TODAY() = 2024-12-31
Date Days Between (to 2024-12-31)
2024-01-05 361 days
2024-01-06 360 days
2024-02-01 334 days
2024-02-07 328 days
2024-02-10 325 days

4. DATEVALUE(<text>) : Converts a text string into a real date value.

Eg : TextToDate = DATEVALUE("2024-03-15")
Result : 2024-03-15 (converted from text to date)

5. DAY(<date>) : Extracts the day number (1–31) from a date.

Eg : DayNumber = DAY(Sales[Date])
Result :
Date DayNumber
2024-01-05 05
2024-01-06 06
2024-02-01 01
2024-02-07 07
2024-02-10 10

6. WEEKNUM(<date>) : Returns the week number of the year for a given date.
Eg : WeekNumber = WEEKNUM(Sales[Date])
Result : (Using default: Week starts Sunday)
Sunday – 01,Monday-02,Tuesday-03,……
Date WeekNumber
2024-01-05 05
2024-01-06 06
2024-02-01 01
2024-02-07 07
2024-02-10 10

7. MONTH(<date>) : Extracts the month number (1–12) from a date.

Eg : MonthNumber = MONTH(Sales[Date])
Result :
Date MonthNumber
2024-01-05 01
2024-01-06 01
2024-02-01 02
2024-02-07 02
2024-02-10 02

8. QUARTER(<date>) : Returns the quarter number (1–4) of the year for a given date.
Eg : QuarterNumber = QUARTER(Sales[Date])
Result :
Date QuarterNumber
2024-01-05 Q1
2024-01-06 Q1
2024-02-01 Q1
2024-02-07 Q1
2024-02-10 Q1
 TIME INTELLIGENCE FUNCTIONS
1. DATEADD(<dates>, <num_intervals>, <interval>) : Shifts dates backward/forward.
Eg : Last Month Dates = DATEADD(Sales[Date], -1, MONTH)
Result :
Date Last Month Dates
2024-01-05 2023-12-05
2024-01-06 2023-12-06
2024-02-01 2024-01-01
2024-02-07 2024-01-07
2024-02-10 2024-01-10

2. DATESBETWEEN(<dates>, <start>, <end>) : Returns dates between start & end.

Eg : FebDates = DATESBETWEEN(Sales[Date],DATE(2024,2,1),DATE(2024,2,29))
Result : Only dates within Feb 2024 remain
Feb
Dates

2024-02-
01

2024-02-
07

2024-02-
10

3. TOTALYTD(<expression>, <dates>) : Year-to-date running total.

Eg : Sales YTD = TOTALYTD([Total Sales], Sales[Date])
Result :
Date Total Sales Sales YTD
2024-01-05 100 100
2024-01-06 150 250
2024-02-01 200 450
 2024-02-07 120 570
2024-02-10 180 750

4. SAMEPERIODLASTYEAR(<dates>) : Returns same calendar period shifted back 1 year.

Eg : Last Year Same Period = SAMEPERIODLASTYEAR(Sales[Date])
Result :
Date Last Year Same Period
2024-01-05 2023-01-05
2024-01-06 2023-01-06
2024-02-01 2023-02-01
2024-02-07 2023-02-07
2024-02-10 2023-02-10

5. STARTOFMONTH / ENDOFMONTH : Beginning / Last day of each month

Eg : StartOfMonth = STARTOFMONTH(Calendar[Date])
Eg : EndOfMonth = ENDOFMONTH(Calendar[Date])
Result :
Date StartOfMonth EndOfMonth
2024-01-05 2024-01-01 2024-01-31
2024-01-06 2024-01-01 2024-01-31
2024-02-01 2024-02-01 2024-02-29
2024-02-07 2024-02-01 2024-02-29
2024-02-10 2024-02-01 2024-02-29

6. STARTOFQUARTER / ENDOFQUARTER : Beginning / Last of quater

Eg : SoQ = STARTOFQUARTER(Calendar[Date])
Eg : EoQ = ENDOFQUARTER(Calendar[Date])
Result :
2024 Q1 = Jan–Mar
 Date StartOfQuarter EndOfQuarter
2024-01-05 2024-01-01 2024-03-31
2024-01-06 2024-01-01 2024-03-31
2024-02-01 2024-01-01 2024-03-31
2024-02-07 2024-01-01 2024-03-31
2024-02-10 2024-01-01 2024-03-31

7. STARTOFYEAR / ENDOFYEAR : Start/End of a year

Eg : SoY = STARTOFYEAR(Calendar[Date])
Eg : EoY = ENDOFYEAR(Calendar[Date])
Result :
Date StartOfYear EndOfYear
2024-01-05 2024-01-01 2024-12-31
2024-01-06 2024-01-01 2024-12-31
2024-02-01 2024-01-01 2024-12-31
2024-02-07 2024-01-01 2024-12-31
2024-02-10 2024-01-01 2024-12-31

RELATIONSHIP FUNCTIONS
1. CROSSFILTER(<left>, <right>, <direction>) : Controls the filter direction between two
related tables inside a calculation.
Eg : Custom Filter = CALCULATE([Total Sales],CROSSFILTER(Sales[Customer],
Customers[Customer], BOTH))
Customer table:
Customer Region
John East
Mary East
Alex West
Sara West
Result :
 Region Custom Filter
East 150
West 100

2. RELATED(<column>) : Fetches a value from a related table (one side → many side) for
each row in the current table.
Eg : Customer Region = RELATED(Customers[Region])
Result :
Sales ID Customer Customer Region
1 John East
2 Mary East
3 Alex West
4 Sara West
5 Mary East

TABLE MANIPULATION FUNCTIONS

1. SUMMARIZE() – Groups a table by column(s) and aggregates data.
Eg : Product Summary = SUMMARIZE(Sales,Sales[Product],"TotalQty",
SUM(Sales[Quantity]),"TotalSales", SUMX(Sales, Sales[Quantity] * Sales[Unit Price]))
Result :
Product TotalQty TotalSales
A 8 80
B 6 120
C 1 50

2. DISTINCT() : Returns unique values from a column.

Eg : Distinct Customers = DISTINCT(Sales[Customer])
Result :
Custom
er

Jo
 hn

Ma
ry

Al
ex

Sa
ra

3. ADDCOLUMNS() : Adds calculated columns to an existing table

Eg : Sales With Amount = ADDCOLUMNS(Sales,"Amount", Sales[Quantity] * Sales[Unit
Price])
Result :
Sales ID Date Product Quantity Unit Customer Region Amount
Price

1 2024-01- A 3 10 John East 30

05

2 2024-01- B 2 20 Mary East 40

06

3 2024-02- A 5 10 Alex West 50

01

4 2024-02- C 1 50 Sara West 50

07

5 2024-02- B 4 20 Mary East 80

10

4. SELECTCOLUMNS() : Selects specific columns and optionally renames them.

Eg : Customer Info = SELECTCOLUMNS(Sales,"Name", Sales [Customer],"Region", Sales
[Region])
Result :
Customer Region

John East

Mary East

Alex West
 Sara West

Mary East

5. GROUPBY() : Groups table by column(s) and performs calculations on each group using
CURRENTGROUP().
Eg : Group By Product = GROUPBY(Sales,Sales[Product],"TotalQty",
SUMX(CURRENTGROUP(), Sales[Quantity]))

Result :
Product TotalQty
A 8
B 6
C 1

6. INTERSECT() : Returns rows that are common in two tables.

Eg : CommonRows = INTERSECT(Sales,Customers )
Result :
Customer Region

John East

Mary East

Alex West

Sara West

7. NATURALINNERJOIN() : Performs inner join between two tables based on common

columns.
Eg : InnerJoin = NATURALINNERJOIN(Sales, Customers)
Result :
Sales ID Date Product Quantity Unit Customer Region
Price

1 2024-01- A 3 10 John East

 05

2 2024-01- B 2 20 Mary East

06

3 2024-02- A 5 10 Alex West

01

4 2024-02- C 1 50 Sara West

07

8. NATURALLEFTOUTERJOIN() : Performs left join between two tables.

Eg : LeftJoin = NATURALLEFTOUTERJOIN(Sales, Customers)
Result :
Sales ID Date Product Quantity Unit Customer Region
Price

1 2024-01- A 3 10 John East

05

2 2024-01- B 2 20 Mary East

06

3 2024-02- A 5 10 Alex West

01

4 2024-02- C 1 50 Sara West

07

9. UNION() : Combines two tables with the same columns into one.
Eg : Combined = UNION(SELECTCOLUMNS(Sales, "Customer", Sales[Customer], "Region",
Sales[Region]),Customers)
Result :
Customer Region

John East

Mary East

Alex West
 Sara West

Mary East

John East

Mary East

Alex West

Sara West

TEXT FUNCTIONS

1. EXACT() : Checks if two text strings are exactly the same (case-sensitive).
Eg : ExactMatch = EXACT("apple", "Apple")
Result : False

2. FIND() : Returns the starting position of a substring within a text (case-sensitive).

Eg : FindLetter = FIND("A", Sales[Product])
Result : Depends on Sales[Product]. For Product = "A" → 1

3. FORMAT() : Converts a value to a text string in a specific number format.

Eg : Formatted Amount = FORMAT([Total Sales], "₹#,##0")
Result : ₹100 (if [Total Sales] = 100)

4. LEFT() : Returns the first N characters from the start of a string.

Eg : First2 = LEFT(Customers[Customer Name], 2)
Result : "Jo" (if Customer Name = John)
5. RIGHT() : Returns the last N characters from the end of a string.
Eg : Last3 = RIGHT(Customers[Customer Name], 3)
Result : "ohn" (if Customer Name = John)

6. LEN() : Returns the number of characters in a text string.

Eg : NameLength = LEN(Customers[Customer])
Result : 4 (if Customer Name = John)

7. LOWER() / UPPER() : Converts text to lowercase / uppercase.

Eg : LowerName = LOWER(Customers[Customer Name])
Eg : UpperName = UPPER(Customers[Customer Name])
Result :
Lower : “john” (if Customer Name = John)
Upper : “JOHN” (if Customer Name = John)

8. TRIM() : Removes extra spaces before, after, or between words.

Eg : Trimmed = TRIM(" Hello ")
Result : "Hello"

9. CONCATENATE() : Joins two text strings together.

Eg : FullInfo = CONCATENATE(Customers[Customer], " - " & Customers[Region])
Result : "John - East"

10. SUBSTITUTE() : Replaces occurrences of a substring within text.

Eg : ReplaceA = SUBSTITUTE("ABC", "A", "X")
Result : "XBC"

11. REPLACE() : Replaces a part of a string based on position and length.

Eg : ReplaceSubstring = REPLACE("PowerBI", 1, 5, "Data")
Result : "DataBI"
 INFORMATION FUNCTIONS

1. COLUMNSTATISTICS() : Creates statistics (min, max, avg, count, distinct count, etc.) for all
columns in the model.
Eg : ColumnStats = COLUMNSTATISTICS()
Result : A table containing statistics for every column in every table.

2. NAMEOF() : Returns the name of a column or measure.

Eg : ColumnName = NAMEOF(Sales[Quantity])
Result : "Quantity"

3. ISBLANK() : Checks if a value is blank.

Eg : CheckBlank = ISBLANK(Sales[Customer])
Result :
TRUE → if Customer is empty
FALSE → otherwise

4. ISERROR() : Checks whether an expression results in an [Link] here to avoid divide-

by-zero errors.
Eg : SafeDiv =IF(ISERROR(Sales[Quantity] / Sales[Unit Price]),0,Sales[Quantity] / Sales[Unit
Price])
Result : Returns 0 if there is an error ; otherwise returns the division result.

5. ISLOGICAL() : Checks if value is TRUE or FALSE.

Eg : CheckLogical = ISLOGICAL(TRUE())
Result : TRUE
6. ISNUMBER() : Checks if the value is a number.
Eg : CheckNum = ISNUMBER(Sales[Quantity])
Result :

TRUE → if Quantity is numeric

FALSE → if not

7. ISFILTERED() : Checks whether a field is directly filtered.

Eg : RegionFiltered = ISFILTERED(Customers[Region])
Result :
TRUE → if Region slicer is used or filtered
FALSE → if not

8. ISCROSSFILTERED() : Checks whether a field is indirectly filtered via relationships.

Eg : CrossFiltered = ISCROSSFILTERED(Customers[Region])
Result :
TRUE → if Region is filtered through related tables (like Sales)
FALSE → otherwise

9. USERPRINCIPALNAME() : Returns the logged-in user's email/UPN.

Eg : LoggedUser = USERPRINCIPALNAME()
Result : "username@[Link]"

DAX STATEMENTS

1. VAR…RETURN : Used to store values inside variables and return a final result.
Eg : Total Sales with Profit =
VAR Total = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])
VAR Profit = Total * 0.20
RETURN Profit
Stores intermediate calculations in variables, then returns the final value.

2. COLUMN() statement (used inside ADDCOLUMNS / GROUPBY)

Creates a calculated column inside a table expression.
Eg : ADDCOLUMNS(Sales,"Amount", Sales[Quantity] * Sales[Unit Price])
Result :
Amount
30
40
50
50

3. ORDER BY() : Used only in calculated tables/columns to define sort order.

Eg : MonthNumber = MONTH(Sales[Date])
Then:
ORDER BY Sales[MonthNumber] ASC
Result : Sorts the calculated column MonthNumber in ascending order.
Month order becomes:
1, 1, 2, 2