DAX FUNCTIONS

Aggregation Functions

1. SUM: Calculates the total of a column.

• Example: SUM(Sales[Sales Amount]) calculates the total sales amount.

2. SUMX: Calculates the sum of an expression evaluated for each row in a table.

• Example: SUMX(Sales, Sales[Sales Amount] * Sales[Quantity]) calculates the total revenue.

3. AVERAGE: Calculates the average of a column.

• Example: AVERAGE(Sales[Sales Amount]) calculates the average sales amount.

4. AVERAGEX: Averages an expression evaluated for each row in a table.

• Example: AVERAGEX(Sales, Sales[Sales Amount] / Sales[Quantity]) calculates the average price per unit.

5. COUNT: Counts the number of non-blank values in a column.

• Example: COUNT(Sales[Product]) counts the number of unique products.

6. COUNTX: Counts non-blank results of an expression in a table.

• Example: COUNTX(Sales, Sales[Sales Amount] > 1000) counts orders with a sales amount greater than 1000.

7. COUNTBLANK: Returns the count of blank cells in a column.

• Example: COUNTBLANK(Sales[Region]) counts the number of regions without a value.

8. MAX: Returns the largest value in a column.

• Example: MAX(Sales[Sales Amount]) finds the highest sales amount.

9. MAXX: Returns the largest value in an expression evaluated for each row.

• Example: MAXX(Sales, Sales[Sales Amount] / Sales[Quantity]) finds the highest price per unit.

10. MIN: Returns the smallest value in a column.

• Example: MIN(Sales[Sales Amount]) finds the lowest sales amount.

11. MINX: Returns the smallest value in an expression for each row.

• Example: MINX(Sales, Sales[Sales Amount] / Sales[Quantity]) finds the lowest price per unit.

12. PRODUCT: Multiplies all numbers in a column.

• Example: PRODUCT(Sales[Quantity]) calculates the total quantity of products.

13. PRODUCTX: Multiplies all numbers in an expression for each row in a table.

• Example: PRODUCTX(Sales, Sales[Sales Amount] * Sales[Quantity]) calculates the total revenue.

14. STDEV: Estimates standard deviation of a population sample in a column.

• Example: STDEV(Sales[Sales Amount]) calculates the standard deviation of sales amounts.

15. STDEVX: Estimates standard deviation of a population sample based on an expression.

• Example: STDEVX(Sales, Sales[Sales Amount] / Sales[Quantity]) calculates the standard deviation of price per unit.

16. STDEVP: Returns the standard deviation for an entire population.

• Example: STDEVP(Sales[Sales Amount]) calculates the standard deviation assuming the data is the entire population.

17. STDEVPX: Returns the population standard deviation of an expression.

• Example: STDEVPX(Sales, Sales[Sales Amount] / Sales[Quantity]) calculates the population standard deviation of price per unit.

18. VAR: Estimates variance for a population sample.

• Example: VAR(Sales[Sales Amount]) calculates the variance of sales amounts.

19. VARX: Estimates variance for a sample based on an expression.

• Example: VARX(Sales, Sales[Sales Amount] / Sales[Quantity]) calculates the variance of price per unit.

20. VARP: Returns variance for an entire population.

• Example: VARP(Sales[Sales Amount]) calculates the variance assuming the data is the entire population.

21. VARPX: Returns variance of a population based on an expression.

• Example: VARPX(Sales, Sales[Sales Amount] / Sales[Quantity]) calculates the population variance of price per unit.

Date and Time Functions

1. NOW: Returns the current date and time.

• Example: NOW() returns the current date and time.

2. TODAY: Returns the current date.

• Example: TODAY() returns the current date.

3. UTCNOW: Returns the current date and time in UTC.

• Example: UTCNOW() returns the current date and time in UTC.

4. UTCTODAY: Returns the current date in UTC.

• Example: UTCTODAY() returns the current date in UTC.

5. DATE: Creates a date from individual year, month, and day values.

