Logical functions
Logical functions act upon an expression to return information about the values or sets in the expression. For example, you can use the IF function to check the result of an expression and create conditional results.
In this category
| Function | Description |
| AND | Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Ex: OrderType = IF(OrderDetails[Quantity]<=50,”Small Order”,IF(OrderDetails[Quantity]>50 && OrderDetails[Quantity]<=100,”Medium Order”,”Large Order”)) |
| COALESCE | Returns the first expression that does not evaluate to BLANK. |
| FALSE | Returns the logical value FALSE. |
| IF | Checks a condition, and returns one value when it’s TRUE, otherwise it returns a second value. Ex: OrderType = IF(OrderDetails[Quantity]<=50,”Small Order”,IF(OrderDetails[Quantity]>50 && OrderDetails[Quantity]<=100,”Medium Order”,”Large Order”)) |
| IFERROR | Evaluates an expression and returns a specified value if the expression returns an error |
| NOT | Changes FALSE to TRUE, or TRUE to FALSE. |
| OR | Checks whether one of the arguments is TRUE to return TRUE. |
| SWITCH | Evaluates an expression against a list of values and returns one of multiple possible result expressions. Ex: OrderVolume = SWITCH(OrderDetails[Quantity],50,”Small Order”,75,”Medium Order”,100,”Large Order”,150,”Mega Order”) |
| TRUE | Returns the logical value TRUE. |
Text functions
Data Analysis Expressions (DAX) includes a set of text functions based on the library of string functions in Excel, but which have been modified to work with tables and columns in tabular models. This section describes text functions available in the DAX language.
| Function | Description |
|---|---|
| COMBINEVALUES | Joins two or more text strings into one text string. |
| CONCATENATE | Joins two text strings into one text string. |
| CONCATENATEX | 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. |
| 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. Ex: LEFT(CONCATENATE(Employees[Employee]&” (“,Employees[Designation]&”)”),15) |
| 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. Ex: REPLACE(CONCATENATE(Employees[Employee]&” (“,Employees[Designation]&”) “),1,3,”***”) |
| 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. |
| SUBSTITUTE | Replaces existing text with new text in a text string. Ex: SUBSTITUTE(CONCATENATE(Employees[Employee]&” (“,Employees[Designation]&”)”),” “,”_”,1) |
| TRIM | Removes all spaces from text except for single spaces between words. Ex: TRIM(UPPER(CONCATENATE(Employees[Employee]&” (“,Employees[Designation]&”) “))) |
| UNICHAR | Returns the Unicode character referenced by the numeric value. |
| UNICODE | Returns the numeric code corresponding to the first character of the text string. |
| UPPER | Converts a text string to all uppercase letters. Ex: UPPER(CONCATENATE(Employees[Employee]&” (“,Employees[Designation]&”)”)) |
| VALUE | Converts a text string that represents a number to a number. |
Date and time functions
These functions help you create calculations based on dates and time. Many of the functions in DAX are similar to the Excel date and time functions. However, DAX functions use a datetime data type, and can take values from a column as an argument.
| Function | Description |
| CALENDAR | Returns a table with a single column named “Date” that contains a contiguous set of dates. |
| CALENDARAUTO | Returns a table with a single column named “Date” that contains a contiguous set of dates. |
| DATE | Returns the specified date in datetime format. Ex: DATE(Employees[HireDate].[Year],Employees[HireDate].[MonthNo], Employees[HireDate].[Day]) |
| DATEDIFF | Returns the count of interval boundaries crossed between two dates. Ex: DATEDIFF(Orders[OrderDate],TODAY(),MONTH) |
| DATEVALUE | Converts a date in the form of text to a date in datetime format. Ex: DATEVALUE(Orders[OrderDate]) |
| 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 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 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 to 4. |
| SECOND | Returns the seconds of a time value, as a number from 0 to 59. |
| 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. |
| 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. |
| WEEKNUM | Returns the week number for the given date and year according to the return_type value. |
| 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. |
Time intelligence functions
Data Analysis Expressions (DAX) includes time-intelligence functions that enable you to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.
| Function | 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. Ex: DATEADD(Orders[OrderDate],7,DAY) |
| DATESBETWEEN | Returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date. Ex: DATESBETWEEN(Orders[OrderDate],”12-12-2010″,”31-12-2011″) |
| 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, column, filtered by the current context, where the expression 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, column, filtered by the current context, where the expression 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 dates column 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. Ex: Previousday = PREVIOUSDAY(OrderDetails[ShipmentDate]) |
| 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 dates 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. |
Math and Trig functions
The mathematical functions in Data Analysis Expressions (DAX) are very similar to the Excel mathematical and trigonometric functions. This section lists the mathematical functions provided by DAX.
| Function | Description |
| ABS | Returns the absolute value of a number. |
| ACOS | Returns the arccosine, or inverse cosine, of a number. |
| ACOSH | Returns the inverse hyperbolic cosine of a number. |
| ACOT | Returns the arccotangent, or inverse cotangent, of a number. |
| ACOTH | Returns the inverse hyperbolic cotangent of a number. |
| ASIN | Returns the arcsine, or inverse sine, of a number. |
| ASINH | Returns the inverse hyperbolic sine of a number. |
| ATAN | Returns the arctangent, or inverse tangent, of a number. |
| ATANH | Returns the inverse hyperbolic tangent of a number. |
| CEILING | Rounds a number up, to the nearest integer or to the nearest multiple of significance. |
| COMBIN | Returns the number of combinations for a given number of items. |
| COMBINA | Returns the number of combinations (with repetitions) for a given number of items. |
| CONVERT | Converts an expression of one data type to another. |
| COS | Returns the cosine of the given angle. |
| COSH | Returns the hyperbolic cosine of a number. |
| CURRENCY | Evaluates the argument and returns the result as currency data type. |
| DEGREES | Converts radians into degrees. |
| DIVIDE | Performs division and returns alternate result or BLANK() on division by 0. |
| EVEN | Returns number rounded up to the nearest even integer. |
| EXP | Returns e raised to the power of a given number. |
| FACT | Returns the factorial of a number, equal to the series 1*2*3*…* , ending in the given number. |
| FLOOR | Rounds a number down, toward zero, to the nearest multiple of significance. |
| GCD | Returns the greatest common divisor of two or more integers. |
| INT | 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 | Returns the least common multiple of integers. |
| LN | Returns the natural logarithm of a number. |
| LOG | Returns the logarithm of a number to the base you specify. |
| LOG10 | Returns the base-10 logarithm of a number. |
| MROUND | Returns a number rounded to the desired multiple. |
| ODD | 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 | 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. |
| SQRT | 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. |
Cheers..
Amit Kumar
Prince Tech Solutions
[email protected] | www.princetechsolutions.com
—————————————————————–
Delivering High Earning Skill Sets
Online Training | Corporate Training | Consulting | Job Support