Data Analysis Expressions(DAX)
Category Function Description Syntax
SUM Adds up all the values in a column. SUM(<column>)
SUMX Returns the sum of an expression evaluated over a table. SUMX(<table>, <expression>)
AVERAGE Calculates the average of values in a column. AVERAGE(<column>)
MIN Returns the smallest value in a column. MIN(<column>)
MAX Returns the largest value in a column. MAX(<column>)
Aggregation
Functions
COUNT Counts the number of non-blank values in a column. COUNT(<column>)
COUNTA Counts the number of non-empty cells in a column. COUNTA(<column>)
COUNTROWS Counts the number of rows in a table. COUNTROWS(<table>)
DISTINCTCOUNT Counts the number of distinct values in a column. DISTINCTCOUNT(<column>)
PRODUCT Returns the product of the numbers in a column. PRODUCT(<column>)
IF Checks a condition and returns one value if true and another if false. IF(<logical_test>, <value_if_true>[, <value_if_false>])
AND Returns TRUE if all arguments are TRUE. AND(<logical1>, <logical2>)
OR Returns TRUE if any argument is TRUE. OR(<logical1>, <logical2>)
Evaluates an expression against a list of values and returns one of several
Logical Functions SWITCH SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
possible results.
TRUE Returns the logical value TRUE. TRUE()
NOT Changes FALSE to TRUE, or TRUE to FALSE. NOT(<logical>)
Returns a specified value if the expression results in an error; otherwise,
IFERROR IFERROR(value, value_if_error)
returns the expression's value.
TODAY Returns the current date. TODAY()
NOW Returns the current date and time. NOW()
DATE Returns the date in a date-time format. DATE(<year>, <month>, <day>)
DATEDIFF Returns the difference between two dates. DATEDIFF(<start_date>, <end_date>, <interval>)
YEAR Returns the year of a date. YEAR(<date>)
Date and Time
MONTH Returns the month of a date. MONTH(<date>)
Functions
DAY Returns the day of the month from a date. DAY(<date>)
Returns the last day of the month, before or after a given number of months,
EOMONTH EOMONTH(<start_date>, <months>)
in datetime format.
HOUR Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.). HOUR(<datetime>)
NETWORKDAYS Returns the number of whole workdays between two dates (inclusive) NETWORKDAYS(<start_date>, <end_date>[, <weekend>, <holidays>])
TIMEVALUE Converts a time in text format to a time in datetime format. TIMEVALUE(time_text)
By: Divya Augustine
Data Analysis Expressions(DAX)
Category Function Description Syntax
CONCATENATE Joins two text strings into one. CONCATENATE(<text1>, <text2>)
LEFT Returns the left part of a text string. LEFT(<text>, <number_of_characters>)
RIGHT Returns the right part of a text string. RIGHT(<text>, <number_of_characters>)
Returns a specific number of characters from a text string, starting at the
MID MID(<text>, <start_position>, <number_of_characters>)
position you specify.
LEN Returns the number of characters in a text string. LEN(<text>)
Text Functions
UPPER Converts a text string to uppercase. UPPER(<text>)
LOWER Converts a text string to lowercase. LOWER(<text>)
FORMAT Converts a value to text according to the specified format. FORMAT(<value>, <format_string>[, <locale_name>])
replaces part of a text string, based on the number of characters you specify,
REPLACE REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)
with a different text string.
TRIM Removes all spaces from text except for single spaces between words. TRIM(<text>)
FILTER Returns a table that represents a subset of another table or expression. FILTER(<table>, <filter_expression>)
ALL Removes all filters from the specified columns or tables. ALL(<column>)
CALCULATE Evaluates an expression in a context modified by the specified filters. CALCULATE(<expression>, <filter1>, <filter2>, ...)
Filter Functions Removes context filters from columns and rows in the current query, while ALLSELECTED([<tableName> | <columnName>[, <columnName>[,
ALLSELECTED
retaining all other context filters or explicit filters <columnName>[,…]]]] )
Returns the value when the context for columnName has been filtered down
SELECTEDVALUE SELECTEDVALUE(<columnName>[, <alternateResult>])
to one distinct value only. Otherwise returns alternateResult.
LOOKUPVALUE (<result_columnName>, <search_columnName>,
Returns the value for the row that meets all criteria specified by one or more
LOOKUPVALUE <search_value> [, <search2_columnName>, <search2_value>]… [,
search conditions.
<alternateResult>])
TOTALYTD Calculates the year-to-date total. TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])
TOTALQTD Calculates the quarter-to-date total. TOTALQTD(<expression>,<dates>[,<filter>])
TOTALMTD Calculates the month-to-date total. TOTALMTD(<expression>, <dates>, [<filter>])
Time Intelligence Returns a table that contains a column of dates shifted by a specified number
PARALLELPERIOD PARALLELPERIOD(<dates>, <number_of_intervals>, <interval>)
Functions of intervals.
SAMEPERIODLASTY Returns a table that contains a column of dates corresponding to the same
SAMEPERIODLASTYEAR(<dates>)
EAR period last year.
Returns the last date of the year in the current context for the specified
ENDOFYEAR ENDOFYEAR(<dates> [,<year_end_date>])
column of dates
Returns a table with a column of all dates from the next month, starting from
NEXTMONTH NEXTMONTH(<dates>)
the first date in the current context’s dates column.
ROUND Rounds a number to the specified number of digits. ROUND(<number>, <num_digits>)
CEILING Rounds a number up to the nearest integer or specified multiple. CEILING(<number>, <significance>)
Math and FLOOR Rounds a number down to the nearest integer or specified multiple. FLOOR(<number>, <significance>)
Trigonometric
Functions DIVIDE Performs division and returns alternate result or BLANK() on division by 0 DIVIDE(<numerator>, <denominator> [,<alternateresult>])
POWER Returns the result of a number raised to a power. POWER(<number>, <power>)
RANDBETWEEN Returns a random number in the range between two numbers you specify RANDBETWEEN(<bottom>,<top>)
By: Divya Augustine
Data Analysis Expressions(DAX)
Category Function Description Syntax
COMBIN Returns the number of combinations for a given number of items COMBIN(number, number_chosen)
Returns the number of combinations (with repetitions) for a given number of
COMBINA COMBINA(number, number_chosen)
items
GEOMEAN Returns the geometric mean of the numbers in a column. GEOMEAN(<column>)
Returns the geometric mean of an expression evaluated for each row in a
GEOMEANX GEOMEANX(<table>, <expression>)
Statistical table.
functions
MEDIAN Returns the median of numbers in a column. MEDIAN(<column>)
RANK.EQ Returns the ranking of a number in a list of numbers. RANK.EQ(<value>, <columnName>[, <order>])
STDEV.S Returns the standard deviation of a sample population STDEV.S(<ColumnName>)
VAR.S Returns the variance of a sample population VAR.S(<columnName>)
Calculates the future value of an investment based on a constant interest
FV FV(<rate>, <nper>, <pmt>[, <pv>[, <type>]])
rate.
Calculates the payment for a loan based on constant payments and a
PMT PMT(<rate>, <nper>, <pv>[, <fv>[, <type>]])
constant interest rate.
Calculates the present value of a loan or an investment, based on a constant
PV PV(<rate>, <nper>, <pmt>[, <fv>[, <type>]])
interest rate.
RATE Interest rate per period of an annuity. RATE(<nper>, <pmt>, <pv>[, <fv>[, <type>[, <guess>]]])
Financial
functions
RRI Equivalent interest rate for the growth of an investment. RRI(<nper>, <pv>, <fv>)
Internal rate of return for a schedule of cash flows that is not necessarily
XIRR XIRR(<table>, <values>, <dates>, [, <guess>[, <alternateResult>]])
periodic.
XNPV Present value for a schedule of cash flows that is not necessarily periodic. XNPV(<table>, <values>, <dates>, <rate>)
YIELD(<settlement>, <maturity>, <rate>, <pr>, <redemption>,
YIELD Returns the yield on a security that pays periodic interest.
<frequency>[, <basis>])
RELATED Returns a related value from another table. RELATED(<column>)
Returns a one-column table that contains the distinct values from the
VALUES VALUES(<column>)
specified column.
Others
BLANK Returns a blank BLANK()
Returns TRUE when the context for columnName has been filtered down to
HASONEVALUE HASONEVALUE(<columnName>)
one distinct value only. Otherwise is FALSE
By: Divya Augustine
