1. SUM
Adds all values in a single column.
Use case: Calculate the total sales amount from a column.
Total Sales = SUM(Sales[SalesAmount])
2. SUMX
Iterates over a table and sums the result of an expression
Use case: Calculate row-wise total for quantity multiplied by price.
Total Line Sales =
SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
3. COUNTROWS
Returns the number of rows in a table.
Use case: Count the number of rows in the Sales table.
Order Count = COUNTROWS(Sales)
4. DISTINCTCOUNT
Counts unique values in a column.
Use case: Count the number of unique customers.
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])
5. CALCULATE
Evaluates an expression in a modified filter context.
Use case: Apply a specific filter (like country = USA) to a measure.
Sales USA = CALCULATE([Total Sales], Customer[Country] = "USA")
6. FILTER
Returns a table with rows that meet a condition.
Use case: Return sales only for transactions greater than 1000.
High Value Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[SalesAmount] > 1000))
7. VALUES
Returns a one-column table of unique values from a column.
Use case: Get a distinct list of product names used in a visual or slicer.
Product List = VALUES(Sales[ProductName])
8. HASONEVALUE
Checks if a column has only one distinct value in the current filter context.
Use case: Check if exactly one category is selected in a visual.
Selected Category = IF(HASONEVALUE(Product[Category]), VALUES(Product[Category]), "Multiple")
9. SELECTEDVALUE
Returns the value when only one is selected, otherwise blank or default.
Use case: Display selected value from slicer or default text if none.
Selected Region = SELECTEDVALUE(Customer[Region], "All Regions")
10. SWITCH
Evaluates expressions like a multiple IF/ELSE logic.
Use case: Categorize sales into High, Medium, or Low based on value.
Sales Category = SWITCH(TRUE(), [Total Sales] > 50000, "High", [Total Sales] > 10000, "Medium", "Low" )
11. DIVIDE
Safely performs division, avoiding divide-by-zero errors.
Use case: Calculate profit margin and handle divide-by-zero errors.
Profit Margin = DIVIDE([Profit], [Total Sales], 0)
12. IF
Evaluates a condition and returns one value if true, another if false.
Use case: Add logic-based conditional columns.
Flag = IF(Sales[SalesAmount] > 1000, "High", "Low")
13. RANKX
Ranks values over a table based on an expression.
Use case: Rank products based on sales.
Product Rank = RANKX(ALL(Product), [Total Sales], , DESC)
14. EARLIER
Refers to an earlier row context in a calculated column.
Use case: Use the value of a column in earlier row context for comparison.
Sales Rank by Customer = CALCULATE( COUNTROWS( FILTER(Sales, Sales[SalesAmount] > EARLIER(Sales[SalesAmount]))) )
15. RELATEDTABLE
Returns a table of related rows from another table.
Use case: Count all related rows (e.g., orders for each customer).
Related Orders = COUNTROWS(RELATEDTABLE(Sales))
16. Cumulative Total
Calculates a running total over time.
Use Case: Track running total of sales over time (e.g., month-by-month).
Cumulative Sales = CALCULATE([Total Sales], FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date])))
17. SAMEPERIODLASTYEAR
Returns the same date range from the previous year.
Use Case: Compare this year’s sales with the same period last year.
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
18. PARALLELPERIOD
Shifts a time period forward or backward in time.
Use Case: Fetch values from the same period in a previous year, quarter, or month.
Sales Parallel Period = CALCULATE([Total Sales], PARALLELPERIOD('Date'[Date], -1, YEAR))
19. DATESADD
Shifts a date column by a specified number of intervals.
Use Case: Shift current period sales by -1 month for month-over-month comparison.
Sales Previous Month = CALCULATE([Total Sales], DATESADD('Date'[Date], -1, MONTH))
20. DATESBETWEEN
Returns dates between two specified dates.
Use Case: Calculate sales within a specific custom date range.
Sales Custom Range = CALCULATE([Total Sales], DATESBETWEEN('Date'[Date], DATE(2024,1,1), DATE(2024,3,31)))
21. DATEDIFF
Returns the difference between two dates in a specified interval (days, months, etc.).
Use Case: Find number of days between order and shipment dates.
Days to Ship = DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY)
22. RELATED
Fetches a related value from another table (many-to-one).
Use Case: Retrieve related values (e.g., region) from a dimension table into a fact table.
Customer Region =
RELATED(Customer[Region])
23. ADDCOLUMNS
Adds calculated columns to a table expression.
Use Case: Add calculated columns like year or month to a table for further analysis.
Sales with Year = ADDCOLUMNS(Sales, "Order Year", YEAR(Sales[OrderDate]))
24. SUMMARIZE
Groups data and returns a table with aggregated values.
Use Case: Group data by region and calculate total sales.
Sales by Region =
SUMMARIZE(Sales, Customer[Region],
“Total Sales”, SUM(Sales[SalesAmount]))
25. ALL
Removes all filters from a table or column.
Use Case: Ignore all filters on the Sales table for total value calculation.
All Sales =
CALCULATE([Total Sales], ALL(Sales))
26. ALLEXCEPT
Removes all filters except those on specified columns.
Use Case: Keep filter on ProductName while ignoring all others.
Sales by Product = CALCULATE([Total Sales], ALLEXCEPT(Sales, Sales[ProductName]))
26. ALLSELECTED
Removes filters except those applied by user selections in visuals.
Use Case: Respect user-selected filters from visuals or slicers in total calculation.
Selected Sales = CALCULATE([Total Sales], ALLSELECTED('Date'[Date]))
28. TOTALYTD
Calculates year-to-date total for an expression.
Use Case: Calculate Year-To-Date cumulative sales total.
Sales YTD =
TOTALYTD([Total Sales], 'Date'[Date])
29. CONCATENATE
Joins two text strings into one.
Use Case: Combine first name and last name into a full name.
FullName =
CONCATENATE(Employees[FirstName], Employees[LastName])
30. CONCATENATEX
Joins text values from a table or expression with a delimiter.
Use Case: Display all product names sold in a single row separated by commas.
ProductList = CONCATENATEX(VALUES(Sales[ProductName]), Sales[ProductName], ", ")
31. LEFT
Returns the leftmost characters from a text string.
Use Case: Extract the area code from a phone number.
AreaCode = LEFT(Employees[PhoneNumber], 3)
32. RIGHT
Returns the rightmost characters from a text string.
Use Case: Extract the last 4 digits of an ID.
Last4Digits = RIGHT(Employees[EmployeeID], 4)
33 MID
Returns a substring from a text string using a starting position and length.
Use Case: Extract year from a string like “Invoice_2024_Report”.
ExtractYear = MID(Sales[FileName], 9, 4)
34. UPPER
Converts text to uppercase.
Use Case: Standardize customer names to uppercase.
UpperName = UPPER(Customers[CustomerName])
35. LOWER
Converts text to lowercase.
Use Case: Normalize email addresses.
LowerEmail = LOWER(Customers[Email])
36. TRIM
Removes all extra spaces from text, leaving single spaces between words.
Use Case: Clean user-entered text before analysis.
CleanName = TRIM(Customers[CustomerName])
37. SUBSTITUTE
Replaces existing text with new text in a string.
Use Case: Replace “Ltd” with “Limited” in company names.
CompanyName = SUBSTITUTE(Companies[Name], "Ltd", "Limited")
38. FIND
Returns the position of a substring in a text string.
Use Case: Find where the “@” symbol appears in an email address.
AtPosition = FIND("@", Customers[Email])
39. COALESCE
Returns the first non-blank value among its arguments.
Use Case: Replace blank values with default values.
COALESCE([Sales], 0)
40. USERPRINCIPALNAME()
Returns the user principal name (email) of the current user.
Use Case: Implement row-level security based on user identity.
USERPRINCIPALNAME()
41. USERNAME()
Returns the domain\username or email of the current user.
Use Case: Used in dynamic row-level security.
USERNAME()
42. BLANK()
Returns a blank value.
Use Case: Use in IF conditions or to reset values.
IF([Sales] = 0, BLANK(), [Sales])
43. CALENDAR
Returns a table with a single column of dates between start and end dates.
Use Case: Create a custom date table.
CALENDAR(DATE(2022,1,1), DATE(2025,12,31))
44. CALENDARAUTO
Automatically generates a date table based on data model.
Use Case: Create a date table without manually setting date range.
CALENDARAUTO()
45. TODAY()
Returns the current date (no time).
Use Case: Filter data based on today’s date.
TODAY()
46. YEAR()
Extracts the year from a date.
Use Case: Create a Year column from a date field.
YEAR([OrderDate])
47. HOUR()
Extracts the hour component from a time or datetime.
Use Case: Analyze data by hour of day.
HOUR([OrderTime])
48. MONTH()
Extracts the month number from a date (1–12).
Use Case: Group data by month.
MONTH([OrderDate])
49. MINUTE()
Extracts the minute from a time value.
Use Case: Create time-based analysis.
MINUTE([OrderTime])
50. NOW()
Returns the current date and time.
Use Case: Track exact time of calculation.
NOW()
51. EDATE()
Returns a date shifted by a specified number of months.
Use Case: Calculate expiry dates, due dates, etc.
EDATE([StartDate], 3)
52. EOMONTH()
Returns the last day of the month after adding months.
Use Case: Get month-end dates for reporting.
EOMONTH([StartDate], 1)
