Start here
Lessons and reference
Look things up
Start here
SQL
Power Query
Data modeling
Power BI and DAX
Finance playbooks
Run queries
Build and check
Measures and calculated tables for Power BI, Excel Power Pivot and Analysis Services. 174 functions, grouped by what you use them for.
AVERAGEReturns the mean of the numbers in one column.AVERAGEXEvaluates an expression on each row of a table, then averages the results.COUNTCounts the cells in a column that hold a number.COUNTACounts the cells in a column that are not blank, whatever type they hold.COUNTBLANKCounts the cells in a column that are blank.COUNTROWSCounts the rows in a table.COUNTXEvaluates an expression on each row of a table and counts the rows where the result is not blank.DISTINCTCOUNTCounts the unique values in a column.DIVIDEDivides two numbers and returns a safe result when the denominator is zero or blank.MAXReturns the largest value in a column, or the larger of two values.MAXXEvaluates an expression on each row of a table and returns the largest result.MEDIANReturns the middle value of the numbers in a column.MINReturns the smallest value in a column, or the smaller of two values.MINXEvaluates an expression on each row of a table and returns the smallest result.PRODUCTMultiplies together the numbers in a column.SUMAdds up every number in one column.SUMXEvaluates an expression on each row of a table, then adds the results.
CALENDARReturns a table with one column called Date that holds every day from the start date to the end date.CALENDARAUTOReturns a date table that spans from the earliest to the latest date found in the date columns of the model.DATEReturns a date built from a year, a month number and a day number.DATEDIFFReturns the number of intervals between two dates.DATEVALUEConverts a date written as text into a date value.DAYReturns the day of the month of a date, from 1 to 31.EDATEReturns the date that is a given number of months before or after the start date, on the same day of the month.EOMONTHReturns the last day of the month that is a given number of months before or after the start date.MONTHReturns the month number of a date, from 1 for January to 12 for December.NOWReturns the current date and time.QUARTERReturns the calendar quarter of a date, from 1 for January to March to 4 for October to December.TIMEReturns a time of day built from hour, minute and second numbers.TODAYReturns the current date, with no time part.WEEKDAYReturns a number for the day of the week of a date.WEEKNUMReturns the week number of the year for a date.YEARReturns the four-digit calendar year of a date.YEARFRACReturns the fraction of a year between two dates.
ALLReturns every row or every value of a table or column, ignoring the filters on it.ALLEXCEPTRemoves every filter on a table except the filters on the columns you list.ALLNOBLANKROWReturns all the rows of a table except the blank row that a broken relationship can add.ALLSELECTEDReturns the rows or values that remain after slicers and page filters, ignoring the visual's own row and column grouping.CALCULATEEvaluates an expression in a filter context that you change.CALCULATETABLEEvaluates a table expression in a filter context that you change.DISTINCTReturns the unique values of a column, or the table with duplicate rows removed.EARLIERReturns the value of a column from an outer row, while a formula loops over an inner row.EARLIESTReturns the value of a column from the outermost row context, the row where the nesting started.FILTERReturns the rows of a table where a condition is true, checked one row at a time.HASONEFILTERReturns TRUE when exactly one filter is applied directly to a column, whatever the number of values in that filter.ISEMPTYReturns TRUE when a table has no rows in the current filter context.KEEPFILTERSCombines a new filter with the existing filter, so only rows that satisfy both are kept.REMOVEFILTERSClears the filters on the tables or columns you name, or on the whole model when used with no arguments. Works only as a CALCULATE filter.VALUESReturns the distinct values, or the distinct rows, that are visible in the current filter context.VARStores a value or a table in a named variable that the RETURN part can reuse.
DBReturns the fixed-rate declining balance depreciation of an asset for a given period.DDBReturns the double declining balance depreciation of an asset for a given period.FVReturns the future value of an investment that has equal periodic payments and a fixed rate.IPMTReturns the interest part of the payment for a given period of a loan with a fixed rate.NPERReturns the number of periods needed to pay off a loan, or to reach a target, with equal payments at a fixed rate.PMTReturns the payment for each period of a loan or annuity that has a fixed rate.PPMTReturns the principal part of the payment for a given period of a loan with a fixed rate.PVReturns the present value of a series of equal payments, or of a future amount, at a fixed rate.RATEReturns the interest rate per period of a loan or annuity.SLNReturns the straight-line depreciation of an asset for one period.SYDReturns the sum-of-years'-digits depreciation of an asset for a given period.XIRRReturns the annual internal rate of return for cash flows that fall on specific dates, which do not need to be evenly spaced.XNPVReturns the net present value of cash flows that fall on specific dates, which do not need to be evenly spaced.
HASONEVALUEReturns TRUE when a column has exactly one distinct value in the current filter context.ISCROSSFILTEREDReturns TRUE when a column is filtered indirectly, through a relationship with another table.ISERRORReturns TRUE when a calculation returns an error.ISFILTEREDReturns TRUE when a column has a direct filter applied to it.ISINSCOPEReturns TRUE when a column is at a level of a matrix or hierarchy that is shown in the current row or column.ISNUMBERReturns TRUE when a value is a number.ISTEXTReturns TRUE when a value is text.SELECTEDVALUEReturns the one selected value of a column, or an alternate result when zero or several values are selected.USERNAMEReturns the name of the user who is connected to the model.USERPRINCIPALNAMEReturns the user principal name of the person connected to the model, usually the sign in email address.
ANDReturns TRUE only when both conditions are true.BLANKReturns a blank value, which means no value at all.COALESCEReturns the first value in the list that is not blank.FALSEReturns the logical value FALSE.IFReturns one value when a test is true and another value when it is false.IFERRORReturns a value, or a replacement value when the calculation fails with an error.ISBLANKReturns TRUE when a value is blank.NOTReverses a true or false value.ORReturns TRUE when at least one of two conditions is true.SWITCHCompares an expression with a list of values and returns the result for the first match.TRUEReturns the logical value TRUE.
ABSReturns a number without its sign, so a negative becomes positive.CEILINGRounds a number up to the nearest multiple of a significance value.FLOORRounds a number down to the nearest multiple of a significance value.INTRounds a number down to the nearest whole number, toward negative infinity.MODReturns the remainder after a number is divided by a divisor.POWERRaises a number to a power.ROUNDRounds a number to a set number of decimal places, or to the left of the decimal point.ROUNDDOWNRounds a number down, toward zero, to a set number of digits.ROUNDUPRounds a number up, away from zero, to a set number of digits.SQRTReturns the square root of a number.TRUNCCuts a number down to a set number of digits, toward zero, without rounding.
INDEXReturns the row at a given position in an ordered list. Position 1 is the first row, and a negative number counts from the end.OFFSETReturns the row a set number of positions before or after the current row, in an ordered list.RANKA newer DAX function that ranks items in an ordered set of rows. Its exact arguments depend on the version you use.RANKXReturns the rank of the current item among the rows of a table, based on an expression.ROWNUMBERGives each row of an ordered list a sequence number, starting from 1.TOPNReturns the top n rows of a table, ordered by an expression, highest first unless you say ASC.WINDOWReturns a block of rows from an ordered list, with positions counted from the start of the list or from the current row.
CROSSFILTERChanges how filters flow through an existing relationship, for one calculation.LOOKUPVALUEReturns one value from a column, found by matching one or more columns to given values, with no relationship needed.RELATEDBrings a value from the one side of a relationship into the current row of the many side.RELATEDTABLEReturns the rows of a table on the many side that match the current row on the one side.TREATASUses the values of a table as filters on columns in another table, with no relationship in the model.USERELATIONSHIPActivates an inactive relationship for one calculation, inside CALCULATE.
ADDCOLUMNSReturns the table you give, with extra computed columns added to each row.CONTAINSReturns TRUE if the table has at least one row where the named column equals the value.CONTAINSROWReturns TRUE if the table has a row that matches the values you give, in column order.CROSSJOINReturns every combination of rows from the tables you give (a cartesian product).DATATABLECreates a small table with the values typed into the formula.EXCEPTReturns the rows in the first table that are not in the second table.GENERATEFor each row of the first table, evaluates a second table expression and returns the combined rows, dropping first-table rows that produce nothing.GENERATESERIESReturns a one-column table of numbers from a start value to an end value.GROUPBYGroups a table by columns and calculates totals for each group, using CURRENTGROUP() to reach the rows in each group.INTERSECTReturns the rows that appear in every table you give.ROWReturns a table with exactly one row, made of the named values.SELECTCOLUMNSReturns a new table with only the columns you name, each one built from an expression.SUMMARIZEReturns one row for each combination of the grouping columns that exists in the data.SUMMARIZECOLUMNSGroups a table by the columns you name, calculates measures for each group, and drops groups where every measure is blank.UNIONAppends the rows of several tables that have the same number of columns.
CONCATENATEJoins two text values into one text value.CONCATENATEXEvaluates an expression on each row of a table and joins the results into one text value.FINDReturns the position of one text value inside another, matching case exactly.FORMATConverts a value to text using a format pattern such as currency, percent or date.LEFTReturns the first characters of a text value.LENReturns the number of characters in a text value.LOWERConverts all letters in a text value to lower case.MIDReturns a set number of characters from the middle of a text value, starting at a position.REPLACEReplaces part of a text value, starting at a position and covering a set number of characters.REPTRepeats a text value a set number of times.RIGHTReturns the last characters of a text value.SEARCHReturns the position of one text value inside another, ignoring case.SUBSTITUTEReplaces text that matches a search string with new text.TRIMRemoves extra spaces from text, leaving one space between words and none at the ends.UPPERConverts all letters in a text value to capitals.VALUEConverts a text value that looks like a number into a number.
CLOSINGBALANCEMONTHEvaluates an expression on the last date of the month in the filter context, for balances that do not add up over time.CLOSINGBALANCEQUARTEREvaluates a balance on the last date of the calendar quarter in the filter context.CLOSINGBALANCEYEAREvaluates a balance on the last date of the year in the filter context, with an optional fiscal year end.OPENINGBALANCEMONTHEvaluates a balance at the start of the month in the filter context.OPENINGBALANCEQUARTEREvaluates a balance at the start of the calendar quarter in the filter context.OPENINGBALANCEYEAREvaluates a balance at the start of the year in the filter context, with an optional fiscal year end.
ENDOFMONTHReturns the last date of the month that contains the dates in the filter context, as a one-row table.ENDOFQUARTERReturns the last date of the calendar quarter that contains the dates in the filter context, as a one-row table.ENDOFYEARReturns the last date of the year that contains the dates in the filter context, with an optional fiscal year end, as a one-row table.FIRSTDATEReturns the first date in the filter context, as a one-row table.FIRSTNONBLANKReturns the first value in a column, in order, where the expression is not blank.LASTDATEReturns the last date in the filter context, as a one-row table.LASTNONBLANKReturns the last value in a column, in order, where the expression is not blank.STARTOFMONTHReturns the first date of the month that contains the dates in the filter context, as a one-row table.STARTOFQUARTERReturns the first date of the calendar quarter that contains the dates in the filter context, as a one-row table.STARTOFYEARReturns the first date of the year that contains the dates in the filter context, with an optional fiscal year end, as a one-row table.
DATEADDShifts the dates in the filter context by a number of days, months, quarters or years.DATESBETWEENReturns the dates from the start date to the end date, including both ends.DATESINPERIODReturns a set of dates that runs for a number of intervals from a start date, going back when the number is negative.NEXTDAYReturns the calendar day after each date in the filter context.NEXTMONTHReturns all the dates of the month after the first date in the filter context.NEXTQUARTERReturns all the dates of the calendar quarter after the first date in the filter context.NEXTYEARReturns all the dates of the year after the year in the filter context.PARALLELPERIODReturns the whole parallel month, quarter or year, shifted by a number of intervals.PREVIOUSDAYReturns the calendar day before each date in the filter context.PREVIOUSMONTHReturns all the dates of the month before the first date in the filter context.PREVIOUSQUARTERReturns all the dates of the calendar quarter before the first date in the filter context.PREVIOUSYEARReturns all the dates of the year before the year in the filter context.SAMEPERIODLASTYEARReturns the same dates one year earlier, for use as a filter in CALCULATE.
DATESMTDReturns a table of dates from the first day of the month up to the last date in the filter context.DATESQTDReturns a table of dates from the start of the calendar quarter up to the last date in the filter context.DATESYTDReturns a table of dates from the start of the year up to the last date in the filter context.TOTALMTDEvaluates an expression from the first day of the month up to the last date in the filter context.TOTALQTDEvaluates an expression from the start of the calendar quarter up to the last date in the filter context.TOTALYTDEvaluates an expression from the start of the year up to the last date in the filter context.