Power-Bi provides a power tool DAX functions, a formula language, that helps in manipulating the data and tables. It includes functions, operators, constants, and values to perform advanced calculations and queries on the tables and columns. Additionally, it assists in generating new information from the existing data. It is an all-encompassing service for data modeling and data analysis.
Since I am new to the Microsoft Power Bi tool, I often forget the syntax of DAX functions, and it is frustrating when this happens. The Power-Bi does provide inline suggestions for syntax nevertheless, it can be inconvenient because of its intricacy. I always prefer syntax in simple language, as making reports can sometimes be an exacting task because of its complexity. Moreover, I want DAX functions to be at my aid rather than making my analysis task strenuous.
Thus, I have prepared this cheat sheet so that I can always refer to it whenever I am stuck with the syntax. As I know what DAX function I want to use, but I often cannot recall the syntax. Hence, using the cheat sheet makes my activity more manageable.
Note: It is just a consolidated version of official Microsoft documentation.
Date and time functions
| Function | Description |
| CALENDAR(, ) | Generates a column of continuous sets of dates, inclusive of the specified dates. |
| CALENDARAUTO([fiscal_year_end_month]) | Generates a column of continuous sets of dates, automatically based on data in model. |
| DATE(, , ) | Returns the specified date in the datetime format. |
| DATEDIFF(, , ) | Returns the count of interval boundaries crossed between two dates. |
| DATEVALUE(date_text) | Converts a date from the text form to datetime format. |
| DAY() | Returns the day of the month, a number from 1 to 31. |
| EDATE(, ) | Returns the date that is the indicated number of months before or after the start date. |
| EOMONTH(, ) | Returns date in the 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.). The generated text value is automatically parsed and converted to a date/time value. |
| MINUTE() | Returns the minute as a number from 0 to 59, given a date and time value. |
| MONTH() | Returns the month as a number from 1 (January) to 12 (December). |
| NOW() | Returns the current date and time in datetime format. |
| QUARTER() | Returns the quarter as a number from 1 (January – March) to 4 (October – December). |
| SECOND( | Returns the seconds of a time value, as a number from 0 to 59. |
| TIME(hour, minute, second) | Converts hours, minutes, and seconds given as numbers to a time in datetime format. |
| TIMEVALUE(time_text) | Converts a time in text format to a time in datetime format. |
| TODAY() | Returns the current date. |
| UTCNOW() | Returns the current UTC date and time. |
| UTCTODAY() | Returns the current UTC date. |
| WEEKDAY(, ) | Returns a number from 1 to 7 identifying the day of the week of a date. By default week starts on Sunday(1) and ends on Saturday(7). |
| WEEKNUM(, ) | Returns the week number for the given date and year according to the return_type value. The week number indicates where the week falls numerically within a year. By default week begins on Sunday(1). |
| YEAR() | Returns the year of a date as a four digit integer in the range 1900-9999. |
| YEARFRAC(, , ) | Calculates the fraction of the year represented by the number of whole days between two dates. |
Filter Function
| 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 a table. |
| ALLEXCEPT( ,[,[,…]]) | Removes all context filters in the table except filters that have been applied to the specified columns. |
| ALLNOBLANKROW( { | [, [, [,…]]]} ) | From the parent table of a relationship, returns all rows but the blank row, or all distinct values of a column but the blank row, and disregards any context filters that might exist. |
| ALLSELECTED([ | [ [, [,…]]]] ) | The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. |
| CALCULATE([, [, [, …]]]) | Evaluates an expression in a modified filter context. |
| CALCULATETABLE([, [, [, …]]]) | Evaluates a table expression in a modified filter context. Filters are optional and it can be Boolean expression, table filter expressions and filter modifier functions. |
| EARLIER(, ) | Returns the current value of the specified column in an outer evaluation pass of the mentioned column. |
| EARLIEST() | Returns the current value of the specified column in an outer evaluation pass of the specified column. |
| FILTER( ,) | Returns a table that represents a subset of another table or expression. |
| KEEPFILTERS() | Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function. |
| LOOKUPVALUE( , , [, , ]… [, ] ) | Returns the value for the row that meets all criteria specified by one or more search conditions. If there’s no match that satisfies all the search values, BLANK or alternateResult (if supplied) is returned. In other words, the function won’t return a lookup value if only some of the criteria match. If multiple rows match the search values and in all cases the result_column values are identical, then that value is returned. However, if result_column returns different values, an error or alternateResult (if supplied) is returned. |
| REMOVEFILTERS([ | [, [, [,…]]]]) | Clear filters from the specified tables or columns. |
| SELECTEDVALUE([, ]) | Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult. |
Logical Functions
| Function | Description |
| AND(,) | Performs logical AND. |
| COALESCE(, [, ]…) | Returns the first expression that does not evaluate to BLANK. If all expressions evaluate to BLANK, BLANK is returned. |
| FALSE() | Returns the logical value FALSE. |
| IF(, [, ]) | Performs the logical condition – if and else. |
| IFERROR(value, value_if_error) | Evaluates an expression and returns a specified value if the expression returns an error; otherwise returns the value of the expression itself. |
| NOT() | Performs logical NOT |
| OR(,) | Performs logical OR |
| SWITCH(, , [, , ]…[, ]) | Evaluates an expression against a list of values and returns one of multiple possible result expressions. |
| TRUE() | Returns the logical value TRUE. |
Financial Functions
| Functions | 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. 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. 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. |
| 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. |
| 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. |
| 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 coupon 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. |
| 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. |
| RATE(, , [, [, [, ]]]) | Returns the interest rate per period of an annuity. |
| 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( , , , [guess]) | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. |
| XNPV( , , , ) | Returns the present value for a schedule of cash flows that is not necessarily periodic. |
| YIELD(, , , , , [, ]) | Returns the yield on a security that pays periodic interest. |
| YIELDDISC(, , , [, ]) | Returns the annual yield for a discounted security. |
| YIELDMAT(, , , , [, ]) | Returns the annual yield of a security that pays interest at maturity. |
Information Function
| Functions | Description |
| CONTAINS( , , [, , ]…) | Returns true if values for all referred columns exist, or are contained, in those columns; otherwise, the function returns false. |
| CONTAINSROW(, [, , …]) | Returns TRUE if a row of values exists or contained in a table, otherwise returns FALSE. |
| CONTAINSSTRING(, ) | Returns TRUE or FALSE indicating whether one string contains another string. |
| CONTAINSSTRINGEXACT(, ) | Returns TRUE or FALSE indicating whether one string contains another string. |
| CUSTOMDATA() | Returns the content of the CustomData property in the connection string. |
| HASONEFILTER() | Returns TRUE when the number of directly filtered values on columnName is one; otherwise returns FALSE. |
| HASONEVALUE() | Returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise it is FALSE. |
| ISBLANK() | Checks whether a value is blank, and returns TRUE or FALSE. |
| ISCROSSFILTERED() | Returns TRUE when the columnName or another column in the same or related table is being filtered. |
| ISEMPTY() | Checks if a table is empty. |
| ISERROR() | Checks whether a value is an error, and returns TRUE or FALSE. |
| ISEVEN(number) | Returns TRUE if number is even, or FALSE if number is odd. |
| ISFILTERED() | Returns TRUE when columnName is being filtered directly. If there is no filter on the column or if the filtering happens because a different column in the same table or in a related table is being filtered then the function returns FALSE. |
| 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 if 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(number) | Returns TRUE if number is odd, or FALSE if number is even. |
| ISONORAFTER(, [, sort_order [, , [, sort_order]]…) | A boolean function that emulates the behavior of a ‘Start At’ clause and returns true for a row that meets all of the condition parameters. |
| ISSELECTEDMEASURE( M1, M2, … ) | Used by expressions for calculation items to determine the measure that is in context is one of those specified in a list of measures. |
| ISSUBTOTAL() | Used by expressions for calculation items to determine the measure that is in context is one of those specified in a list of measures. |
| ISTEXT() | Checks if a value is text, and returns TRUE or FALSE. |
| NONVISUAL() | Marks a value filter in a SUMMARIZECOLUMNS expression as non-visual. This function can only be used within a SUMMARIZECOLUMNS expression. |
| SELECTEDMEASURE() | Used by expressions for calculation items to reference the measure that is in context. |
| SELECTEDMEASUREFORMATSTRING() | Used by expressions for calculation items to retrieve the format string of the measure that is in context. |
| SELECTEDMEASURENAME() | Used by expressions for calculation items to determine the measure that is in context by name. |
| USERNAME() | Returns the domain name and username from the credentials given to the system at connection time. |
| USEROBJECTID() | Returns the current user’s Object ID from Azure AD or security identifier (SID). |
| USERPRINCIPALNAME() | Returns the user principal name. |
Math & Trigonometric Functions
| Function | Description |
| ABS() | Returns absolute value of a number |
| ACOS(number) | Returns arccosine or inverse cosine of a number, in radians (0 to ). |
| ACOSH(number) | Returns the inverse hyperbolic cosine of a number. The number must be greater than or equal to 1. |
| ACOT(number) | Returns the principal value of the arccotangent, or inverse cotangent of a number. |
| ACOTH(number) | Returns the inverse hyperbolic cotangent of a number. |
| ASIN(number) | Returns the arcsine, or inverse sine, of a number. The returned angle is given in radians ( -π/2 to π/2). |
| ASINH(number) | Returns the inverse hyperbolic sine of a number. |
| ATAN(number) | Returns the arctangent, or inverse tangent, of a number. The returned angle is given in radians ( -π/2 to π/2). |
| ATANH(number) | Returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1 (excluding -1 and 1). |
| CEILING(, ) | Rounds a number up, to the nearest integer or to the nearest multiple of significance. |
| COMBIN(number, number_chosen) | Returns the number of combinations for a given number of items. |
| COMBINA(number, number_chosen) | Returns the number of combinations (with repetitions) for a given number of items. |
| CONVERT(, ) | Converts an expression of one data type to another. |
| COS(number) | Returns the cosine of the given angle. |
| COSH(number) | Returns the hyperbolic cosine of a number. |
| CURRENCY() | Evaluates the argument and returns the result as currency data type. |
| DEGREES(angle) | Converts radians into degrees. |
| DIVIDE(, [,]) | Performs division and returns alternate result or BLANK() on division by 0. |
| EVEN(number) | Returns number rounded up to the nearest even integer. |
| EXP(number) | Returns e raised to the power of a given number. |
| FACT(number) | Returns the factorial of a number. |
| FLOOR(, ) | Rounds a number down, toward zero, to the nearest multiple of significance. |
| GCD(number1, [number2], …) | Returns the greatest common divisor of two or more integers. |
| INT(number) | Rounds a number down to the nearest integer. |
| ISO.CEILING([, ]) | Rounds a number up, to the nearest integer or to the nearest multiple of significance. |
| LCM(number1, [number2], …) | Returns the least common multiple of integers. |
| LN(number) | Returns the natural logarithm of a number. |
| LOG(,) | Returns the logarithm of a number to the specified base. |
| LOG10() | Returns the base-10 logarithm of a number. |
| MOD(, ) | Returns the remainder after a number is divided by a divisor. |
| MROUND(, ) | Returns a number rounded to the desired multiple. |
| ODD(number) | Returns number rounded up to the nearest odd integer. |
| PI() | Returns the value of Pi, 3.14159265358979, accurate to 15 digits. |
| POWER(, ) | Returns the result of a number raised to a power. |
| PRODUCT() | Returns the product of the numbers in a column. |
| PRODUCTX( , ) | Returns the product of an expression evaluated for each row in a table. |
| QUOTIENT(, ) | Performs division and returns only the integer portion of the division result. |
| RADIANS(angle) | Converts degrees to radians. |
| RAND() | Returns a random number greater than or equal to 0 and less than 1, evenly distributed. |
| RANDBETWEEN(,) | Returns a random number in the range between two numbers you specify. |
| ROUND(, ) | Rounds a number to the specified number of digits. |
| ROUNDDOWN(, ) | Rounds a number down, toward zero. |
| ROUNDUP(, ) | Rounds a number up, away from 0 (zero). |
| SIGN() | Determines the sign of a number, the result of a calculation, or a value in a column. The function returns 1 if the number is positive, 0 (zero) if the number is zero, or -1 if the number is negative. |
| SQRT(number) | Returns the square root of a number. |
| SUM() | Adds all the numbers in a column. |
| SUMX( , ) | Returns the sum of an expression evaluated for each row in a table. |
| TRUNC(,) | Truncates a number to an integer by removing the decimal, or fractional, part of the number. |
Other Function
| Functions | Description |
| BLANK() | Returns a blank. |
| ERROR() | Raises an error with an error message. |
Parent and child Functions
| Functions | Description |
| PATH(, ) | Returns a delimited text string with the identifiers of all the parents of the current identifier, starting with the oldest and continuing until current. |
| PATHCONTAINS(, ) | Returns TRUE if the specified item exists within the specified path. |
| PATHITEM(, [, ]) | Returns the item at the specified position from a string resulting from evaluation of a PATH function. Positions are counted from left to right. |
| PATHITEMREVERSE(, [, ]) | Returns the item at the specified position from a string resulting from evaluation of a PATH function. Positions are counted backwards from right to left. |
| PATHLENGTH() | Returns the number of parents to the specified item in a given PATH result, including self. |
Relationship Functions
| Function | Description |
| CROSSFILTER(, , ) | Specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns. |
| RELATED() | Returns a related value from another table. |
| RELATEDTABLE() | Evaluates a table expression in a context modified by the given filters. |
| USERELATIONSHIP(,) | Specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2. |
Statistical Functions
| Function | Description |
| APPROXIMATEDISTINCTCOUNT() | Returns the approximate number of rows that contain distinct values in a column. |
| 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. |
| BETA.DIST(x,alpha,beta,cumulative,[A],[B]) | Returns the beta distribution. |
| BETA.INV(probability,alpha,beta,[A],[B]) | Returns the inverse of the beta cumulative probability density function (BETA.DIST). |
| CHISQ.DIST(, , ) | Returns the chi-squared distribution. |
| CHISQ.DIST.RT(, ) | Returns the right-tailed probability of the chi-squared distribution. |
| CHISQ.INV(probability,deg_freedom) | Returns the inverse of the left-tailed probability of the chi-squared distribution. |
| CHISQ.INV.RT(probability,deg_freedom) | Returns the inverse of the right-tailed probability of the chi-squared distribution. |
| CONFIDENCE.NORM(alpha,standard_dev,size) | Returns a range of values in the confidence interval. |
| CONFIDENCE.T(alpha,standard_dev,size) | Returns the confidence interval for a population mean, using a Student’s t distribution. |
| COT() | Returns the cotangent of an angle specified in radians. |
| COTH() | Returns the hyperbolic cotangent of a hyperbolic angle. |
| COUNT() | The COUNT function counts the number of cells in a column that contain non-blank values. |
| COUNTA() | The COUNTA function counts the number of cells in a column that are not empty. |
| COUNTAX( ,) | The function counts nonblank results when evaluating the result of an expression over a table. |
| COUNTBLANK() | Counts the number of blank cells in a column. |
| COUNTROWS( ) | The function counts the number of rows in the specified table, or in a table defined by an expression. |
| COUNTX( ,) | Counts the number of rows that contain a non-blank value or an expression that evaluates to a non-blank value, when evaluating an expression over a table. |
| DISTINCTCOUNT() | Counts the number of distinct values in a column. |
| EXPON.DIST(x,lambda,cumulative) | Returns the exponential distribution. |
| GEOMEAN() | Returns the geometric mean of the numbers in a column. |
| GEOMEANX( , ) | Returns the geometric mean of an expression evaluated for each row in a table. |
| MAX() | Returns the largest value in a column |
| MAXA() | Returns the largest value in a column(used for number or dates). |
| MAXX( ,) | Evaluates an expression for each row of a table and returns the largest value. |
| MEDIAN() | Returns the median of numbers in a column. |
| MEDIANX( , ) | Returns the median number of an expression evaluated for each row in a table. |
| MIN() | Returns the smallest value in a column, or between two scalar expressions. |
| MINA() | Returns the smallest value in a column. |
| MINX( , < expression>) | Returns the smallest value that results from evaluating an expression for each row of a table. |
| NORM.DIST(X, Mean, Standard_dev, Cumulative) | Returns the normal distribution for the specified mean and standard deviation. |
| NORM.INV(Probability, Mean, Standard_dev) | The inverse of the normal cumulative distribution for the specified mean and standard deviation. |
| NORM.S.DIST(Z, Cumulative) | Returns the standard normal distribution (has a mean of zero and a standard deviation of one). |
| NORM.S.INV(Probability) | Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one. |
| PERCENTILE.EXC(, ) | Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive. |
| PERCENTILE.INC(, ) | Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. |
| PERCENTILEX.EXC( , , k) | Returns the percentile number of an expression evaluated for each row in a table. |
| PERCENTILEX.INC( , ;, k) | Returns the percentile number of an expression evaluated for each row in a table. |
| PERMUT(number, number_chosen) | Returns the number of permutations for a given number of objects that can be selected from number objects. |
| POISSON.DIST(x,mean,cumulative) | Returns the Poisson distribution. |
| RANK.EQ(, [, ]) | Returns the ranking of a number in a list of numbers. |
| RANKX( , [, [, [, ]]]) | Returns the ranking of a number in a list of numbers for each row in the table argument. |
| SAMPLE(, , , [[, , []]…]) | Returns a sample of N rows from the specified table. |
| SIN(number) | Returns the sine of the given angle. |
| SINH(number) | Returns the hyperbolic sine of a number. |
| STDEV.S() | Returns the standard deviation of a sample population. |
| STDEV.P() | Returns the standard deviation of the entire population. |
| STDEVX.S( , ) | Returns the standard deviation of a sample population. |
| STDEVX.P( , ) | Returns the standard deviation of the entire population. |
| SQRTPI(number) | Returns the square root of (number * pi). |
| T.DIST(X,Deg_freedom,Cumulative) | Returns the Student’s left-tailed t-distribution. |
| T.DIST.2T(X,Deg_freedom) | Returns the two-tailed Student’s t-distribution. |
| T.DIST.RT(X,Deg_freedom) | Returns the right-tailed Student’s t-distribution. |
| T.INV(Probability,Deg_freedom) | Returns the left-tailed inverse of the Student’s t-distribution. |
| T.INV.2T(Probability,Deg_freedom) | Returns the two-tailed inverse of the Student’s t-distribution. |
| TAN(number) | Returns the tangent of the given angle. |
| TANH(number) | Returns the hyperbolic tangent of a number. |
| VAR.S() | Returns the variance of a sample population. |
| VAR.P() | Returns the variance of the entire population. |
| VARX.S( , ) | Returns the variance of a sample population. |
| VARX.P( , ) | Returns the variance of the entire population. |
Table Manipulation Function
| Functions | Description |
| ADDCOLUMNS( , , [, , ]…) | Adds calculated columns to the given table or table expression. |
| ADDMISSINGITEMS ( [ [, [, … ] ] ], [, [, [] [, [, [] [, … ] ] ] ] ] ] ) | Adds rows with empty values to a table returned by SUMMARIZECOLUMNS. |
| CROSSJOIN( ,[,]…) | Returns a table that contains the Cartesian product of all rows from all tables in the arguments. The columns in the new table are all the columns in all the argument tables. |
| CURRENTGROUP ( ) | Returns a set of rows from the table argument of a GROUPBY expression that belong to the current row of the GROUPBY result. |
| DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2…, {{Value1, Value2…}, {ValueN, ValueN+1…}…}) | Provides a mechanism for declaring an inline set of data values. |
| DETAILROWS([Measure]) | Evaluates a Detail Rows Expression defined for a measure and returns the data. |
| DISTINCT() | Returns a one-column table that contains the distinct values from the specified column. |
| DISTINCT( ) | Returns a table by removing duplicate rows from another table or expression. |
| EXCEPT(, | Returns the rows of one table which do not appear in another table. |
| FILTERS() | Returns the values that are directly applied as filters to columnName. |
| GENERATE(, ) | Returns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1. |
| GENERATEALL(, ) | Returns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1. |
| GENERATESERIES(, [, ]) | Returns a single column table containing the values of an arithmetic series |
| GROUPBY ( [, [, [, …]]] [, , [, , [, …]]]) | The GROUPBY function is similar to the SUMMARIZE function. However, GROUPBY does not do an implicit CALCULATE for any extension columns that it adds. GROUPBY permits a new function, CURRENTGROUP, to be used inside aggregation functions in the extension columns that it adds. GROUPBY is used to perform multiple aggregations in a single table scan. |
| IGNORE() | Modifies the behavior of the SUMMARIZECOLUMNS function by omitting specific expressions from the BLANK/NULL evaluation. |
| INTERSECT(, ) | Returns the row intersection of two tables, retaining duplicates. |
| NATURALINNERJOIN(, ) | Performs an inner join of a table with another table. |
| NATURALLEFTOUTERJOIN(, ) | Performs an inner join of a table with another table. |
| ROLLUP ( [, [, … ] ] ) | Modifies the behavior of the SUMMARIZE function by adding rollup rows to the result on columns defined by the groupBy_columnName parameter. This function can only be used within a SUMMARIZE expression. |
| ROLLUPADDISSUBTOTAL ( [], , [, [] [, , [, [] [, … ] ] ] ] ) | Modifies the behavior of the SUMMARIZECOLUMNS function by adding rollup/subtotal rows to the result based on the groupBy_columnName columns. This function can only be used within a SUMMARIZECOLUMNS expression. |
| ROLLUPGROUP ( [, [, … ] ] ) | Modifies the behavior of the SUMMARIZE and SUMMARIZECOLUMNS functions by adding rollup rows to the result on columns defined by the the groupBy_columnName parameter. This function can only be used within a SUMMARIZE or SUMMARIZECOLUMNS expression. |
| ROLLUPISSUBTOTAL ( [], , [, [] [, , [, [] [, … ] ] ] ] ) | Pairs rollup groups with the column added by ROLLUPADDISSUBTOTAL. This function can only be used within an ADDMISSINGITEMS expression. |
| ROW(, [[,, ]…]) | Returns a table with a single row containing values that result from the expressions given to each column. |
| SELECTCOLUMNS( , , [, , ]…) | Adds calculated columns to the given table or table expression. |
| SUMMARIZE ( , [, ]…[, , ]…) | Returns a summary table for the requested totals over a set of groups. |
| SUMMARIZECOLUMNS( [, < groupBy_columnName >]…, []…[, , ]…) | Returns a summary table over a set of groups. |
| { , , … } { ( , , … ), ( , , … ), … } | Returns a table of one or more columns. |
| TOPN(, , , [[, , []]…]) | Returns the top N rows of the specified table. |
| TREATAS(table_expression, [, [, [,…]]]} ) | Applies the result of a table expression as filters to columns from an unrelated table. |
| UNION(, [,]…) | Creates a union (join) table from a pair of tables. |
| VALUES() | When the input parameter is a column name, returns a one-column table that contains the distinct values from the specified column. Duplicate values are removed and only unique values are returned. A BLANK value can be added. When the input parameter is a table name, returns the rows from the specified table. Duplicate rows are preserved. A BLANK row can be added. |
Text Functions
| Function | Description |
| COMBINEVALUES(, , [, ]…) | Joins two or more text strings into one text string. |
| CONCATENATE(, ) | Joins two text strings into one text string. |
| CONCATENATEX( , , [delimiter]) | Concatenates the result of an expression evaluated for each row in a table. |
| EXACT(,) | Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. |
| FIND(, [, [][, ]]) | Returns the starting position of one text string within another text string. FIND is case-sensitive. |
| FIXED(, , ) | Rounds a number to the specified number of decimals and returns the result as text. |
| FORMAT(, ) | Converts a value to text according to the specified 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(, , , ) | REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string. |
| REPT(, ) | Repeats text a given number of times. |
| RIGHT(, ) | RIGHT returns the last character or characters in a text string, based on the number of characters you specify. |
| SEARCH(, [, [][, ]]) | Returns the number of the character at which a specific character or text string is first found, reading left to right. Search is case-insensitive and accent sensitive. |
| SUBSTITUTE(, , , ) | Replaces existing text with new text in a text string. |
| TRIM() | Removes all spaces from text except for single spaces between words. |
| UNICHAR(number) | Returns the Unicode character referenced by the 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. |
Time Intelligence Functions
| Functions | Description |
| CLOSINGBALANCEMONTH(,[,]) | Evaluates the expression at the last date of the month in the current context. |
| CLOSINGBALANCEQUARTER(,[,]) | Evaluates the expression at the last date of the quarter in the current context. |
| CLOSINGBALANCEYEAR(,[,][,]) | Evaluates the expression at the last date of the year in the current context. |
| DATEADD(,,) | Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context. |
| DATESBETWEEN(, , ) | Returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date. |
| DATESINPERIOD(, , , ) | Returns a table that contains a column of dates that begins with a specified start date and continues for the specified number and type of date intervals. |
| DATESMTD() | Returns a table that contains a column of the dates for the month to date, in the current context. |
| DATESQTD() | Returns a table that contains a column of the dates for the quarter to date, in the current context. |
| DATESYTD( [,]) | Returns a table that contains a column of the dates for the year to date, in the current context. |
| ENDOFMONTH() | Returns the last date of the month in the current context for the specified column of dates. |
| ENDOFQUARTER() | Returns the last date of the quarter in the current context for the specified column of dates. |
| ENDOFYEAR( [,]) | Returns the last date of the year in the current context for the specified column of dates. |
| FIRSTDATE() | Returns the first date in the current context for the specified column of dates. |
| FIRSTNONBLANK(,) | Returns the first value in the column, filtered by the current context, where the expression is not blank. |
| FIRSTNONBLANKVALUE(, ) | Evaluates an expression filtered by the sorted values of a column and returns the first value of the expression that is not blank. |
| LASTDATE() | Returns the last date in the current context for the specified column of dates. |
| LASTNONBLANK(,) | Returns the last value in the column, filtered by the current context, where the expression is not blank. |
| LASTNONBLANKVALUE(, ) | Evaluates an expression filtered by the sorted values of a column and returns the last value of the expression that is not blank. |
| NEXTDAY() | Returns a table that contains a column of all dates from the next day, based on the first date specified in the dates column in the current context. |
| NEXTMONTH() | Returns a table that contains a column of all dates from the next month, based on the first date in the current context. |
| NEXTQUARTER() | Returns a table that contains a column of all dates in the next quarter, based on the first date specified in the dates column, in the current context. |
| NEXTYEAR([,]) | Returns a table that contains a column of all dates in the next year, based on the first date in the dates column, in the current context. |
| OPENINGBALANCEMONTH(,[,]) | Evaluates the expression at the first date of the month in the current context. |
| OPENINGBALANCEQUARTER(,[,]) | Evaluates the expression at the first date of the quarter, in the current context. |
| OPENINGBALANCEYEAR(,[,][,]) | Evaluates the expression at the first date of the year in the current context. |
| PARALLELPERIOD(,,) | Returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time. |
| PREVIOUSDAY() | Returns a table that contains a column of all dates representing the day that is previous to the first date in the dates column, in the current context. |
| PREVIOUSMONTH() | Returns a table that contains a column of all dates from the previous month, based on the first date in the dates column, in the current context. |
| PREVIOUSQUARTER() | Returns a table that contains a column of all dates from the previous quarter, based on the first date in the dates column, in the current context. |
| PREVIOUSYEAR([,]) | Returns a table that contains a column of all dates from the previous year, given the last date in the dates column, in the current context. |
| SAMEPERIODLASTYEAR() | Returns a table that contains a column of dates shifted one year back in time from the dates in the specified date column, in the current context. |
| STARTOFMONTH() | Returns the first date of the month in the current context for the specified column of dates. |
| STARTOFQUARTER() | Returns the first date of the quarter in the current context for the specified column of dates. |
| STARTOFYEAR() | Returns the first date of the year in the current context for the specified column of dates. |
| TOTALMTD(,[,]) | Evaluates the value of the expression for the month to date, in the current context. |
| TOTALQTD(,[,]) | Evaluates the value of the expression for the dates in the quarter to date, in the current context. |
| TOTALYTD(,[,][,]) | Evaluates the year-to-date value of the expression in the current context. |