• Example: DATE(2023, 12, 31) creates the date December 31, 2023.

6. TIME: Returns a time from individual hour, minute, and second values.

• Example: TIME(15, 30, 0) creates the time 15:30:00.

7. YEAR: Returns the year from a date.

• Example: YEAR(Sales[Order Date]) returns the year of the order date.

8. MONTH: Returns the month from a date.

• Example: MONTH(Sales[Order Date]) returns the month of the order date.

9. DAY: Returns the day from a date.

• Example: DAY(Sales[Order Date]) returns the day of the order date.

10. HOUR: Returns the hour from a time value.

• Example: HOUR(Sales[Order Time]) returns the hour of the order time.

11. MINUTE: Returns the minute from a time value.

• Example: MINUTE(Sales[Order Time]) returns the minute of the order time.

12. SECOND: Returns the second from a time value.

• Example: SECOND(Sales[Order Time]) returns the second of the order time.

13. DATEDIFF: Calculates the difference between two dates in units (days, months, etc.).

• Example: DATEDIFF(Sales[Order Date], Sales[Ship Date], DAY) calculates the number of days between order and ship dates.

14. DATEADD: Returns a date shifted by a given interval (e.g., months, years).

• Example: DATEADD(Sales[Order Date], 3, MONTH) adds 3 months to the order date.

15. EDATE: Returns a date shifted by a specified number of months.

• Example: EDATE(Sales[Order Date], 3) adds 3 months to the order date.

16. EOMONTH: Returns the last day of the month, shifted by a number of months.

• Example: EOMONTH(Sales[Order Date], 1) returns the last day of the next month.

Filter Functions

1. ALL: Removes filters from the specified column or table.

• Example: CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales)) calculates the total sales amount ignoring any filters.

2. ALLEXCEPT: Removes filters on all columns except the specified ones.

• Example: CALCULATE(SUM(Sales[Sales Amount]), ALLEXCEPT(Sales, Sales[Product])) calculates the total sales amount for a specific product, ignoring filters on other columns.

3. FILTER: Returns a table that contains only rows that meet the criteria.

• Example: FILTER(Sales, Sales[Sales Amount] > 1000) returns a table with orders having a sales amount greater than 1000.

4. CALCULATETABLE: Evaluates an expression in a modified filter context and returns a table.

• Example: CALCULATETABLE(VALUES(Sales[Product]), FILTER(Sales, Sales[Region] = "West")) returns a table with unique products from the West region.

5. CALCULATE: Evaluates an expression in a modified filter context.

• Example: CALCULATE(SUM(Sales[Sales Amount]), FILTER(Sales, Sales[Region] = "West")) calculates the total sales amount for the West region.

Logical Functions

1. IF: Returns one value if a condition is true and another if false.

• Example: IF(Sales[Sales Amount] > 1000, "High", "Low") returns "High" if the sales amount is greater than 1000, otherwise "Low".

2. IFERROR: Returns a value if an expression results in an error, otherwise returns the result.

• Example: IFERROR(DIVIDE(Sales[Sales Amount], Sales[Quantity]), 0) returns 0 if there's a division by zero error.

3. IFBLANK: Returns an alternate value if an expression returns blank.

• Example: IFBLANK(Sales[Region], "Unknown") returns "Unknown" if the region is blank.

4. AND: Checks if all conditions are true.

• Example: AND(Sales[Sales Amount] > 1000, Sales[Order Date] >= DATE(2023, 1, 1)) checks if the sales amount is greater than 1000 and the order date is after January 1, 2023.

5. OR: Checks if any condition is true.

• Example: OR(Sales[Region] = "West", Sales[Region] = "East") checks if the region is either West or East.

6. NOT: Reverses the result of a logical expression.

• Example: NOT(Sales[Sales Amount] > 1000) returns TRUE if the sales amount is not greater than 1000.

7. XOR: Returns TRUE if one of the conditions is true, but not both.

