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

FunctionDescription
ANDChecks 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”))
COALESCEReturns the first expression that does not evaluate to BLANK.
FALSEReturns the logical value FALSE.
IFChecks 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”))
IFERROREvaluates an expression and returns a specified value if the expression returns an error
NOTChanges FALSE to TRUE, or TRUE to FALSE.
ORChecks whether one of the arguments is TRUE to return TRUE.
SWITCHEvaluates 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”)
TRUEReturns 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.

FunctionDescription
COMBINEVALUESJoins two or more text strings into one text string.
CONCATENATEJoins two text strings into one text string.
CONCATENATEXConcatenates the result of an expression evaluated for each row in a table.
EXACTCompares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.
FINDReturns the starting position of one text string within another text string.
FIXEDRounds a number to the specified number of decimals and returns the result as text.
FORMATConverts a value to text according to the specified format.
LEFTReturns the specified number of characters from the start of a text string.  
Ex: LEFT(CONCATENATE(Employees[Employee]&” (“,Employees[Designation]&”)”),15)
LENReturns the number of characters in a text string.
LOWERConverts all letters in a text string to lowercase.
MIDReturns a string of characters from the middle of a text string, given a starting position and length.
REPLACEREPLACE 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,”***”)
REPTRepeats text a given number of times.
RIGHTRIGHT returns the last character or characters in a text string, based on the number of characters you specify.
SEARCHReturns the number of the character at which a specific character or text string is first found, reading left to right.
SUBSTITUTEReplaces existing text with new text in a text string.   Ex: SUBSTITUTE(CONCATENATE(Employees[Employee]&” (“,Employees[Designation]&”)”),” “,”_”,1)
TRIMRemoves all spaces from text except for single spaces between words.  
Ex: TRIM(UPPER(CONCATENATE(Employees[Employee]&” (“,Employees[Designation]&”) “)))
UNICHARReturns the Unicode character referenced by the numeric value.
UNICODEReturns the numeric code corresponding to the first character of the text string.
UPPERConverts a text string to all uppercase letters.  
Ex: UPPER(CONCATENATE(Employees[Employee]&” (“,Employees[Designation]&”)”))
VALUEConverts 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.

FunctionDescription
CALENDARReturns a table with a single column named “Date” that contains a contiguous set of dates.
CALENDARAUTOReturns a table with a single column named “Date” that contains a contiguous set of dates.
DATEReturns the specified date in datetime format.  
Ex: DATE(Employees[HireDate].[Year],Employees[HireDate].[MonthNo], Employees[HireDate].[Day])
DATEDIFFReturns the count of interval boundaries crossed between two dates.  
Ex: DATEDIFF(Orders[OrderDate],TODAY(),MONTH)
DATEVALUEConverts a date in the form of text to a date in datetime format.  
Ex: DATEVALUE(Orders[OrderDate])
DAYReturns the day of the month, a number from 1 to 31.
EDATEReturns the date that is the indicated number of months before or after the start date.
EOMONTHReturns the date in datetime format of the last day of the month, before or after a specified number of months.
HOURReturns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).
MINUTEReturns the minute as a number from 0 to 59, given a date and time value.
MONTHReturns the month as a number from 1 (January) to 12 (December).
NOWReturns the current date and time in datetime format.
QUARTERReturns the quarter as a number from 1 to 4.
SECONDReturns the seconds of a time value, as a number from 0 to 59.
TIMEConverts hours, minutes, and seconds given as numbers to a time in datetime format.
TIMEVALUEConverts a time in text format to a time in datetime format.
TODAYReturns the current date.
UTCNOWReturns the current UTC date and time
UTCTODAYReturns the current UTC date.
WEEKDAYReturns a number from 1 to 7 identifying the day of the week of a date.
WEEKNUMReturns the week number for the given date and year according to the return_type value.
YEARReturns the year of a date as a four digit integer in the range 1900-9999.
YEARFRACCalculates 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.

