| Time Intelligent Functions | Time intelligence functions support calculations to compare and aggregate data over time periods, supporting days, months, quarters, and years. |
|---|---|
| Function | Description |
| CLOSINGBALANCEMONTH | Evaluates the specified expression for the date corresponding to the end of the current month after applying specified filters. |
| CLOSINGBALANCEQUARTER | Evaluates the specified expression for the date corresponding to the end of the current quarter after applying specified filters. |
| CLOSINGBALANCEYEAR | Evaluates the specified expression for the date corresponding to the end of the current year after applying specified filters. |
| DATEADD | Moves the given set of dates by a specified interval. |
| DATESBETWEEN | Returns the dates between two given dates. |
| DATESINPERIOD | Returns the dates from the given period. |
| DATESMTD | Returns a set of dates in the month up to the last date visible in the filter context. |
| DATESQTD | Returns a set of dates in the quarter up to the last date visible in the filter context. |
| DATESYTD | Returns a set of dates in the year up to the last date visible in the filter context. |
| ENDOFMONTH | Returns the end of month. |
| ENDOFQUARTER | Returns the end of quarter. |
| ENDOFYEAR | Returns the end of year. |
| FIRSTDATE | Returns first non blank date. |
| FIRSTNONBLANK | Returns the first value in the column for which the expression has a non blank value. |
| FIRSTNONBLANKVALUE | Returns the first non blank value of the expression that evaluated for the column. |
| LASTDATE | Returns last non blank date. |
| LASTNONBLANK | Returns the last value in the column for which the expression has a non blank value. |
| LASTNONBLANKVALUE | Returns the last non blank value of the expression that evaluated for the column. |
| NEXTDAY | Returns a next day. |
| NEXTMONTH | Returns a next month. |
| NEXTQUARTER | Returns a next quarter. |
| NEXTYEAR | Returns a next year. |
| OPENINGBALANCEMONTH | Evaluates the specified expression for the date corresponding to the end of the previous month after applying specified filters. |
| OPENINGBALANCEQUARTER | Evaluates the specified expression for the date corresponding to the end of the previous quarter after applying specified filters. |
| OPENINGBALANCEYEAR | Evaluates the specified expression for the date corresponding to the end of the previous year after applying specified filters. |
| PARALLELPERIOD | Returns a parallel period of dates by the given set of dates and a specified interval. |
| PREVIOUSDAY | Returns a previous day. |
| PREVIOUSMONTH | Returns a previous month. |
| PREVIOUSQUARTER | Returns a previous quarter. |
| PREVIOUSYEAR | Returns a previous year. |
| SAMEPERIODLASTYEAR | Returns a set of dates in the current selection from the previous year. |
| STARTOFMONTH | Returns the start of month. |
| STARTOFQUARTER | Returns the start of quarter. |
| STARTOFYEAR | Returns the start of year. |
| TOTALMTD | Evaluates the specified expression over the interval which begins on the first of the month and ends with the last date in the specified date column after applying specified filters. |
| TOTALQTD | Evaluates the specified expression over the interval which begins on the first day of the quarter and ends with the last date in the specified date column after applying specified filters. |
| TOTALYTD | Evaluates the specified expression over the interval which begins on the first day of the year and ends with the last date in the specified date column after applying specified filters. |
| Table Manipulation Functions | These functions manipulate and return tables |
|---|---|
| Function | Description |
| ADDCOLUMNS | Returns a table with new columns specified by the DAX expressions. |
| ADDMISSINGITEMS | Add the rows with empty measure values back. |
| CROSSJOIN | Returns a table that is a crossjoin of the specified tables. |
| CURRENTGROUP | Access to the (sub)table representing current group in GroupBy function. Can be used only inside GroupBy function. |
| DATATABLE | Returns a table with data defined inline. |
| DETAILROWS | Returns the table data corresponding to the DetailRows expression defined on the specified Measure. If a DetailRows expression is not defined then the entire table to which the Measure belongs is returned. |
| DISTINCT | Returns a one column table that contains the distinct (unique) values in a column, for a column argument. Or multiple columns with distinct (unique) combination of values, for a table expression argument. |
| EXCEPT | Returns the rows of left-side table which do not appear in right-side table. |
| FILTERS | Returns a table of the filter values applied directly to the specified column. |
| GENERATE | The second table expression will be evaluated for each row in the first table. Returns the crossjoin of the first table with these results. |
| GENERATEALL | The second table expression will be evaluated for each row in the first table. Returns the crossjoin of the first table with these results, including rows for which the second table expression is empty. |
| GENERATESERIES | Returns a table with one column, populated with sequential values from start to end. |
| GROUPBY | Creates a summary the input table grouped by the specified columns. |
| IGNORE | Tags a measure expression specified in the call to SUMMARIZECOLUMNS function to be ignored when determining the non-blank rows. |
| INTERSECT | Returns the rows of left-side table which appear in right-side table. |
| NATURALINNERJOIN | Joins the Left table with right table using the Inner Join semantics. |
| NATURALLEFTOUTERJOIN | Joins the Left table with right table using the Left Outer Join semantics. |
| NONVISUAL | Mark the filter as NonVisual. |
| ROLLUP | Identifies a subset of columns specified in the call to SUMMARIZE function that should be used to calculate subtotals. |
| ROLLUPADDISSUBTOTAL | Identifies a subset of columns specified in the call to SUMMARIZECOLUMNS function that should be used to calculate groups of subtotals. |
| ROLLUPGROUP | Identifies a subset of columns specified in the call to SUMMARIZE function that should be used to calculate groups of subtotals. |
| ROLLUPISSUBTOTAL | Pairs up the rollup groups with the column added by ROLLUPADDISSUBTOTAL. |
| ROW | Returns a single row table with new columns specified by the DAX expressions. |
| SELECTCOLUMNS | Returns a table with selected columns from the table and new columns specified by the DAX expressions. |
| SUBSTITUTEWITHINDEX | Returns a table which represents the semijoin of two tables supplied and for which the common set of columns are replaced by a 0-based index column. The index is based on the rows of the second table sorted by specified order expressions. |
| SUMMARIZE | Creates a summary of the input table grouped by the specified columns. |
| SUMMARIZECOLUMNS | Create a summary table for the requested totals over set of groups. |
| TOPN | Returns a given number of top rows according to a specified expression. |
| TOPNPERLEVEL | |
| TOPNSKIP | Retrieves a number of rows from a table efficiently, skipping a number of rows. Compared to TOPN, the TOPNSKIP function is less flexible, but much faster. |
| TREATAS | Treats the columns of the input table as columns from other tables.For each column, filters out any values that are not present in its respective output column. |
| UNION | Returns the union of the tables whose columns match. |
| VALUES | When a column name is given, returns a single column table of unique values. When a table name is given, returns a table with the same columns and all the rows of the table (including duplicates) with the additional blank row if present. |
| Date and Time Functions | Date and time functions help creating calculations based on dates and time. Many of the functions in DAX are similar to the Excel date and time functions. |
|---|---|
| Function | Description |
| CALENDAR | Returns a table with one column of all dates between StartDate and EndDate. |
| CALENDARAUTO | Returns a table with one column of dates calculated from the model automatically. |
| DATE | Returns the specified date in datetime format. |
| DATEDIFF | Returns the number of units (unit specified in Interval) between the input two dates. |
| DATEVALUE | Converts a date in the form of text to a date in datetime format. |
| DAY | Returns a number from 1 to 31 representing the day of the month. |
| EDATE | Returns the date that is the indicated number of months before or after the start date. |
| EOMONTH | Returns the date in datetime format of the last day of the month before or after a specified number of months. |
| HOUR | Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.). |
| MINUTE | Returns a number from 0 to 59 representing the minute. |
| MONTH | Returns a number from 1 (January) to 12 (December) representing the month. |
| NOW | Returns the current date and time in datetime format. |
| QUARTER | Returns a number from 1 (January-March) to 4 (October-December) representing the quarter. |
| SECOND | Returns a number from 0 to 59 representing the second. |
| TIME | Converts hours, minutes, and seconds given as numbers to a time in datetime format. |
| TIMEVALUE | Converts a time in text format to a time in datetime format. |
| TODAY | Returns the current date in datetime format. |
| UTCNOW | Returns the current date and time in datetime format expressed in Coordinated Universal Time (UTC). |
| UTCTODAY | Returns the current date in datetime format expressed in Coordinated Universal Time (UTC). |
| WEEKDAY | Returns a number identifying the day of the week of a date. The number is in a range 1-7 or 0-6 according to the choice of the ReturnType parameter. |
| WEEKNUM | Returns the week number in the year. |
| YEAR | Returns the year of a date as a four digit integer. |
| YEARFRAC | Returns the year fraction representing the number of whole days between start_date and end_date. |
| Text Functions | Text functions manipulate strings |
|---|---|
| Function | Description |
| COMBINEVALUES | Combines the given set of operands using a specified delimiter. |
| CONCATENATE | Joins two text strings into one text string. |
| CONCATENATEX | Evaluates expression for each row on the table, then return the concatenation of those values in a single string result, seperated by the specified delimiter. |
| EXACT | Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case-sensitive. |
| FIND | Returns the starting position of one text string within another text string. FIND is case-sensitive and accent-sensitive. |
| FIXED | Rounds a number to the specified number of decimals and returns the result as text with optional commas. |
| FORMAT | Converts a value to text in the specified number format. |
| LEFT | Returns the specified number of characters from the start of a text string. |
| LEN | Returns the number of characters in a text string. |
| LOWER | Converts all letters in a text string to lowercase. |
| MID | Returns a string of characters from the middle of a text string, given a starting position and length. |
| REPLACE | Replaces part of a text string with a different text string. |
| REPT | Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string. |
| RIGHT | Returns the specified number of characters from the end of a text string. |
| SEARCH | Returns the starting position of one text string within another text string. SEARCH is not case-sensitive, but it is accent-sensitive. |
| SUBSTITUTE | Replaces existing text with new text in a text string. |
| TRIM | Removes all spaces from a text string except for single spaces between words. |
| UNICHAR | Returns the Unicode character that is referenced by the given numeric value. |
| UNICODE | Returns the number (code point) corresponding to the first character of the text. |
| UPPER | Converts a text string to all uppercase letters. |
| VALUE | Converts a text string that represents a number to a number. |
| Financial Functions | Financial functions corresponding to Excel functions with the same name. |
|---|---|
| Function | Description |
| ACCRINT | Returns the accrued interest for a security that pays periodic interest. |
| ACCRINTM | Returns the accrued interest for a security that pays interest at maturity. |
| AMORDEGRC | Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is purchased in the middle of the accounting period, the prorated depreciation is taken into account. The function is similar to AMORLINC, except that a depreciation coefficient is applied in the calculation depending on the life of the assets. |
| AMORLINC | Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is purchased in the middle of the accounting period, the prorated depreciation is taken into account. |
| COUPDAYBS | Returns the number of days from the beginning of a coupon period until its settlement date. |
| COUPDAYS | Returns the number of days in the coupon period that contains the settlement date. |
| COUPDAYSNC | Returns the number of days from the settlement date to the next coupon date. |
| COUPNCD | Returns the next coupon date after the settlement date. |
| COUPNUM | Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon. |
| COUPPCD | Returns the previous coupon date before the settlement date. |
| CUMIPMT | Returns the cumulative interest paid on a loan between start_period and end_period. |
| CUMPRINC | Returns the cumulative principal paid on a loan between start_period and end_period. |
| DB | Returns the depreciation of an asset for a specified period using the fixed-declining balance method. |
| DDB | Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify. |
| DISC | Returns the discount rate for a security. |
| DOLLARDE | Converts a dollar price expressed as an integer part and a fraction part, such as 1.02, into a dollar price expressed as a decimal number. Fractional dollar numbers are sometimes used for securities prices. |
| DOLLARFR | Converts a dollar price expressed as a decimal number into a dollar price expressed as an integer part and a fraction part, such as 1.02. Fractional dollar numbers are sometimes used for securities prices. |
| DURATION | Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of cash flows, and is used as a measure of a bond price's response to changes in yield. |
| EFFECT | Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year. |
| FV | Calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, or a single lump sum payment. |
| INTRATE | Returns the interest rate for a fully invested security. |
| IPMT | Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. |
| ISPMT | Calculates the interest paid (or received) for the specified period of a loan (or investment) with even principal payments. |
| MDURATION | Returns the modified Macauley duration for a security with an assumed par value of $100. |
| NOMINAL | Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year. |
| NPER | Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. |
| ODDFPRICE | Returns the price per $100 face value of a security having an odd (short or long) first period. |
| ODDFYIELD | Returns the yield of a security that has an odd (short or long) first period. |
| ODDLPRICE | Returns the price per $100 face value of a security having an odd (short or long) last period. |
| ODDLYIELD | Returns the yield of a security that has an odd (short or long) last period. |
| PDURATION | Returns the number of periods required by an investment to reach a specified value. |
| PMT | Calculates the payment for a loan based on constant payments and a constant interest rate. |
| PPMT | Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate. |
| PRICE | Returns the price per $100 face value of a security that pays periodic interest. |
| PRICEDISC | Returns the price per $100 face value of a discounted security. |
| PRICEMAT | Returns the price per $100 face value of a security that pays interest at maturity. |
| PV | calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either periodic, constant payments (such as a mortgage or other loan), or a future value that's your investment goal. |
| RATE | Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns an error. |
| RECEIVED | Returns the amount received at maturity for a fully invested security. |
| RRI | Returns an equivalent interest rate for the growth of an investment. |
| SLN | Returns the straight-line depreciation of an asset for one period. |
| SYD | Returns the sum-of-years' digits depreciation of an asset for a specified period. |
| TBILLEQ | Returns the bond-equivalent yield for a Treasury bill. |
| TBILLPRICE | Returns the price per $100 face value for a Treasury bill. |
| TBILLYIELD | Returns the yield for a Treasury bill. |
| VDB | Returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance method or some other method you specify. VDB stands for variable declining balance. |
| XIRR | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. |
| XNPV | Returns the net present value for a schedule of cash flows. |
| YIELD | Returns the yield on a security that pays periodic interest. Use YIELD to calculate bond yield. |
| YIELDDISC | Returns the annual yield for a discounted security. |
| YIELDMAT | Returns the annual yield of a security that pays interest at maturity. |
| Information Functions | Information functions provide information about data type or filter context of the argument provided. |
|---|---|
| Function | Description |
| COLUMNSTATISTICS | Provides statistics regarding every column in every table in the model. |
| CONTAINS | Returns TRUE if there exists at least one row where all columns have specified values. |
| CONTAINSROW | Returns TRUE if there exists at least one row where all columns have specified values. |
| CONTAINSSTRING | Returns TRUE if one text string contains another text string. CONTAINSSTRING is not case-sensitive, but it is accent-sensitive. |
| CONTAINSSTRINGEXACT | Returns TRUE if one text string contains another text string. CONTAINSSTRINGEXACT is case-sensitive and accent-sensitive. |
| CUSTOMDATA | Returns the value of the CustomData connection string property if defined; otherwise, BLANK(). |
| HASONEFILTER | Returns true when the specified table or column has one and only one value resulting from direct filter(s). |
| HASONEVALUE | Returns true when there's only one value in the specified column. |
| ISAFTER | Returns true if the list of Value1 parameters compares strictly after the list of Value2 parameters. |
| ISBLANK | Checks whether a value is blank, and returns TRUE or FALSE. |
| ISCROSSFILTERED | Returns true when the specified table or column is crossfiltered. |
| ISEMPTY | Returns true if the specified table or table-expression is Empty. |
| ISERROR | Checks whether a value is an error, and returns TRUE or FALSE. |
| ISEVEN | Returns TRUE if number is even, or FALSE if number is odd. |
| ISFILTERED | Returns true when there are direct filters on the specified column. |
| ISINSCOPE | Returns true when the specified column is the level in a hierarchy of levels. |
| ISLOGICAL | Checks whether a value is a logical value (TRUE or FALSE), and returns TRUE or FALSE. |
| ISNONTEXT | Checks whether a value is not text (blank cells are not text), and returns TRUE or FALSE. |
| ISNUMBER | Checks whether a value is a number, and returns TRUE or FALSE. |
| ISODD | Returns TRUE if number is odd, or FALSE if number is even. |
| ISONORAFTER | The IsOnOrAfter function is a boolean function that emulates the behavior of Start At clause and returns true for a row that meets all the conditions mentioned as parameters in this function. |
| ISSELECTEDMEASURE | Returns true if one of the specified measures is currently being evaluated. |
| ISSUBTOTAL | Returns TRUE if the current row contains a subtotal for a specified column and FALSE otherwise. |
| ISTEXT | Checks whether a value is text, and returns TRUE or FALSE. |
| NAMEOF | Returns the name of a column or measure. |
| SELECTEDMEASURE | Returns the measure that is currently being evaluated. |
| SELECTEDMEASUREFORMATSTRING | Returns format string for the measure that is currently being evaluated. |
| SELECTEDMEASURENAME | Returns name of the measure that is currently being evaluated. |
| USERCULTURE | Returns the culture code for the user, based on their operating system or browser settings. |
| USERNAME | Returns the domain name and user name of the current connection with the format of domain-name\user-name. |
| USEROBJECTID | Returns the current user's Object ID from Azure AD for Azure Analysis Server and the current user's SID for on-premise Analysis Server. |
| USERPRINCIPALNAME | Returns the user principal name. |
| Aggregation Functions | Aggregation functions return a scalar value applying an aggregation function to a column or to an expression evaluated by iterating a table expression. |
|---|---|
| Function | Description |
| APPROXIMATEDISTINCTCOUNT | Counts the approximate number of distinct values in a column. Currently this function is only available on a DirectQuery table from Azure SQL or Azure SQL Data Warehouse. |
| AVERAGE | Returns the average (arithmetic mean) of all the numbers in a column. |
| AVERAGEA | Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values. |
| AVERAGEX | Calculates the average (arithmetic mean) of a set of expressions evaluated over a table. |
| COUNT | Counts the number of rows in the table where the specified column has a non-blank value. |
| COUNTA | Counts the number of values in a column. |
| COUNTAX | Counts the number of values which result from evaluating an expression for each row of a table. |
| COUNTBLANK | Counts the number of blanks in a column. |
| COUNTROWS | Counts the number of rows in a table. |
| COUNTX | Counts the number of values which result from evaluating an expression for each row of a table. |
| DISTINCTCOUNT | Counts the number of distinct values in a column. |
| DISTINCTCOUNTNOBLANK | Counts the number of distinct values in a column. |
| MAX | Returns the largest value in a column, or the larger value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order. |
| MAXA | Returns the largest value in a column. Does not ignore logical values and text. |
| MAXX | Returns the largest value that results from evaluating an expression for each row of a table. Strings are compared according to alphabetical order. |
| MIN | Returns the smallest value in a column, or the smaller value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order. |
| MINA | Returns the smallest value in a column. Does not ignore logical values and text. |
| MINX | Returns the smallest value that results from evaluating an expression for each row of a table. Strings are compared according to alphabetical order. |
| PRODUCT | Returns the product of given column reference. |
| PRODUCTX | Returns the product of an expression values in a table. |
| SUM | Adds all the numbers in a column. |
| SUMX | Returns the sum of an expression evaluated for each row in a table. |
| Filter Functions | Filter functions manipulate table and filter contexts. |
|---|---|
| Function | Description |
| ALL | Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. |
| ALLCROSSFILTERED | Clear all filters which are applied to the specified table. |
| ALLEXCEPT | Returns all the rows in a table except for those rows that are affected by the specified column filters. |
| ALLNOBLANKROW | Returns all the rows except blank row in a table, or all the values in a column, ignoring any filters that might have been applied. |
| ALLSELECTED | Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied inside the query, but keeping filters that come from outside. |
| CALCULATE | Evaluates an expression in a context modified by filters. |
| CALCULATETABLE | Evaluates a table expression in a context modified by filters. |
| FILTER | Returns a table that has been filtered. |
| KEEPFILTERS | Changes the CALCULATE and CALCULATETABLE function filtering semantics. |
| LOOKUPVALUE | Retrieves a value from a table. |
| REMOVEFILTERS | Clear filters from the specified tables or columns. |
| SELECTEDVALUE | Returns the value when there's only one value in the specified column, otherwise returns the alternate result. |