• Example: XOR(Sales[Region] = "West", Sales[Region] = "East") returns TRUE if the region is either West or East, but not both.

Mathematical Functions

1. ABS: Returns the absolute value of a number.

• Example: ABS(-10) returns 10.

2. CEILING: Rounds a number up to the nearest integer or multiple of significance.

• Example: CEILING(3.4, 1) returns 4.

3. FLOOR: Rounds a number down to the nearest integer or multiple of significance.

• Example: FLOOR(3.4, 1) returns 3.

4. ROUND: Rounds a number to the specified number of digits.

• Example: ROUND(3.14159, 2) returns 3.14.

5. INT: Returns the integer part of a number.

• Example: INT(3.14159) returns 3.

6. MOD: Returns the remainder of a division.

• Example: MOD(10, 3) returns 1.

7. PI: Returns the value of π (Pi).

• Example: PI() returns 3.14159...

8. POWER: Returns the result of a number raised to a power.

• Example: POWER(2, 3) returns 8.

9. QUOTIENT: Returns the integer portion of a division.

• Example: QUOTIENT(10, 3) returns 3.

10. SIGN: Returns the sign of a number (+1, 0, -1).

• Example: SIGN(-10) returns -1.

11. SQRT: Returns the square root of a number.

• Example: SQRT(16) returns 4.

12. TRUNC: Truncates a number to an integer by removing the fractional part.

• Example: TRUNC(3.14159) returns 3.

String Functions

1. LEN: Returns the number of characters in a string.

• Example: LEN("Hello") returns 5.

2. LOWER: Converts all letters in a text string to lowercase.

• Example: LOWER("Hello") returns "hello".

3. UPPER: Converts all letters in a text string to uppercase.

• Example: UPPER("hello") returns "HELLO".

4. TRIM: Removes extra spaces from text, leaving only single spaces.

• Example: TRIM(" Hello ") returns "Hello".

5. CLEAN: Removes non-printable characters from text.

• Example: CLEAN("Hello \n") removes the newline character.

6. CONCATENATE: Joins two or more text strings into one string.

• Example: CONCATENATE("Hello", " ", "world") returns "Hello world".

7. REPT: Repeats a text string a specified number of times.

• Example: REPT("Hello", 3) returns "HelloHelloHello".

8. SUBSTITUTE: Substitutes existing text with new text in a string.

• Example: SUBSTITUTE("Hello world", "world", "there") returns "Hello there".

9. SEARCH: Finds the position of a substring within a string (case-insensitive).

• Example: SEARCH("world", "Hello world") returns 7.

10. FIND: Finds the position of a substring within a string (case-sensitive).

• Example: FIND("world", "Hello world") returns 7.

Table Functions

1. ALL: Returns all rows in a table or all values in a column, ignoring filters.

• Example: CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales)) calculates the total sales amount ignoring any filters.

2. ALLEXCEPT: Removes all filters on a table except for one or more specified columns.

• Example: CALCULATE(SUM(Sales[Sales Amount]), ALLEXCEPT(Sales, Sales[Product])) calculates the total sales amount for a specific product, ignoring filters on other columns.

3. FILTER: Returns a table with rows that meet the specified condition.

• Example: FILTER(Sales, Sales[Sales Amount] > 1000) returns a table with orders having a sales amount greater than 1000.

4. CALCULATETABLE: Evaluates an expression in a modified filter context and returns a table.

• Example: CALCULATETABLE(VALUES(Sales[Product]), FILTER(Sales, Sales[Region] = "West")) returns a table with unique products from the West region.

5. SUMMARIZE: Returns a summary table for the requested columns.

• Example: SUMMARIZE(Sales, Sales[Region], "Total Sales", SUM(Sales[Sales Amount])) summarizes sales by region.

6. GROUPBY: Groups rows in a table based on one or more columns.

• Example: GROUPBY(Sales, Sales[Region], "Total Sales", SUM(Sales[Sales Amount])) groups sales by region.

7. ROLLUP: Creates subtotals for a column.

