Microsoft PowerPivot, Power BI Desktop, and SQL Server Analysis Services (SSAS) Tabular models have a native formula and query language named DAX or Data Analysis Expression. DAX includes a set of functions and operators to perform Dynamic aggregation with the relational data. This page helps you find the list of operators and functions with the respective Step by Step guide we have with DAX.
At the end of this article, you may find some interview questions.
| Category | Function | Syntax | Description | |
| Date and Time Functions | DATE Function | DATE(<year>, <month>, <day>) | Returns the specified date in datetime format. | |
| Date and Time Functions | DATEVALUE Function | DATEVALUE(date_text) | Converts a date in the form of text to a date in datetime format. | |
| Date and Time Functions | DAY Function | DAY(<date>) <<datetime or text>> | Returns the day of the month, a number from 1 to 31. | |
| Date and Time Functions | EDATE Function | EDATE(<start_date>, <months>) | Returns the date that is the indicated number of months before or after the start date. Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. | |
| Date and Time Functions | EOMONTH Function | EOMONTH(<start_date>, <months>) | Returns the date in datetime format of the last day of the month, before or after a specified number of months. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. | |
| Date and Time Functions | HOUR Function | HOUR(<datetime>) | Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.). | |
| Date and Time Functions | MINUTE Function | MINUTE(<datetime>) | Returns the minute as a number from 0 to 59, given a date and time value. | |
| Date and Time Functions | MONTH Function | MONTH(<datetime>) | Returns the month as a number from 1 (January) to 12 (December). | |
| Date and Time Functions | NOW Function | NOW() | The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet. | |
| Date and Time Functions | SECOND Function | SECOND(<time>) | Returns the seconds of a time value, as a number from 0 to 59. | |
| Date and Time Functions | TIME Function | TIME(hour, minute, second) | Converts hours, minutes, and seconds given as numbers to a time in datetime format. | |
| Date and Time Functions | TIMEVALUE Function | TIMEVALUE(time_text) | Converts a time in text format to a time in datetime format. | |
| Date and Time Functions | TODAY Function | TODAY() | Returns the current date. | |
| Date and Time Functions | WEEKDAY Function | WEEKDAY(<date>, <return_type>) | Returns a number from 1 to 7 identifying the day of the week of a date. By default the day ranges from 1 (Sunday) to 7 (Saturday). | |
| Date and Time Functions | WEEKNUM Function | WEEKNUM(<date>, <return_type>) | 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. | |
| Date and Time Functions | YEAR Function | YEAR(<date>) | Returns the year of a date as a four digit integer in the range 1900-9999. | |
| Date and Time Functions | YEARFRAC Function | YEARFRAC(<start_date>, <end_date>, <basis>) | Calculates the fraction of the year represented by the number of whole days between two dates. Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term. | |
| Time Intelligence Functions | CLOSINGBALANCEMONTH Function | CLOSINGBALANCEMONTH(<expression>,<dates>[,<filter>]) | Evaluates the expression at the last date of the month in the current context. | |
| Time Intelligence Functions | CLOSINGBALANCEQUARTER Function | |||
| Time Intelligence Functions | CLOSINGBALANCEYEAR Function | |||
| Time Intelligence Functions | DATEADD Function | DATEADD(<dates>,<number_of_intervals>,<interval>) | 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. | |
| Time Intelligence Functions | DATESBETWEEN Function | DATESBETWEEN(<dates>,<start_date>,<end_date>) | Returns a table that contains a column of dates that begins with the start_date and continues until the end_date. | |
| Time Intelligence Functions | DATESINPERIOD Function | DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>) | Returns a table that contains a column of dates that begins with the start_date and continues for the specified number_of_intervals. | |
| Time Intelligence Functions | DATESMTD Function | DATESMTD(<dates>) | Returns a table that contains a column of the dates for the month to date, in the current context. | |
| Time Intelligence Functions | DATESQTD Function | |||
| Time Intelligence Functions | DATESYTD Function | |||
| Time Intelligence Functions | ENDOFMONTH Function | ENDOFMONTH(<dates>) | Returns the last date of the month in the current context for the specified column of dates. | |
| Time Intelligence Functions | ENDOFQUARTER Function | |||
| Time Intelligence Functions | ENDOFYEAR Function | |||
| Time Intelligence Functions | FIRSTDATE Function | FIRSTDATE(<dates>) | Returns the first date in the current context for the specified column of dates. | |
| Time Intelligence Functions | FIRSTNONBLANK Function | FIRSTNONBLANK(<column>,<expression>) | Returns the first value in the column, column, filtered by the current context, where the expression is not blank. | |
| Time Intelligence Functions | LASTDATE Function | LASTDATE(<dates>) | Returns the last date in the current context for the specified column of dates. | |
| Time Intelligence Functions | LASTNONBLANK Function | LASTNONBLANK(<column>,<expression>) | Returns the last value in the column, column, filtered by the current context, where the expression is not blank. | |
| Time Intelligence Functions | NEXTDAY Function | NEXTDAY(<dates>) | 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. | |
| Time Intelligence Functions | NEXTMONTH Function | |||
| Time Intelligence Functions | NEXTQUARTER Function | |||
| Time Intelligence Functions | NEXTYEAR Function | |||
| Time Intelligence Functions | OPENINGBALANCEMONTH Function | OPENINGBALANCEMONTH(<expression>,<dates>[,<filter>]) | Evaluates the expression at the first date of the month in the current context. | |
| Time Intelligence Functions | OPENINGBALANCEQUARTER Function | |||
| Time Intelligence Functions | OPENINGBALANCEYEAR Function | |||
| Time Intelligence Functions | PARALLELPERIOD Function | PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>) | 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. | |
| Time Intelligence Functions | PREVIOUSDAY Function | PREVIOUSDAY(<dates>) | 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. | |
| Time Intelligence Functions | PREVIOUSMONTH Function | |||
| Time Intelligence Functions | PREVIOUSQUARTER Function | |||
| Time Intelligence Functions | PREVIOUSYEAR Function | |||
| Time Intelligence Functions | SAMEPERIODLASTYEAR Function | SAMEPERIODLASTYEAR(<dates>) | Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. | |
| Time Intelligence Functions | STARTOFMONTH Function | STARTOFMONTH(<dates>) | Returns the first date of the month in the current context for the specified column of dates. | |
| Time Intelligence Functions | STARTOFQUARTER Function | |||
| Time Intelligence Functions | STARTOFYEAR Function | |||
| Time Intelligence Functions | TOTALMTD Function | TOTALMTD(<expression>,<dates>[,<filter>]) | Evaluates the value of the expression for the month to date, in the current context. | |
| Time Intelligence Functions | TOTALQTD Function | |||
| Time Intelligence Functions | TOTALYTD Function | |||
| Filter Functions | ADDMISSINGITEMS Function | ADDMISSINGITEMS(<showAllColumn>[, <showAllColumn>]…, <table>, <groupingColumn>[, <groupingColumn>]…[, filterTable]…) ADDMISSINGITEMS(<showAllColumn>[, <showAllColumn>]…, <table>, [ROLLUPISSUBTOTAL(]<groupingColumn>[, <isSubtotal_columnName>][, <groupingColumn>][, <isSubtotal_columnName>]…[)], [, filterTable]…) |
Adds combinations of items from multiple columns to a table if they do not already exist. The determination of which item combinations to add is based on referencing source columns which contain all the possible values for the columns. | |
| Filter Functions | ALL Function | ALL( {<table> | <column>[, <column>[, <column>[,…]]]} ) | Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table. | |
| Filter Functions | ALLEXCEPT Function | ALLEXCEPT(<table>,<column>[,<column>[,…]]) | Removes all context filters in the table except filters that have been applied to the specified columns. | |
| Filter Functions | ALLNOBLANKROW Function | ALLNOBLANKROW(<table>|<column>) | 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. | |
| Filter Functions | ALLSELECTED Function | ALLSELECTED([<tableName> | <columnName>]) | Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters. | |
| Filter Functions | CALCULATE Function | CALCULATE(<expression>,<filter1>,<filter2>…) | Evaluates an expression in a context that is modified by the specified filters. | |
| Filter Functions | CALCULATETABLE Function | CALCULATETABLE(<expression>,<filter1>,<filter2>,…) | Evaluates a table expression in a context modified by the given filters. | |
| Filter Functions | CROSSFILTER Function | CROSSFILTER(<columnName1>, <columnName2>, <direction>) | Specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns. | |
| Filter Functions | DISTINCT Function | DISTINCT(<column>) | Returns a one-column table that contains the distinct values from the specified column. In other words, duplicate values are removed and only unique values are returned. | |
| Filter Functions | EARLIER Function | EARLIER(<column>, <number>) | Returns the current value of the specified column in an outer evaluation pass of the mentioned column. | |
| Filter Functions | EARLIEST Function | EARLIEST(<column>) | Returns the current value of the specified column in an outer evaluation pass of the specified column. | |
| Filter Functions | FILTER Function | FILTER(<table>,<filter>) | Returns a table that represents a subset of another table or expression. | |
| Filter Functions | FILTERS Function | FILTERS(<columnName>) | Returns the values that are directly applied as filters to columnName. | |
| Filter Functions | HASONEFILTER Function | HASONEFILTER(<columnName>) | Returns TRUE when the number of directly filtered values on columnName is one; otherwise returns FALSE. | |
| Filter Functions | HASONEVALUE Function | HASONEVALUE(<columnName>) | Returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE. | |
| Filter Functions | ISCROSSFILTERED Function | ISCROSSFILTERED(<columnName>) | Returns TRUE when columnName or another column in the same or related table is being filtered. | |
| Filter Functions | ISFILTERED Function | ISFILTERED(<columnName>) | 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. | |
| Filter Functions | KEEPFILTERS Function | KEEPFILTERS(<expression>) | Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function. | |
| Filter Functions | RELATED Function | RELATED(<column>) | Returns a related value from another table. | |
| Filter Functions | RELATEDTABLE Function | RELATEDTABLE(<tableName>) | Evaluates a table expression in a context modified by the given filters. | |
| Filter Functions | SUBSTITUTEWITHINDEX Function | SUBSTITUTEWITHINDEX(<table>, <indexColumnName>, <indexColumnsTable>, [<orderBy_expression>, [<order>][, <orderBy_expression>, [<order>]]…]) | Returns a table which represents a left semijoin of the two tables supplied as arguments. | |
| Filter Functions | USERELATIONSHIP Function | USERELATIONSHIP(<columnName1>,<columnName2>) | Specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2. | |
| Filter Functions | VALUES Function | VALUES(<TableNameOrColumnName>) | Returns a one-column table that contains the distinct values from the specified table or column. In other words, duplicate values are removed and only unique values are returned. | |
| Information Functions | CONTAINS Function | CONTAINS(<table>, <columnName>, <value>[, <columnName>, <value>]…) | Returns true if values for all referred columns exist, or are contained, in those columns; otherwise, the function returns false. | |
| Information Functions | CUSTOMDATA Function | CUSTOMDATA() | Returns the content of the CustomData property in the connection string. | |
| Information Functions | ISBLANK Function | ISBLANK(<value>) | Checks whether a value is blank, and returns TRUE or FALSE. | |
| Information Functions | ISERROR Function | ISERROR(<value>) | Checks whether a value is an error, and returns TRUE or FALSE. | |
| Information Functions | ISEVEN Function | ISEVEN(number) | Returns TRUE if number is even, or FALSE if number is odd. | |
| Information Functions | ISLOGICAL Function | ISLOGICAL(<value>) | Checks whether a value is a logical value, (TRUE or FALSE), and returns TRUE or FALSE. | |
| Information Functions | ISNONTEXT Function | ISNONTEXT(<value>) | Checks if a value is not text (blank cells are not text), and returns TRUE or FALSE. | |
| Information Functions | ISNUMBER Function | ISNUMBER(<value>) | Checks whether a value is a number, and returns TRUE or FALSE. | |
| Information Functions | ISONORAFTER Function | ISONORAFTER(<scalar_expression>, <scalar_expression>sort_order] [,scalar_expression>, <scalar_expression>, [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. New for 2016. | |
| Information Functions | ISTEXT Function | ISTEXT(<value>) | Checks if a value is text, and returns TRUE or FALSE. | |
| Information Functions | LOOKUPVALUE Function | LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…) | Returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value. | |
| Information Functions | USERNAME Function | USERNAME() | Returns the domain name and username from the credentials given to the system at connection time | |
| Logical Functions | AND Function | AND(<logical1>,<logical2>) | Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise returns false. | |
| Logical Functions | FALSE Function | FALSE() | Returns the logical value FALSE. | |
| Logical Functions | IF Function | IF(logical_test>,<value_if_true>, value_if_false) | Checks if a condition provided as the first argument is met. Returns one value if the condition is TRUE, and returns another value if the condition is FALSE. | |
| Logical Functions | IFERROR Function | 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. | |
| Logical Functions | NOT Function | NOT(<logical>) | Changes FALSE to TRUE, or TRUE to FALSE. | |
| Logical Functions | OR Function | OR(<logical1>,<logical2>) | Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if both arguments are FALSE. | |
| Logical Functions | SWITCH Function | SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>]) | Evaluates an expression against a list of values and returns one of multiple possible result expressions. | |
| Logical Functions | TRUE Function | TRUE() | Returns the logical value TRUE. | |
| Math and Trig Functions | ABS Function | ABS(<number>) | Returns the absolute value of a number. | |
| Math and Trig Functions | ACOS Function | ACOS(number) | Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi. | |
| Math and Trig Functions | ACOSH Function | |||
| Math and Trig Functions | ASIN Function | |||
| Math and Trig Functions | ASINH Function | |||
| Math and Trig Functions | ATAN Function | |||
| Math and Trig Functions | ATANH Function | |||
| Math and Trig Functions | CEILING Function | CEILING(<number>, <significance>) | Rounds a number up, to the nearest integer or to the nearest multiple of significance. | |
| Math and Trig Functions | COMBIN Function | COMBIN(number, number_chosen) | Returns the number of combinations for a given number of items. Use COMBIN to determine the total possible number of groups for a given number of items. | |
| Math and Trig Functions | COMBINA Function | COMBINA(number, number_chosen) | Returns the number of combinations (with repetitions) for a given number of items. | |
| Math and Trig Functions | COS Function | |||
| Math and Trig Functions | COSH Function | |||
| Math and Trig Functions | CURRENCY Function | CURRENCY(<value>) | Evaluates the argument and returns the result as currency data type. | |
| Math and Trig Functions | DEGREES Function | DEGREES(angle) | Converts radians into degrees. | |
| Math and Trig Functions | DIVIDE Function | DIVIDE(<numerator>, <denominator> [,<alternateresult>]) | Performs division and returns alternate result or BLANK() on division by 0. | |
| Math and Trig Functions | EVEN Function | EVEN(number) | Returns number rounded up to the nearest even integer. You can use this function for processing items that come in twos. For example, a packing crate accepts rows of one or two items. The crate is full when the number of items, rounded up to the nearest two, matches the crate's capacity. | |
| Math and Trig Functions | EXP Function | EXP(<number>) | Returns e raised to the power of a given number. The constant e equals 2.71828182845904, the base of the natural logarithm. | 3 |
| Math and Trig Functions | FACT Function | FACT(<number>) | Returns the factorial of a number, equal to the series 1*2*3*...* , ending in the given number. | |
| Math and Trig Functions | FLOOR Function | FLOOR(<number>, <significance>) | Rounds a number down, toward zero, to the nearest multiple of significance. | |
| Math and Trig Functions | GCD Function | GCD(number1, [number2], ...) | Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder. | |
| Math and Trig Functions | INT Function | INT(<number>) | Rounds a number down to the nearest integer. | |
| Math and Trig Functions | ISO.CEILING Function | ISO.CEILING(<number>[, <significance>]) | Rounds a number up, to the nearest integer or to the nearest multiple of significance. | |
| Math and Trig Functions | LCM Function | LCM(number1, [number2], ...) | Returns the least common multiple of integers. The least common multiple is the smallest positive integer that is a multiple of all integer arguments number1, number2, and so on. Use LCM to add fractions with different denominators. | |
| Math and Trig Functions | LN Function | LN(<number>) | Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904). | 1 |
| Math and Trig Functions | LOG Function | LOG(<number>,<base>) | Returns the logarithm of a number to the base you specify. | |
| Math and Trig Functions | LOG10 Function | LOG10(<number>) | Returns the base-10 logarithm of a number. | |
| Math and Trig Functions | MOD Function | MOD(<number>, <divisor>) | Returns the remainder after a number is divided by a divisor. The result always has the same sign as the divisor. | |
| Math and Trig Functions | MROUND Function | MROUND(<number>, <multiple>) | Returns a number rounded to the desired multiple. | |
| Math and Trig Functions | ODD Function | ODD(number) | Returns number rounded up to the nearest odd integer. | |
| Math and Trig Functions | PI Function | PI() | Returns the value of Pi, 3.14159265358979, accurate to 15 digits. | |
| Math and Trig Functions | POWER Function | POWER(<number>, <power>) | Returns the result of a number raised to a power. | 27 |
| Math and Trig Functions | PRODUCT Function | PRODUCT(<column>) | Returns the product of the numbers in a column. | |
| Math and Trig Functions | PRODUCTX Function | PRODUCTX(<table>, <expression>) | Returns the product of an expression evaluated for each row in a table. | |
| Math and Trig Functions | QUOTIENT Function | QUOTIENT(<numerator>, <denominator>) | Performs division and returns only the integer portion of the division result. Use this function when you want to discard the remainder of division. | |
| Math and Trig Functions | RADIANS Function | RADIANS(angle) | Converts degrees to radians. | |
| Math and Trig Functions | RAND Function | RAND() | Returns a random number greater than or equal to 0 and less than 1, evenly distributed. The number that is returned changes each time the cell containing this function is recalculated. | |
| Math and Trig Functions | RANDBETWEEN Function | RANDBETWEEN(<bottom>,<top>) | Returns a random number in the range between two numbers you specify. | |
| Math and Trig Functions | ROUND Function | ROUND(<number>, <num_digits>) | Rounds a number to the specified number of digits. | |
| Math and Trig Functions | ROUNDDOWN Function | ROUNDDOWN(<number>, <num_digits>) | Rounds a number down, toward zero. | |
| Math and Trig Functions | ROUNDUP Function | ROUNDUP(<number>, <num_digits>) | Rounds a number up, away from 0 (zero). | |
| Math and Trig Functions | SIGN Function | SIGN(<number>) | 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. | |
| Math and Trig Functions | SQRT Function | SQRT(<number>) | Returns the square root of a number. | 3 |
| Math and Trig Functions | SUM Function | SUM(<column>) | Adds all the numbers in a column. | |
| Math and Trig Functions | SUMX Function | SUMX(<table>, <expression>) | Returns the sum of an expression evaluated for each row in a table. | |
| Math and Trig Functions | TRUNC Function | TRUNC(<number>,<num_digits>) | Truncates a number to an integer by removing the decimal, or fractional, part of the number. | |
| Other functions | EXCEPT Function | EXCEPT(<table_expression1>, <table_expression2> | Returns the rows of one table which do not appear in another table. | |
| Other functions | GROUPBY Function | GROUPBY (<table>, [<groupBy_columnName1>], [<name>, <expression>]… ) | 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 attempts to reuse the data that has been grouped making it highly performant. | |
| Other functions | INTERSECT Function | INTERSECT(<table_expression1>, <table_expression2>) | Returns the row intersection of two tables, retaining duplicates. | |
| Other functions | ISEMPTY Function | ISEMPTY(<table_expression>) | Checks if a table is empty. | |
| Other functions | NATURALINNERJOIN Function | NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>) | Performs an inner join of a table with another table. The tables are joined on common columns (by name) in the two tables. If the two tables have no common column names, an error is returned. | |
| Other functions | NATURALLEFTOUTERJOIN Function | NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>) | Performs a left outer join of a table with another table. The tables are joined on common columns (by name) in the two tables. If the two tables have no common column names, an error is returned. | |
| Other functions | SUMMARIZECOLUMNS Function | SUMMARIZECOLUMNS( <groupBy_columnName> [, < groupBy_columnName >]…, [<filterTable>]…[, <name>, <expression>]…) | Returns a summary table over a set of groups. | |
| Other functions | UNION Function | UNION(<table_expression1>, <table_expression2> [,<table_expression>]…) | Creates a union (join) table from a pair of tables. | |
| Other functions | VAR | VAR <name> = <expression> | Stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression. | |
| Parent and Child Functions | PATH Function | PATH(<ID_columnName>, <parent_columnName>) | Returns a delimited text string with the identifiers of all the parents of the current identifier, starting with the oldest and continuing until current. | |
| Parent and Child Functions | PATHCONTAINS Function | PATHCONTAINS(<path>, <item>) | Returns TRUE if the specified item exists within the specified path. | |
| Parent and Child Functions | PATHITEM Function | PATHITEM(<path>, <position>[, <type>]) | Returns the item at the specified position from a string resulting from evaluation of a PATH function. Positions are counted from left to right. | |
| Parent and Child Functions | PATHITEMREVERSE Function | PATHITEMREVERSE(<path>, <position>[, <type>]) | 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. | |
| Parent and Child Functions | PATHLENGTH Function | PATHLENGTH(<path>) | Returns the number of parents to the specified item in a given PATH result, including self. | |
| Statistical Functions | ADDCOLUMNS Function | ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…) | Adds calculated columns to the given table or table expression. | |
| Statistical Functions | AVERAGE Function | AVERAGE(<column>) | Returns the average (arithmetic mean) of all the numbers in a column. | |
| Statistical Functions | AVERAGEA Function | AVERAGEA(<column>) | Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values. | |
| Statistical Functions | AVERAGEX Function | AVERAGEX(<table>,<expression>) | Calculates the average (arithmetic mean) of a set of expressions evaluated over a table. | |
| Statistical Functions | BETA.DIST Function | BETA.DIST(x,alpha,beta,cumulative,[A],[B]) | Returns the beta distribution. The beta distribution is commonly used to study variation in the percentage of something across samples, such as the fraction of the day people spend watching television. | |
| Statistical Functions | BETA.INV Function | BETA.INV(probability,alpha,beta,[A],[B]) | Returns the inverse of the beta cumulative probability density function (BETA.DIST). | |
| Statistical Functions | CHISQ.INV Function | CHISQ.INV(probability,deg_freedom) | Returns the inverse of the left-tailed probability of the chi-squared distribution. | |
| Statistical Functions | CHISQ.INV.RT Function | CHISQ.INV.RT(probability,deg_freedom) | Returns the inverse of the right-tailed probability of the chi-squared distribution. | |
| Statistical Functions | CONFIDENCE.NORM Function | CONFIDENCE.NORM(alpha,standard_dev,size) | The confidence interval is a range of values. Your sample mean, x, is at the center of this range and the range is x ± CONFIDENCE.NORM. | |
| Statistical Functions | CONFIDENCE.T Function | CONFIDENCE.T(alpha,standard_dev,size) | Returns the confidence interval for a population mean, using a Student's t distribution. | |
| Statistical Functions | COUNT Function | COUNT(<column>) | The COUNT function counts the number of cells in a column that contain numbers. | |
| Statistical Functions | COUNTA Function | COUNTA(<column>) | The COUNTA function counts the number of cells in a column that are not empty. It counts not just rows that contain numeric values, but also rows that contain nonblank values, including text, dates, and logical values. | |
| Statistical Functions | COUNTAX Function | COUNTAX(<table>,<expression>) | The COUNTAX function counts nonblank results when evaluating the result of an expression over a table. That is, it works just like the COUNTA function, but is used to iterate through the rows in a table and count rows where the specified expressions results in a nonblank result. | |
| Statistical Functions | COUNTBLANK Function | COUNTBLANK(<column>) | Counts the number of blank cells in a column. | |
| Statistical Functions | COUNTROWS Function | COUNTROWS(<table>) | The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression. | |
| Statistical Functions | COUNTX Function | COUNTX(<table>,<expression>) | Counts the number of rows that contain a number or an expression that evaluates to a number, when evaluating an expression over a table. | |
| Statistical Functions | CROSSJOIN Function | CROSSJOIN(<table>, <table>[, <table>]…) | 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. | |
| Statistical Functions | DATATABLE Function | DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2..., {{Value1, Value2...}, {ValueN, ValueN+1...}...}) | Provides a mechanism for declaring an inline set of data values. | |
| Statistical Functions | DISTINCTCOUNT Function | DISTINCTCOUNT(<column>) | The DISTINCTCOUNT function counts the number of different cells in a column of numbers. | |
| Statistical Functions | EXPON.DIST Function | EXPON.DIST(x,lambda,cumulative) | Returns the exponential distribution. Use EXPON.DIST to model the time between events, such as how long an automated bank teller takes to deliver cash. For example, you can use EXPON.DIST to determine the probability that the process takes at most 1 minute. | |
| Statistical Functions | GENERATE Function | GENERATE(<table1>, <table2>) | 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. | |
| Statistical Functions | GENERATEALL Function | GENERATEALL(<table1>, <table2>) | 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. | |
| Statistical Functions | GEOMEAN Function | GEOMEAN(<column>) | Returns the geometric mean of the numbers in a column. | |
| Statistical Functions | GEOMEANX Function | GEOMEANX(<table>, <expression>) | Returns the geometric mean of an expression evaluated for each row in a table. | |
| Statistical Functions | MAX Function | MAX(<column>) | Returns the largest numeric value in a column. | |
| Statistical Functions | MAXA Function | MAXA(<column>) | Returns the largest value in a column. Logical values and blanks are counted. | |
| Statistical Functions | MAXX Function | MAXX(<table>,<expression>) | Evaluates an expression for each row of a table and returns the largest numeric value. | |
| Statistical Functions | MEDIAN Function | MEDIAN(<column>) | Returns the median of numbers in a column. | |
| Statistical Functions | MEDIANX Function | MEDIANX(<table>, <expression>) | Returns the median number of an expression evaluated for each row in a table. | |
| Statistical Functions | MIN Function | MIN(<column>) | Returns the smallest numeric value in a column. Ignores logical values and text. | |
| Statistical Functions | MINA Function | MINA(<column>) | Returns the smallest value in a column, including any logical values and numbers represented as text. | |
| Statistical Functions | MINX Function | MINX(<table>, < expression>) | Returns the smallest numeric value that results from evaluating an expression for each row of a table. | |
| Statistical Functions | PERCENTILE.EXC Function | PERCENTILE.EXC(<column>, <k>) | Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive. | |
| Statistical Functions | PERCENTILE.INC Function | PERCENTILE.INC(<column>, <k>) | Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. | |
| Statistical Functions | PERCENTILEX.EXC Function | PERCENTILEX.EXC(<table>, <expression>, k) | Returns the percentile number of an expression evaluated for each row in a table. | |
| Statistical Functions | PERCENTILEX.INC Function | PERCENTILEX.INC(<table>, <expression>;, k) | Returns the percentile number of an expression evaluated for each row in a table. | |
| Statistical Functions | POISSON.DIST Function | POISSON.DIST(x,mean,cumulative) | Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute. | |
| Statistical Functions | RANK.EQ Function | RANK.EQ(<value>, <columnName>[, <order>]) | Returns the ranking of a number in a list of numbers. | |
| Statistical Functions | RANKX Function | RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]) | Returns the ranking of a number in a list of numbers for each row in the table argument. | |
| Statistical Functions | ROW Function | ROW(<name>, <expression>[[,<name>, <expression>]…]) | Returns a table with a single row containing values that result from the expressions given to each column. | |
| Statistical Functions | SAMPLE Function | SAMPLE(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…]) | Returns a sample of N rows from the specified table. | |
| Statistical Functions | SELECTCOLUMNS Function | SELECTCOLUMNS(<table>, <name>, <scalar_expression> [, <name>, <scalar_expression>]…) | Adds calculated columns to the given table or table expression. | |
| Statistical Functions | SIN Function | SIN(number) | Returns the sine of the given angle. | |
| Statistical Functions | SINH Function | SINH(number) | Returns the hyperbolic sine of a number. | |
| Statistical Functions | STDEV.P Function | STDEV.P(<ColumnName>) | Returns the standard deviation of the entire population. | |
| Statistical Functions | STDEV.S Function | STDEV.S(<ColumnName>) | Returns the standard deviation of a sample population. | |
| Statistical Functions | STDEVX.P Function | STDEVX.P(<table>, <expression>) | Returns the standard deviation of the entire population. | |
| Statistical Functions | STDEVX.S Function | STDEVX.S(<table>, <expression>) | Returns the standard deviation of a sample population. | |
| Statistical Functions | SQRTPI Function | SQRTPI(number) | Returns the square root of (number * pi). | |
| Statistical Functions | SUMMARIZE Function | SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…) | Returns a summary table for the requested totals over a set of groups. | |
| Statistical Functions | TAN Function | TAN(number) | Returns the tangent of the given angle. | |
| Statistical Functions | TANH Function | TANH(number) | Returns the hyperbolic tangent of a number. | |
| Statistical Functions | TOPN Function | TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…]) | Returns the top N rows of the specified table. | |
| Statistical Functions | VAR.P Function | VAR.P(<columnName>) | Returns the variance of the entire population. | |
| Statistical Functions | VAR.S Function | VAR.S(<columnName>) | Returns the variance of a sample population. | |
| Statistical Functions | VARX.P Function | VARX.P(<table>, <expression>) | Returns the variance of the entire population. | |
| Statistical Functions | VARX.S Function | VARX.S(<table>, <expression>) | Returns the variance of a sample population. | |
| Statistical Functions | XIRR Function | XIRR(<table>, <values>, <dates>, [guess]) | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. | |
| Statistical Functions | XNPV Function | XNPV(<table>, <values>, <dates>, <rate>) | Returns the present value for a schedule of cash flows that is not necessarily periodic. | |
| Text functions | BLANK Function | BLANK() | Returns a blank. | |
| Text functions | CODE Function | CODE(text) | Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer. | |
| Text functions | CONCATENATE Function | CONCATENATE(<text1>, <text2>) | Joins two text strings into one text string. | |
| Text functions | CONCATENATEX Function | CONCATENATEX(<table>, <expression>, [delimiter]) | Concatenates the result of an expression evaluated for each row in a table. | |
| Text functions | EXACT Function | EXACT(<text1>,<text2>) | Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. You can use EXACT to test text being entered into a document. | |
| Text functions | FIND Function | FIND(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]]) | Returns the starting position of one text string within another text string. FIND is case-sensitive. | |
| Text functions | FIXED Function | FIXED(<number>, <decimals>, <no_commas>) | Rounds a number to the specified number of decimals and returns the result as text. You can specify that the result be returned with or without commas. | |
| Text functions | FORMAT Function | FORMAT(<value>, <format_string>) | Converts a value to text according to the specified format. | |
| Text functions | Pre-Defined Numeric Formats for the FORMAT Function | |||
| Text functions | Custom Numeric Formats for the FORMAT Function | |||
| Text functions | Pre-defined Date and Time formats for the FORMAT Function | |||
| Text functions | Custom Date and Time formats for the FORMAT Function | |||
| Text functions | LEFT Function | LEFT(<text>, <num_chars>) | Returns the specified number of characters from the start of a text string. | |
| Text functions | LEN Function | LEN(<text>) | Returns the number of characters in a text string. | |
| Text functions | LOWER Function | LOWER(<text>) | Converts all letters in a text string to lowercase. | |
| Text functions | MID Function | MID(<text>, <start_num>, <num_chars>) | Returns a string of characters from the middle of a text string, given a starting position and length. | |
| Text functions | REPLACE Function | REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>) | REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string. | |
| Text functions | REPT Function | REPT(<text>, <num_times>) | Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string. | |
| Text functions | RIGHT Function | RIGHT(<text>, <num_chars>) | RIGHT returns the last character or characters in a text string, based on the number of characters you specify. | |
| Text functions | SEARCH Function | SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]]) | 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. | |
| Text functions | SUBSTITUTE Function | SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>) | Replaces existing text with new text in a text string. | |
| Text functions | TRIM Function | TRIM(<text>) | Removes all spaces from text except for single spaces between words. | |
| Text functions | UPPER Function | UPPER (<text>) | Converts a text string to all uppercase letters | |
| Text functions | VALUE Function | VALUE(<text>) | Converts a text string that represents a number to a number. |
#Data Analysis Expressions (DAX) Reference - Microsoft Docs
DAX Interview Questions 2021 Latest Updates
IBM ,Accenture, Wipro,infosys............................