FunctionDescription
CLOSINGBALANCEMONTHEvaluates the expression at the last date of the month in the current context.
CLOSINGBALANCEQUARTEREvaluates the expression at the last date of the quarter in the current context.
CLOSINGBALANCEYEAREvaluates the expression at the last date of the year in the current context.
DATEADDReturns 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)
DATESBETWEENReturns 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″)
DATESINPERIODReturns 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.
DATESMTDReturns a table that contains a column of the dates for the month to date, in the current context.
DATESQTDReturns a table that contains a column of the dates for the quarter to date, in the current context.
DATESYTDReturns a table that contains a column of the dates for the year to date, in the current context.
ENDOFMONTHReturns the last date of the month in the current context for the specified column of dates.
ENDOFQUARTERReturns the last date of the quarter in the current context for the specified column of dates.
ENDOFYEARReturns the last date of the year in the current context for the specified column of dates.
FIRSTDATEReturns the first date in the current context for the specified column of dates.
FIRSTNONBLANKReturns the first value in the column, column, filtered by the current context, where the expression is not blank
LASTDATEReturns the last date in the current context for the specified column of dates.
LASTNONBLANKReturns the last value in the column, column, filtered by the current context, where the expression is not blank.
NEXTDAYReturns 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.
NEXTMONTHReturns 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.
NEXTQUARTERReturns 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.
NEXTYEARReturns 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.
OPENINGBALANCEMONTHEvaluates the expression at the first date of the month in the current context.
OPENINGBALANCEQUARTEREvaluates the expression at the first date of the quarter, in the current context.
OPENINGBALANCEYEAREvaluates the expression at the first date of the year in the current context.
PARALLELPERIODReturns 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.
PREVIOUSDAYReturns 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])
PREVIOUSMONTHReturns 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.
PREVIOUSQUARTERReturns 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.
PREVIOUSYEARReturns 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.
SAMEPERIODLASTYEARReturns 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.
STARTOFMONTHReturns the first date of the month in the current context for the specified column of dates.
STARTOFQUARTERReturns the first date of the quarter in the current context for the specified column of dates.
STARTOFYEARReturns the first date of the year in the current context for the specified column of dates.
TOTALMTDEvaluates the value of the expression for the month to date, in the current context.
TOTALQTDEvaluates the value of the expression for the dates in the quarter to date, in the current context.
TOTALYTDEvaluates 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.

FunctionDescription
ABSReturns the absolute value of a number.
ACOSReturns the arccosine, or inverse cosine, of a number.
ACOSHReturns the inverse hyperbolic cosine of a number.
ACOTReturns the arccotangent, or inverse cotangent, of a number.
ACOTHReturns the inverse hyperbolic cotangent of a number.
ASINReturns the arcsine, or inverse sine, of a number.
ASINHReturns the inverse hyperbolic sine of a number.
ATANReturns the arctangent, or inverse tangent, of a number.
ATANHReturns the inverse hyperbolic tangent of a number.
CEILINGRounds a number up, to the nearest integer or to the nearest multiple of significance.
COMBINReturns the number of combinations for a given number of items.
COMBINAReturns the number of combinations (with repetitions) for a given number of items.
CONVERTConverts an expression of one data type to another.
COSReturns the cosine of the given angle.
COSHReturns the hyperbolic cosine of a number.
CURRENCYEvaluates the argument and returns the result as currency data type.
DEGREESConverts radians into degrees.
DIVIDEPerforms division and returns alternate result or BLANK() on division by 0.
EVENReturns number rounded up to the nearest even integer.
EXPReturns e raised to the power of a given number.
FACTReturns the factorial of a number, equal to the series 1*2*3*…* , ending in the given number.
FLOORRounds a number down, toward zero, to the nearest multiple of significance.
GCDReturns the greatest common divisor of two or more integers.
INTRounds a number down to the nearest integer.
ISO.CEILINGRounds a number up, to the nearest integer or to the nearest multiple of significance.
LCMReturns the least common multiple of integers.
LNReturns the natural logarithm of a number.
LOGReturns the logarithm of a number to the base you specify.
LOG10Returns the base-10 logarithm of a number.
MROUNDReturns a number rounded to the desired multiple.
ODDReturns number rounded up to the nearest odd integer.
PIReturns the value of Pi, 3.14159265358979, accurate to 15 digits.
POWERReturns the result of a number raised to a power.
PRODUCTReturns the product of the numbers in a column.
PRODUCTXReturns the product of an expression evaluated for each row in a table.
QUOTIENTPerforms division and returns only the integer portion of the division result.
RADIANSConverts degrees to radians.
RANDReturns a random number greater than or equal to 0 and less than 1, evenly distributed.
RANDBETWEENReturns a random number in the range between two numbers you specify.
ROUNDRounds a number to the specified number of digits.
ROUNDDOWNRounds a number down, toward zero.
ROUNDUPRounds a number up, away from 0 (zero).
SIGNDetermines the sign of a number, the result of a calculation, or a value in a column.
SQRTReturns the square root of a number.
SUMAdds all the numbers in a column.
SUMXReturns the sum of an expression evaluated for each row in a table.
TRUNCTruncates a number to an integer by removing the decimal, or fractional, part of the number.

Cheers..
Amit Kumar

Prince Tech Solutions
trainer.qv@gmail.com | www.princetechsolutions.com
—————————————————————–
Delivering High Earning Skill Sets
Online Training | Corporate Training | Consulting | Job Support