• Example: ROLLUP(Sales, Sales[Region], Sales[Product]) creates subtotals for regions and products.

8. ROLLUPGROUP: Allows grouping for rollup with finer control.

• Example: ROLLUPGROUP(Sales, Sales[Region], Sales[Product]) is similar to ROLLUP but provides more flexibility.

9. CROSSTABLE: Cross-tabulates the data into a pivot-like structure.

• Example: CROSSTABLE(Sales, Sales[Region], Sales[Product], SUM(Sales[Sales Amount])) creates a pivot table with regions as rows and products as columns.

Other Functions

1. BLANK: Returns a blank value.

• Example: BLANK() returns a blank value.

2. DIVIDE: Performs division and returns alternate results if the denominator is zero.

• Example: DIVIDE(Sales[Sales Amount], Sales[Quantity], 0) returns 0 if there's a division by zero error.

3. FORMAT: Converts a value to text in a specified format.

• Example: FORMAT(Sales[Order Date], "yyyy-MM-dd") formats the order date as "2023-12-31".

4. ISBLANK: Checks if a value is blank.

• Example: ISBLANK(Sales[Region]) returns TRUE if the region is blank.

5. ISERROR: Checks if a value results in an error.

• Example: ISERROR(DIVIDE(Sales[Sales Amount], Sales[Quantity])) returns TRUE if there's a division by zero error.

6. ISLOGICAL: Checks if a value is a logical type.

• Example: ISLOGICAL(TRUE) returns TRUE.

7. ISNUMBER: Checks if a value is a number.

• Example: ISNUMBER(10) returns TRUE.

8. ISTEXT: Checks if a value is text.

• Example: ISTEXT("Hello") returns TRUE.

9. USERELATIONSHIP: Specifies an inactive relationship to use in a calculation.

• Example: USERELATIONSHIP(Sales[Product-Key], Products[Product-Key]) specifies a relationship between Sales and Products tables.

Time Intelligence Functions

Time intelligence functions are specifically designed to work with date hierarchies in Power BI. They allow you to analyze data over time, calculate trends, and perform time-based calculations.

1. DATEADD: Adds or subtracts a specified interval from a date.

• Example: DATEADD(Sales[Order Date], 3, MONTH) adds 3 months to the order date.

2. DATESBETWEEN: Returns a table of dates within a specified range.

• Example: DATESBETWEEN(Sales[Order Date], DATE(2023, 1, 1), DATE(2023, 12, 31)) returns dates between January 1, 2023, and December 31, 2023.

3. DATEADDYEARS: Adds or subtracts a specified number of years from a date.

• Example: DATEADDYEARS(Sales[Order Date], 2) adds 2 years to the order date.

4. DATEADDMONTHS: Adds or subtracts a specified number of months from a date.

• Example: DATEADDMONTHS(Sales[Order Date], 3) adds 3 months to the order date.

5. DATEADDQUARTER: Adds or subtracts a specified number of quarters from a date.

• Example: DATEADDQUARTER(Sales[Order Date], 2) adds 2 quarters to the order date.

6. DATEADDWEEK: Adds or subtracts a specified number of weeks from a date.

• Example: DATEADDWEEK(Sales[Order Date], 2) adds 2 weeks to the order date.

7. DATEADDDAY: Adds or subtracts a specified number of days from a date.

• Example: DATEADDDAY(Sales[Order Date], 2) adds 2 days to the order date.

8. DATESYTD: Returns a table of dates from the beginning of the year to the current date.

• Example: DATESYTD(Sales[Order Date]) returns dates from the beginning of the year to the current date.

9. DATESMTD: Returns a table of dates from the beginning of the month to the current date.

• Example: DATESMTD(Sales[Order Date]) returns dates from the beginning of the month to the current date.

10. DATESQTD: Returns a table of dates from the beginning of the quarter to the current date.

• Example: DATESQTD(Sales[Order Date]) returns dates from the beginning of the quarter to the current date.

11. DATESWTD: Returns a table of dates from the beginning of the week to the current date.

• Example: DATESWTD(Sales[Order Date]) returns dates from the beginning of the week to the current date.

12. PARALLELPERIOD: Returns a period that is parallel to a specified period in a different year.

• Example: PARALLELPERIOD(Sales[Order Date], 1, YEAR) returns the same period in the previous year.

13. PREVIOUSYEAR: Returns the previous year from a specified date.

• Example: PREVIOUSYEAR(Sales[Order Date]) returns the previous year from the order date.

14. PREVIOUSMONTH: Returns the previous month from a specified date.

• Example: PREVIOUSMONTH(Sales[Order Date]) returns the previous month from the order date.

15. PREVIOUSQUARTER: Returns the previous quarter from a specified date.

• Example: PREVIOUSQUARTER(Sales[Order Date]) returns the previous quarter from the order date.

16. PREVIOUSWEEK: Returns the previous week from a specified date.

• Example: PREVIOUSWEEK(Sales[Order Date]) returns the previous week from the order date.

17. NEXTYEAR: Returns the next year from a specified date.

• Example: NEXTYEAR(Sales[Order Date]) returns the next year from the order date.

18. NEXTMONTH: Returns the next month from a specified date.

• Example: NEXTMONTH(Sales[Order Date]) returns the next month from the order date.

19. NEXTQUARTER: Returns the next quarter from a specified date.

• Example: NEXTQUARTER(Sales[Order Date]) returns the next quarter from the order date.

20. NEXTWEEK: Returns the next week from a specified date.

• Example: NEXTWEEK(Sales[Order Date]) returns the next week from the order date.

Statistical Functions

1. PERCENTILE.INC: Returns the kth percentile value in a data set.

• Example: PERCENTILE.INC(Sales[Sales Amount], 0.25) returns the 25th percentile of sales amounts.

2. PERCENTILE.EXC: Returns the kth percentile value in a data set, excluding the minimum and maximum values.

• Example: PERCENTILE.EXC(Sales[Sales Amount], 0.25) returns the 25th percentile of sales amounts, excluding the minimum and maximum values.

3. QUARTILE.INC: Returns the kth quartile value in a data set.

• Example: QUARTILE.INC(Sales[Sales Amount], 1) returns the first quartile (25th percentile) of sales amounts.

4. QUARTILE.EXC: Returns the kth quartile value in a data set, excluding the minimum and maximum values.

• Example: QUARTILE.EXC(Sales[Sales Amount], 1) returns the first quartile (25th percentile) of sales amounts, excluding the minimum and maximum values.

5. MEDIAN: Returns the median value in a data set.

• Example: MEDIAN(Sales[Sales Amount]) returns the median sales amount.

6. MODE.MULT: Returns the most frequent value in a data set, returning multiple values if there are ties.

• Example: MODE.MULT(Sales[Product]) returns the most frequent product.

7. MODE.SNGL: Returns the most frequent value in a data set, returning only the first value if there are ties.

• Example: MODE.SNGL(Sales[Product]) returns the most frequent product, returning only the first value if there are ties.

Financial Functions

1. FV: Calculates the future value of an investment.

• Example: FV(0.05, 10, -1000, 0, 1) calculates the future value of a $1000 investment at 5% interest rate for 10 years, compounded annually.

2. PV: Calculates the present value of an investment.

• Example: PV(0.05, 10, -1000, 0, 1) calculates the present value of a $1000 investment at 5% interest rate for 10 years, compounded annually.

3. PMT: Calculates the periodic payment for a loan.

• Example: PMT(0.05, 36, -10000, 0, 1) calculates the monthly payment for a $10,000 loan at 5% interest rate for 36 months.

4. RATE: Calculates the interest rate for a loan.

• Example: RATE(36, -100, 10000, 0, 1) calculates the interest rate for a 36-month loan with a monthly payment of $100 and a present value of $10,000.

5. NPER: Calculates the number of periods for a loan.

• Example: NPER(0.05, -100, 10000, 0, 1) calculates the number of months for a $10,000 loan at 5% interest rate with a monthly payment of $100.

6. IRR: Calculates the internal rate of return for an investment.

• Example: IRR({-1000, 200, 300, 400}) calculates the internal rate of return for an investment with initial cash flow of -$1000 and subsequent cash flows of $200, $300, and $400.

7. NPV: Calculates the net present value of an investment.

• Example: NPV(0.05, {-1000, 200, 300, 400}) calculates the net present value of an investment at 5% interest rate with initial cash flow of -$1000 and subsequent cash flows of $200, $300, and $400.

Expanded Explanation of DAX Functions with Examples (Continued)

Information Functions

1. USERNAME: Returns the name of the current user.

• Example: USERNAME() returns the name of the currently logged-in user.

2. USERPRINCIPALNAME: Returns the user principal name (UPN) of the current user.

• Example: USERPRINCIPALNAME() returns the UPN of the currently logged-in user.

3. PATH: Returns the current path in the data model.

• Example: PATH(Sales[Product]) returns the path of the Product column in the Sales table.

4. RELATEDTABLE: Returns a table related to the current table based on a specified relationship.

• Example: RELATEDTABLE(Products) returns the Products table related to the current table.

5. RELATED: Returns a value from a related table based on a specified relationship.

• Example: RELATED(Products[ProductName]) returns the product name from the related Products table.

6. HASONEVALUE: Checks if there is exactly one value in a column.

• Example: HASONEVALUE(Sales[Region]) returns TRUE if there is only one region selected.

7. EARLIER: Returns the value of an expression from an earlier row context.

• Example: EARLIER(Sales[Sales Amount]) returns the sales amount from the previous row.

8. EARLIERX: Returns the value of an expression from an earlier row context, with a specified filter.

• Example: EARLIERX(Sales, Sales[Region] = "West", Sales[Sales Amount]) returns the sales amount from the previous row where the region is "West".

9. LASTNONBLANK: Returns the last non-blank value in a column.

• Example: LASTNONBLANK(Sales[Sales Amount]) returns the last non-blank sales amount.

10. FIRSTNONBLANK: Returns the first non-blank value in a column.

• Example: FIRSTNONBLANK(Sales[Sales Amount]) returns the first non-blank sales amount.

Logical Functions (Continued)

11. ANDX: Evaluates an expression for each row in a table and returns TRUE if all expressions are true.

• Example: ANDX(Sales, Sales[Sales Amount] > 1000 && Sales[Order Date] >= DATE(2023, 1, 1)) checks if all rows meet both conditions.

12. ORX: Evaluates an expression for each row in a table and returns TRUE if at least one expression is true.

• Example: ORX(Sales, Sales[Region] = "West" || Sales[Region] = "East") checks if any row is in the West or East region.

Table Functions (Continued)

10. ADDCOLUMNS: Adds new columns to a table.

• Example: ADDCOLUMNS(Sales, "Total Revenue", Sales[Sales Amount] * Sales[Quantity]) adds a new column "Total Revenue" to the Sales table.

11. GENERATESERIES: Generates a series of numbers or dates.

• Example: GENERATESERIES(1, 10) generates a series of numbers from 1 to 10.

12. VALUES: Returns a table of unique values from a column.

• Example: VALUES(Sales[Product]) returns a table of unique products.

13. TOPN: Returns the top N rows of a table based on a specified column.

• Example: TOPN(10, Sales, Sales[Sales Amount], DESC) returns the top 10 sales orders.

14. BOTTOMN: Returns the bottom N rows of a table based on a specified column.

• Example: BOTTOMN(10, Sales, Sales[Sales Amount]) returns the bottom 10 sales orders.

15. RANKX: Ranks rows in a table based on an expression.

• Example: RANKX(Sales, Sales[Sales Amount], DESC) ranks sales orders by sales amount in descending order.