OAX functions
OAX, Open Analytical Expressions, is the language of OpenBI’s engine. It is compatible with DAX: the models of Power BI and Analysis Services, and their measures, work as they are. OAX adds functions of OpenBI’s own, marked OpenBI: a measure that uses one works in OpenBI only.
Every function the engine has is here, generated from the engine’s own list, each with a line of OpenBI’s. How OAX and DAX relate, and where they differ: OAX and DAX. To write measures, see Measures and OAX: the studio’s autocomplete offers each function as you type.
OpenBI Studio 0.3.78's engine has 482 OAX functions, in 21 groups: 396 that DAX has too, and 86 of OpenBI's own (OpenBI). Each name opens the function's page: its syntax and parameters, what it returns, remarks, and examples with the results OpenBI's engine gives them.
Aggregation
42 functions, 8 of them OpenBI's own.
APPROXIMATEDISTINCTCOUNT- The number of distinct values in a column, counted exactly: OpenBI has no DirectQuery source to estimate it on.
AVERAGE- The mean of a column's numbers, blanks left out.
AVERAGEA- The mean of a column's values, text counting as 0, TRUE as 1 and FALSE as 0.
AVERAGEX- Works out an expression row by row over a table and gives the mean of the results.
CONCATENATEX- An expression's text for each row of a table, joined with a delimiter, optionally in an order; BLANK over no rows.
COUNT- The number of a column's values that are not blank; a column of TRUE and FALSE is an error (use COUNTA).
COUNTA- The number of a column's values that are not blank, of any type.
COUNTAX- The number of a table's rows where an expression is not blank, of any type.
COUNTBLANK- How many cells of a column are blank, empty text included.
COUNTROWS- The number of rows of a table.
COUNTX- The number of a table's rows where an expression is not blank; for TRUE and FALSE values use COUNTAX.
DISTINCTCOUNT- How many different values a column holds; a blank, if there is one, is one of them.
DISTINCTCOUNTNOBLANK- How many different values a column holds, leaving blanks out.
KURT.POpenBI- How heavy the tails of a column's numbers are when they are all there is: the excess kurtosis (0 for a normal distribution); BLANK when they don't vary.
KURT.SOpenBI- The excess kurtosis of a column's numbers taken as a sample, as Excel's KURT works it out; BLANK for fewer than four values or when they don't vary.
KURTX.POpenBI- The excess kurtosis of an expression over a table's rows, taken as the whole population; blanks left out.
KURTX.SOpenBI- The excess kurtosis of an expression over a table's rows, taken as a sample (Excel's KURT); blanks left out, BLANK for fewer than four values.
MAX- The largest value in a column, or the larger of two values (a blank comparing as zero).
MAXA- The largest value in a column, text counting as 0, TRUE as 1 and FALSE as 0.
MAXX- The largest value of an expression over a table's rows; with TRUE as a third argument, text and numbers compare as ORDER BY sorts them.
MEDIAN- The median of a column's numbers, blanks left out.
MEDIANX- The median of an expression over a table's rows; blanks count, sorting as zero, and give BLANK when the median falls on one.
MIN- The smallest value in a column, or the smaller of two values (a blank comparing as zero).
MINA- The smallest value in a column, text counting as 0, TRUE as 1 and FALSE as 0.
MINX- The smallest value of an expression over a table's rows; with TRUE as a third argument, text and numbers compare as ORDER BY sorts them.
PRODUCT- The product of a column's numbers.
PRODUCTX- Evaluates an expression on every row of a table and multiplies the results together.
RANKX- A value's rank among an expression's values over a table's rows: largest first by default, ties skipping the next ranks (SKIP) or not (DENSE).
SKEW.POpenBI- How lopsided a column's numbers are when they are all there is (positive with a long tail of large values), as Excel's SKEW.P; BLANK for fewer than three values or when they don't vary.
SKEW.SOpenBI- The skewness of a column's numbers taken as a sample, as Excel's SKEW works it out; BLANK for fewer than three values or when they don't vary.
SKEWX.POpenBI- The skewness of an expression over a table's rows, taken as the whole population (Excel's SKEW.P); blanks left out.
SKEWX.SOpenBI- The skewness of an expression over a table's rows, taken as a sample (Excel's SKEW); blanks left out, BLANK for fewer than three values.
STDEV.P- How spread a column's numbers are when they are all there is: the standard deviation dividing by n.
STDEV.S- How spread out a column's numbers are, treating them as a sample: the standard deviation dividing by n − 1.
STDEVX.P- The standard deviation of an expression over a table's rows, taken as the whole population.
STDEVX.S- The standard deviation of an expression over a table's rows, taken as a sample.
SUM- The sum of a column's numbers.
SUMX- Evaluates an expression on every row of a table and adds the results up.
VAR.P- The variance of a column's numbers, taken as the whole population.
VAR.S- The variance of a column's numbers, taken as a sample.
VARX.P- The variance of an expression over a table's rows, taken as the whole population.
VARX.S- The variance of an expression over a table's rows, taken as a sample.
Date and time
24 functions.
CALENDAR- A table of one column, [Date], with every date from a start date to an end date.
CALENDARAUTO- A table of one column, [Date], of whole years around the dates in the model's imported date columns; kept for models that use it, where a date table of the model's own is the better choice.
DATE- A date from a year, a month and a day; months and days past their range carry over, and years 0 to 49 read as 2000 to 2049, 50 to 99 as 1950 to 1999.
DATEDIFF- How many interval boundaries (second, minute, hour, day, week, month, quarter or year) lie between two dates.
DATEVALUE- A date read from text, its time dropped.
DAY- Which day of its month a date falls on, 1 to 31.
EDATE- A date moved by a number of months, its time dropped.
EOMONTH- The last day of a month: the date's own, or one a number of months before or after it.
HOUR- The hour of a time, 0 to 23.
MINUTE- The minutes past the hour of a date with a time, 0 to 59.
MONTH- Which month a date falls in, counted from January as 1.
NETWORKDAYS- The working days from one date to another, both counted, with a choice of weekend days and a table of holidays.
NOW- The date and time now, by the clock of the computer the engine runs on, read once for the whole query.
QUARTER- The quarter of a date, 1 to 4.
SECOND- The seconds past the minute of a date with a time, 0 to 59.
TIME- A time from hours, minutes and seconds; past a day the hours wrap round.
TIMEVALUE- A time read from text, its date dropped.
TODAY- Today's date, by the clock of the computer the engine runs on, read once for the whole query.
UTCNOW- The date and time now in UTC, read once for the whole query: NOW less the time zone's offset.
UTCTODAY- Today's date in UTC, read once for the whole query; near midnight it can differ from TODAY.
WEEKDAY- The day of the week of a date, 1 to 7 from Sunday by default; other types start the week on another day, or count Monday as 0.
WEEKNUM- The week of the year of a date: weeks starting on Sunday by default, on another day by type, or the ISO week (type 21).
YEAR- The year of a date, as a whole number such as 2026.
YEARFRAC- The fraction of a year between two dates, by a day-count basis (US 30/360 by default).
Time intelligence
45 functions.
CLOSINGBALANCEMONTH- The expression's value on the final date of the month the filter context is in.
CLOSINGBALANCEQUARTER- The expression's value on the final date of the quarter the filter context is in.
CLOSINGBALANCEWEEK- Reads a value on the closing day of the calendar's week, such as a stock or a price as the week ends.
CLOSINGBALANCEYEAR- An expression evaluated at the last date of the year in the filter context (the year ending on 31 December, or on a date given).
DATEADD- The dates in the filter context moved forward or back by a number of days, months, quarters or years (with a calendar, weeks too).
DATESBETWEEN- The dates from a start date to an end date, both included.
DATESINPERIOD- The dates from a start date over a number of days, months, quarters or years, forward or back (with a calendar, weeks too).
DATESMTD- The dates from the start of the month to the last date in the filter context.
DATESQTD- The dates from the start of the quarter to the last date in the filter context.
DATESWTD- The dates from the start of the week to the last date in the filter context; needs a calendar.
DATESYTD- The dates from the start of the year to the last date in the filter context (the year ending on 31 December, or on a date given).
ENDOFMONTH- Gives the column's final date in the month of the filter context: on business days, the month's last business day.
ENDOFQUARTER- Gives the column's final date in the quarter of the filter context.
ENDOFWEEK- The last date of the week in the filter context; needs a calendar.
ENDOFYEAR- The last date of the year in the filter context (the year ending on 31 December, or on a date given).
FIRSTDATE- The first date in the filter context, as a table of one row.
FIRSTNONBLANK- The first of a column's values, in their order, for which an expression is not blank.
FIRSTNONBLANKVALUE- An expression's first value that is not blank, over a column's values in their order.
LASTDATE- The last date in the filter context, as a table of one row.
LASTNONBLANK- The last of a column's values, in their order, for which an expression is not blank.
LASTNONBLANKVALUE- An expression's last value that is not blank, over a column's values in their order.
NEXTDAY- The day after the last date in the filter context.
NEXTMONTH- The dates of the month after the last date in the filter context.
NEXTQUARTER- The dates of the quarter after the last date in the filter context.
NEXTWEEK- The dates of the week after the last date in the filter context; needs a calendar.
NEXTYEAR- The dates of the year after the last date in the filter context (the year ending on 31 December, or on a date given).
OPENINGBALANCEMONTH- Reads a value as the month opens: on the column's final date before the month begins.
OPENINGBALANCEQUARTER- Reads a value as the quarter opens: on the column's final date before the quarter begins.
OPENINGBALANCEWEEK- An expression evaluated at the last date of the week before the filter context's; needs a calendar.
OPENINGBALANCEYEAR- An expression evaluated at the last date of the year before the filter context's (the year ending on 31 December, or on a date given).
PARALLELPERIOD- The whole months, quarters or years the filter context touches, moved by a number of them.
PREVIOUSDAY- The day before the first date in the filter context.
PREVIOUSMONTH- The dates of the month before the first date in the filter context.
PREVIOUSQUARTER- The dates of the quarter before the first date in the filter context.
PREVIOUSWEEK- The dates of the week before the first date in the filter context; needs a calendar.
PREVIOUSYEAR- The dates of the year before the first date in the filter context (the year ending on 31 December, or on a date given).
SAMEPERIODLASTYEAR- The dates in the filter context moved back one year.
STARTOFMONTH- Gives the column's earliest date in the month of the filter context: on business days, the month's first business day.
STARTOFQUARTER- Gives the column's earliest date in the quarter of the filter context.
STARTOFWEEK- The first date of the week in the filter context; needs a calendar.
STARTOFYEAR- The first date of the year in the filter context (the year ending on 31 December, or on a date given).
TOTALMTD- An expression evaluated over the month to date.
TOTALQTD- An expression evaluated over the quarter to date.
TOTALWTD- An expression evaluated over the week to date; needs a calendar.
TOTALYTD- An expression evaluated over the year to date (the year ending on 31 December, or on a date given).
Filter
18 functions.
ALL- A table's rows, or columns' values, with every filter on them removed; as a filter of CALCULATE, removes those filters.
ALLCROSSFILTERED- As a filter of CALCULATE, removes every filter on a table, those reaching it through relationships too.
ALLEXCEPT- Removes every filter on a table except those on the columns named.
ALLNOBLANKROW- As ALL, without the blank row a relationship adds for values the other table lacks.
ALLSELECTED- A table's rows or columns' values as the filters from outside the visual leave them (slicers, the page), ignoring its own rows and columns.
CALCULATE- An expression evaluated in a filter context its filter arguments change; a row context becomes filters (context transition).
CALCULATETABLE- Gives the rows of a table expression, worked out under filters its other arguments add, remove or replace.
DISTINCT- A column's distinct values, or a table's distinct rows, without the blank row a relationship may add.
EARLIER- A column's value in an outer row context: the one before, or a number of levels out.
EARLIEST- A column's value in the outermost row context.
FILTER- The rows of a table for which a condition is TRUE.
FILTERS- The values filtering a column directly.
KEEPFILTERS- As a filter of CALCULATE, or around the table an iterator goes through, adds to the filters already in place instead of replacing them.
LOOKUPVALUE- A column's value in the row whose search columns have the values given; when none has, or rows disagree, an alternate result if given, else BLANK or an error.
REMOVEFILTERS- As a filter of CALCULATE, removes the filters on tables or columns, or all of them.
SELECTEDVALUE- A column's value when the filter context leaves exactly one, else BLANK or an alternate result.
TREATAS- A table's values applied as filters on other columns, as if the tables were related.
VALUES- A column's distinct values in the filter context, or a table's rows, with the blank row a relationship adds for values the other table lacks.
Relationships
4 functions.
CROSSFILTER- As a filter of CALCULATE, sets which way a relationship filters for the calculation: one way, both ways, or not at all.
RELATED- A column's value from the table on the one side of a relationship, for the current row.
RELATEDTABLE- The rows of a related table that belong to the current row.
USERELATIONSHIP- As a filter of CALCULATE, uses an inactive relationship between two columns for the calculation.
Table manipulation
33 functions.
ADDCOLUMNS- A table with new columns, each an expression evaluated for its rows.
ADDMISSINGITEMS- A grouped table with a row added, its other columns blank, for each combination of values it lacks.
COLUMNSTATISTICS- A row for every column of every table: its least and greatest value, its number of distinct values and its longest text.
CROSSJOIN- Every combination of the rows of two or more tables.
CURRENTGROUP- Inside GROUPBY, the rows of the group being computed.
DATATABLE- A table written out in the formula: its columns' names and types, then its rows.
DETAILROWS- Returns the rows that lie behind a measure's value, as the measure's detail rows expression describes them.
EXCEPT- The rows of a table that a second table doesn't have.
GENERATE- Each row of a table joined with the rows a second table expression gives for it; rows that get none left out.
GENERATEALL- As GENERATE, keeping the rows that get none, with blanks.
GENERATESERIES- A table of one column, [Value], of numbers from a start to an end by a step (1 by default).
GROUPBY- A table's rows grouped by columns, with columns computed over each group's rows (CURRENTGROUP), without context transition.
HASH- A 64-bit hash of one or more values, text regardless of case and 1 apart from 1.0; the numbers differ from Power BI's, whose algorithm is not published.
IGNORE- In SUMMARIZECOLUMNS, marks an expression whose blanks don't remove a row.
INTERSECT- The rows of a table that a second table has too, duplicates kept.
ISSUBTOTAL- In SUMMARIZE with ROLLUP, whether the row is a subtotal of a column.
NATURALINNERJOIN- Two tables joined on the columns they share; rows without a match left out.
NATURALLEFTOUTERJOIN- Two tables joined on the columns they share, every row of the first kept.
NONVISUAL- In SUMMARIZECOLUMNS, marks a filter that ALLSELECTED leaves out.
ROLLUP- In SUMMARIZE, adds subtotal rows for the columns named.
ROLLUPADDISSUBTOTAL- In SUMMARIZECOLUMNS, adds subtotal rows for columns, with a column saying which rows are subtotals.
ROLLUPGROUP- In ROLLUP or ROLLUPADDISSUBTOTAL, columns rolled up together as one level.
ROLLUPISSUBTOTAL- In ADDMISSINGITEMS, rolled-up columns paired with the subtotal columns ROLLUPADDISSUBTOTAL made.
ROW- A table of one row, each column a name and an expression.
SELECTCOLUMNS- A table's rows with the columns named, each a column or an expression.
SUBSTITUTEWITHINDEX- A table's rows with the columns it shares with an index table replaced by the matching row's position in that table, in an order.
SUMMARIZE- A table's groups by columns of it or of related tables, optionally with columns computed for each group.
SUMMARIZECOLUMNS- Groups by columns of any tables, with filters and measures; rows where every measure is blank left out.
TOCSV- A table's first rows (10 by default) as CSV text, with a header and a delimiter of your choice.
TOJSON- A table's first rows (10 by default) as JSON text: its header, row count and data.
TOPN- The first n rows of a table in an order, rows tied at the boundary all kept.
TOPNSKIP- n rows of a table after skipping a number of them, optionally in an order: for paging through rows.
UNION- The rows of two or more tables with the same number of columns, one after another, duplicates kept.
Information
36 functions.
CONTAINS- Whether a table has a row with the given values in the given columns.
CONTAINSROW- Whether a table has a row equal to the values given (as IN does).
CUSTOMDATA- The custom data text the connection was opened with, or BLANK when it has none.
EVALUATEANDLOG- Its first argument's value, or table, unchanged; OpenBI writes no evaluation log.
HASONEFILTER- Whether a column is filtered directly to exactly one value.
HASONEVALUE- Whether the filter context leaves exactly one value in a column.
ISAFTER- Whether a list of values comes after another, pair by pair, in the order given (as START AT does).
ISBOOLEAN- Tells whether a value is one of the two logical values; another name for ISLOGICAL.
ISCROSSFILTERED- Whether a table or column is filtered, directly or through another column or a relationship.
ISCURRENCY- Whether a value is a fixed decimal number (Currency).
ISDATETIME- Whether a value is a date, or a date and time.
ISDECIMAL- Whether a value is a fixed decimal number (Currency), as ISCURRENCY.
ISDOUBLE- Tells whether a value is of the Double type, the decimal number that most arithmetic gives.
ISEMPTY- Whether a table has no rows.
ISEVEN- Whether a number, rounded half away from zero to a whole number, is even.
ISFILTERED- Whether a column, or any column of a table, is filtered directly.
ISINSCOPE- Whether a column is a level grouped at the current row: TRUE on its rows, FALSE on the totals above it.
ISINT64- Whether a value is a whole number (as ISINTEGER).
ISINTEGER- Whether a value is a whole number.
ISLOGICAL- TRUE for a Boolean value, FALSE for anything else.
ISNONTEXT- Whether a value is not text; a blank is not text.
ISNUMBER- Whether a value is a number; a date counts as one.
ISNUMERIC- Whether a value is a number: whole, decimal or fixed decimal (a date doesn't count).
ISODD- Whether a number, rounded half away from zero to a whole number, is odd.
ISONORAFTER- Whether a list of values is the same as another or comes after it, pair by pair, in the order given.
ISSELECTEDMEASURE- In a calculation item, whether the measure in context is one of those named.
ISSTRING- Whether a value is text (as ISTEXT).
ISTEXT- Whether a value is text.
NAMEOF- A column's or a measure's full name as text: 'Table'[Name].
SELECTEDMEASURE- In a calculation item or a dynamic format string, the measure in context.
SELECTEDMEASUREFORMATSTRING- Inside a calculation item: the format string of the measure being computed.
SELECTEDMEASURENAME- In a calculation item, the name of the measure in context.
USERCULTURE- The model's culture, such as nb-NO (en-US when the model names none).
USERNAME- The user the query runs as (on OpenBI Server, the person's e-mail); empty text when there is none.
USEROBJECTID- The user the query runs as, the same text as USERNAME.
USERPRINCIPALNAME- The user the query runs as: on OpenBI Server, the person's e-mail, as roles' filters use it.
Logical
14 functions.
AND- TRUE when both of two conditions are TRUE (the && operator).
BLANK- A blank value.
COALESCE- The first of its arguments that is not blank.
ERROR- Stops the evaluation with an error carrying the text given.
FALSE- Boolean false, as FALSE() writes it.
IF- One value when a condition is TRUE, another (or BLANK) when it isn't.
IF.EAGER- As IF, with the same result: Power BI's hint to evaluate both branches first, which OpenBI evaluates as IF.
IFERROR- An expression's value, or another value when it gives an error (an infinite number counts as one).
ISBLANK- Whether a value is blank.
ISERROR- Whether an expression gives an error (an infinite number counts as one).
NOT- The opposite of a logical value.
OR- TRUE when either of two conditions is TRUE (the || operator).
SWITCH- The result paired with the first value equal to an expression, or the else value; SWITCH(TRUE(), …) tests conditions in turn.
TRUE- Boolean true, as TRUE() writes it.
Math
57 functions.
ABS- A number without its sign.
ACOS- The angle, in radians, whose cosine is a number.
ACOSH- Undoes COSH: for a number of 1 or more, the value of 0 or more that has it as its hyperbolic cosine.
ACOT- The angle, in radians, whose cotangent is a number.
ACOTH- Inverse hyperbolic cotangent: the value whose hyperbolic cotangent is the number.
ASIN- The angle, in radians, whose sine is a number.
ASINH- Undoes SINH: for any number, the value that has it as its hyperbolic sine.
ATAN- The angle, in radians, whose tangent is a number.
ATANH- Undoes TANH: for a number strictly between -1 and 1, the value that has it as its hyperbolic tangent.
BITAND- Bit by bit AND of two whole numbers: a bit is set where both have it.
BITLSHIFT- A whole number's bits shifted left by a number of places.
BITOR- Bit by bit OR of two whole numbers: a bit is set where either has it.
BITRSHIFT- A whole number's bits shifted right by a number of places.
BITXOR- Compares two whole numbers bit by bit and keeps the bits where they differ (exclusive or).
CEILING- A number rounded up to a multiple of a significance.
COMBIN- The number of ways to choose a number of items from a set, order not counting.
COMBINA- The number of ways to choose a number of items from a set, repeats allowed and order not counting.
CONVERT- A value converted to a type: BOOLEAN, CURRENCY, DATETIME, DOUBLE, INTEGER or STRING; text that isn't that type is an error.
COS- The cosine of an angle in radians.
COSH- Hyperbolic cosine of the number: (e^x + e^-x) / 2.
COT- For an angle in radians, its cosine divided by its sine.
COTH- Hyperbolic cotangent of the number: its hyperbolic cosine divided by its hyperbolic sine.
CURRENCY- A value as a fixed decimal number (Currency), rounded to four decimals.
DEGREES- An angle in radians, in degrees.
DIVIDE- One number divided by another; BLANK, or an alternate result, when the divisor is zero or blank.
EVEN- A number rounded away from zero to an even whole number.
EXP- e raised to a power.
FACT- The factorial of a whole number.
FLOOR- Takes a number down to a multiple of a step you give, such as whole pallets or quarter hours.
GCD- The largest whole number that goes into both of two whole numbers without a remainder.
INT- A number rounded down to a whole number.
ISO.CEILING- A number rounded up to a multiple of a significance (1 by default), whatever the significance's sign.
LCM- The smallest whole number that both of two whole numbers go into, such as the day two delivery cycles meet.
LN- The natural logarithm of a number.
LOG- A number's logarithm, base 10 unless a base is given.
LOG10- Logarithm in base 10 of the number: LOG10(1000) is 3.
MOD- The remainder of a division, with the divisor's sign.
MROUND- Takes a number to whichever multiple of a step lies closest, such as the nearest quarter hour.
ODD- A number rounded away from zero to an odd whole number.
PERMUT- The number of ways to arrange a number of items chosen from a set, order counting.
PI- The constant pi, a circle's circumference divided by its diameter: 3.14159265358979.
POWER- Multiplies a number by itself a given number of times, or takes a root for a fractional power; the same as the ^ operator.
QUOTIENT- The whole part of a division.
RADIANS- An angle in degrees, in radians.
RAND- A random number from 0 up to 1, different at each evaluation.
RANDBETWEEN- A random whole number between two numbers, both included, different at each evaluation.
ROUND- A number rounded to a number of decimals (negative: to tens, hundreds…), halves away from zero.
ROUNDDOWN- A number rounded towards zero to a number of decimals.
ROUNDUP- A number rounded away from zero to a number of decimals.
SIGN- 1 for a positive number, -1 for a negative one, 0 for zero.
SIN- The sine of an angle in radians.
SINH- Hyperbolic sine of the number: (e^x - e^-x) / 2.
SQRT- The number's square root: the non-negative value that, squared, gives it.
SQRTPI- Multiplies a number by pi and takes the square root of the product.
TAN- The tangent of an angle in radians.
TANH- Hyperbolic tangent of the number: its hyperbolic sine divided by its hyperbolic cosine.
TRUNC- A number with its decimals cut off, or cut to a number of decimals.
Text
22 functions.
COMBINEVALUES- Texts joined with a delimiter (a constant text that isn't empty).
CONCATENATE- Two texts joined into one (the & operator).
CONTAINSSTRING- Whether a text contains another, ignoring case, with SEARCH's wildcards (? and *).
CONTAINSSTRINGEXACT- Whether a text contains another, case counting.
EXACT- Whether two texts are the same, case counting.
FIND- Where a text first appears in another, case counting, from a start position; a value of your choice, or an error, when it doesn't.
FIXED- A number rounded to a number of decimals (2 by default) as text, with or without thousands separators.
FORMAT- A value as text in a format string, in the model's culture or one named.
LEFT- The first characters of a text (one by default).
LEN- How many characters a text has.
LOWER- A text in lower case.
MID- A number of characters from a text, from a position.
REPLACE- A text with a number of its characters, from a position, replaced by another text.
REPT- A text repeated a number of times.
RIGHT- The last characters of a text (one by default).
SEARCH- Where a text first appears in another, ignoring case and width, with ? and * wildcards; a value of your choice, or an error, when it doesn't.
SUBSTITUTE- A text with every instance of another text, or only the n-th, replaced.
TRIM- A text without spaces at its ends, and with single spaces between its words.
UNICHAR- The character of a Unicode code point.
UNICODE- The Unicode code point of a text's first character.
UPPER- A text in upper case.
VALUE- A number read from text, in the model's culture.
Parent and child
5 functions.
PATH- The keys from the top of a parent-child hierarchy down to the current row, joined with |.
PATHCONTAINS- Whether a PATH text holds a key.
PATHITEM- The key at a position in a PATH text, counted from the top.
PATHITEMREVERSE- The key at a position in a PATH text, counted from the current row up.
PATHLENGTH- The number of keys in a PATH text: the row's depth in the hierarchy.
Statistics
35 functions, 7 of them OpenBI's own.
BETA.DIST- The beta distribution at a value, as a density or a cumulative probability, on 0 to 1 or an interval given.
BETA.INV- The value at which the beta distribution's cumulative probability is the one given.
BOXPLOTOpenBI- A distribution's box in one row: count, quartiles, whiskers (1.5 IQR), mean and outliers, as R's quantile type 7 and ggplot2 give them.
CHISQ.DIST- The chi-squared distribution at a value, as a density or a cumulative probability.
CHISQ.DIST.RT- The chi-squared distribution's right-tail probability at a value.
CHISQ.INV- The value whose chi-squared left-tail probability is the one given.
CHISQ.INV.RT- The value whose chi-squared right-tail probability is the one given.
CONFIDENCE.NORM- Half the width of a confidence interval for a mean, by the normal distribution.
CONFIDENCE.T- The margin of error either side of a sample's mean (confidence 1 - alpha), from Student's t.
CORRELOpenBI- The correlation between two expressions over a table's rows (Pearson's); BLANK for fewer than two rows or no variation.
CORREL.SPEARMANOpenBI- The rank correlation between two expressions over a table's rows (Spearman's; ties share their average rank).
DECOMPOSEOpenBI- A time series' trend, season and remainder (STL, as R's stl), or how strong each is.
DENSITYOpenBI- A distribution's density curve as a table: a Gaussian kernel, as R's density.
EXPON.DIST- The exponential distribution at a value, as a density or a cumulative probability.
GEOMEAN- The geometric mean of a column's numbers.
GEOMEANX- The geometric mean of an expression over a table's rows.
HISTOGRAMOpenBI- Sorts an expression's values into bins and counts them, one row a bin: at round break points by default, or into a given number of bins of equal width.
LINEST- A least-squares line through columns of the model, in one row: slopes, intercept, their standard errors and the fit's statistics.
LINESTX- A least-squares line through expressions over a table's rows, in one row: slopes, intercept, standard errors, R², F and sums of squares.
NORM.DIST- The normal distribution of a mean and standard deviation at a value, as a density or a cumulative probability.
NORM.INV- The value whose cumulative probability, in the normal distribution of a mean and standard deviation, is the one given.
NORM.S.DIST- The standard normal distribution at a value, as a density or a cumulative probability.
NORM.S.INV- The value whose cumulative probability in the standard normal distribution is the one given.
PERCENTILE.EXC- A percentile of a column's values, k strictly between 0 and 1.
PERCENTILE.INC- A percentile of a column's values, k from 0 to 1.
PERCENTILEX.EXC- A percentile of an expression over a table's rows, k strictly between 0 and 1.
PERCENTILEX.INC- A percentile of an expression over a table's rows, k from 0 to 1.
POISSON.DIST- The Poisson distribution of a mean at a number of events, as a probability or a cumulative one.
RANK.EQ- A value's rank among a column's values: 1 the largest, or the smallest with order 1; text ranks too.
T.DIST- Student's t distribution at a value, as a density or a left-tail probability.
T.DIST.2T- Student's t distribution's two-tailed probability at a value.
T.DIST.RT- Student's t distribution's right-tail probability at a value.
T.INV- The value whose left-tail probability in Student's t distribution is the one given.
T.INV.2T- The positive t value whose two tails together hold a given probability: the critical value of a two-sided t test.
TRENDLINEOpenBI- Trend lines or curves of y on x (linear, polynomial, log, exp, power) with their confidence and prediction bands and R², as R's lm.
Financial
51 functions.
ACCRINT- The interest a security that pays periodically has accrued by settlement.
ACCRINTM- For a security paying interest only at maturity, how much interest it has built up by settlement.
AMORDEGRC- An asset's depreciation for a period by the French declining method, with a coefficient by its life.
AMORLINC- An asset's depreciation for a period by the French straight-line method, the first period prorated.
COUPDAYBS- How many days of the current coupon period have passed at settlement.
COUPDAYS- How many days the coupon period around the settlement date lasts.
COUPDAYSNC- How many days remain from settlement until the coupon after it.
COUPNCD- The date of the coupon that follows settlement.
COUPNUM- How many coupons a bond still pays between settlement and maturity.
COUPPCD- The date of the coupon paid last before settlement, or on it.
CUMIPMT- The interest paid on a loan from one period to another, together.
CUMPRINC- The principal paid on a loan from one period to another, together.
DB- An asset's depreciation for a period at a fixed declining rate.
DDB- An asset's depreciation for a period by the declining balance at a factor (2: double), never below its salvage value.
DISC- A security's discount rate.
DOLLARDE- A price written as a whole part and a fraction of a denominator (1.02 for 1 2/16), as a decimal number.
DOLLARFR- A decimal price written as a whole part and a fraction of a denominator.
DURATION- A bond's Macaulay duration, in years.
EFFECT- The effective annual rate of a nominal rate compounded a number of times a year.
FV- The future value of an investment at a constant rate, from payments each period and a present value.
INTRATE- For a fully invested security, the rate its investment and redemption amounts imply.
IPMT- The interest part of a loan's payment in a period.
ISPMT- The interest paid in a period of a loan whose principal is repaid in equal parts.
MDURATION- A bond's modified duration.
NOMINAL- The nominal annual rate of an effective rate compounded a number of times a year.
NPER- The number of periods a loan or an investment takes at a constant payment and rate.
ODDFPRICE- The price of a bond whose first coupon period is shorter or longer than the rest.
ODDFYIELD- The yield of a security whose first period is short or long.
ODDLPRICE- The price of a bond whose last coupon period is shorter or longer than the rest.
ODDLYIELD- The yield of a security whose last period is short or long.
PDURATION- How many periods a value takes to grow to a target at a fixed rate.
PMT- A loan's payment each period at a constant rate.
PPMT- The principal part of a loan's payment in a period.
PRICE- The price per 100 of a bond that pays coupons, from its yield.
PRICEDISC- What a security sold at a discount is worth per 100 of face value.
PRICEMAT- What a security that pays its interest at maturity is worth per 100 of face value.
PV- The present value of payments each period and a future value, at a constant rate.
RATE- The interest rate per period of an annuity, solved from a guess.
RECEIVED- What a security bought at a discount pays back at maturity.
RRI- The rate per period that grows a present value to a future one in a number of periods.
SLN- Straight-line depreciation: the same amount every period of an asset's life.
SYD- How much an asset loses in one period when depreciated by the sum-of-years'-digits method.
TBILLEQ- A Treasury bill's yield restated so it can be compared with a bond's.
TBILLPRICE- What a Treasury bill costs, in percent of its face value, for a discount rate.
TBILLYIELD- A Treasury bill's yield.
VDB- An asset's declining-balance depreciation for any period, partial ones too, turning to straight line when that is more.
XIRR- The internal rate of return of cash flows on dates, each row of a table a value and its date.
XNPV- The net present value at a rate of cash flows on dates, each row of a table a value and its date.
YIELD- The yield of a bond that pays coupons, from its price.
YIELDDISC- Yield per year of a security sold at a discount.
YIELDMAT- For a security that pays all its interest at maturity, the yield it earns per year.
Window and visual calculations
25 functions.
COLLAPSE- In a visual calculation, an expression evaluated a level up an axis of the visual, or at the level of the columns named.
COLLAPSEALL- In a visual calculation, an expression evaluated at the top level of an axis.
EXPAND- In a visual calculation, an expression evaluated a level down an axis of the visual, or at the level of the columns named.
EXPANDALL- In a visual calculation, an expression evaluated at the bottom level of an axis.
FIRST- In a visual calculation, a column's value at the first row along an axis.
INDEX- The row at a position in the current row's partition, in an order (1 the first, -1 the last).
ISATLEVEL- For a visual calculation's current row, whether that column is one of its level's columns.
LAST- In a visual calculation, a column's value at the last row along an axis.
LOOKUP- In a visual calculation, an expression's value at the row some columns' values name, the others kept from the current row.
LOOKUPWITHTOTALS- As LOOKUP, with the columns not named taken at their totals.
MATCHBY- In a window function, the columns that identify the current row.
MOVINGAVERAGE- In a visual calculation, the average of a column over a window of rows along an axis, up to the current one.
NEXT- In a visual calculation, a column's value at the next row along an axis, or a number of rows on.
OFFSET- The row a number of rows before or after the current one, within its partition and order.
ORDERBY- In a window function, the expressions, and their directions, that order each partition.
PARTITIONBY- In a window function, the columns that split its table into partitions.
PREVIOUS- In a visual calculation, a column's value at the previous row along an axis, or a number of rows back.
RANGE- In a visual calculation, the rows within a number of steps of the current one along an axis.
RANK- The current row's rank within its partition in an order; ties share a rank, skipping the next (SKIP) or not (DENSE).
ROWNUMBER- The current row's number within its partition in an order, unique to it.
RUNNINGSUM- In a visual calculation, the sum of a column from the first row along an axis to the current one.
SAMPLE- A number of a table's rows spread evenly over an order, the first and last included.
SAMPLEAXISWITHLOCALMINMAX- A line chart's sample of a table: each bin's least and greatest values per series; past the size asked, Power BI Desktop's own bins may differ.
SAMPLECARTESIANPOINTSBYCOVER- A scatter chart's sample of a table: a row for each occupied cell of a grid; past the size asked, Power BI Desktop's own sample may differ.
WINDOW- The rows from one position to another in the current row's partition, each position counted from the current row or from an end of the partition.
Images and colours
3 functions, all OpenBI's own.
COLORNAMEOpenBI- The nearest named colour to a colour, given as #RRGGBB or as red, green and blue.
IMAGECOLOROpenBI- One colour of a picture at an address or a data: URI, by rank (1: the dominant one), as #RRGGBB.
IMAGECOLORSOpenBI- The colours of a picture at an address or a data: URI, the most used first, with their names and shares.
Predictions
1 function, all OpenBI's own.
PREDICTOpenBI- A prediction's scores for each row, or a forecast's dates, actuals and forecasts, from the version in use.
Operations research
4 functions, all OpenBI's own.
OPTIMIZEOpenBI- An operations research plan in use, solved again with other values for its parameters: its rows with the new decisions (a what-if).
OPTIMIZE.VALUEOpenBI- The objective of a plan in use, solved again with other values for its parameters.
ROUTESOpenBI- Routes for vehicles from a depot through the places in view, with capacities, solved with OR-Tools.
SCHEDULEOpenBI- A job shop's schedule: each task's machine, start and end, the whole as short as CP-SAT finds.
Quantitative finance
15 functions, all OpenBI's own.
MONTECARLOOpenBI- A series simulated ahead with QuantLib: its paths, their percentiles step by step, where they end, or its risk in a row.
QUANT.BOND.ACCRUEDOpenBI- A bond's accrued interest per 100.
QUANT.BOND.CONVEXITYOpenBI- A bond's convexity, from its yield.
QUANT.BOND.DURATIONOpenBI- A bond's modified duration, from its yield.
QUANT.BOND.DV01OpenBI- A bond's DV01: the change in its price for one basis point of yield.
QUANT.BOND.PRICEOpenBI- A bond's clean price per 100, from its yield, with QuantLib.
QUANT.BOND.YIELDOpenBI- A bond's yield, from its clean price.
QUANT.CURVEOpenBI- A yield curve bootstrapped from a table of deposits, swaps or zero rates: zero, forward and discount, month by month.
QUANT.FX.FORWARDOpenBI- An FX forward rate, from the spot and the two currencies' rates.
QUANT.KEYRATEOpenBI- A bond's DV01 by the curve's instruments, each bumped a basis point (key rate durations).
QUANT.OPTION.GREEKOpenBI- One of an option's greeks: delta, gamma, vega, theta or rho.
QUANT.OPTION.IMPLIEDVOLOpenBI- The volatility an option's price implies.
QUANT.OPTION.PRICEOpenBI- An option's price, European (the default) or American, with QuantLib.
VALUATIONOpenBI- A valuation in use: its positions, and their change at a parallel shift of yields in basis points.
VALUATION.VALUEOpenBI- One number of a valuation in use: its market value, its DV01, the change at a shift, or a risk figure saved with it.
Networks
26 functions, all OpenBI's own.
NETWORK.CENTRALITYOpenBI- Each node's centrality: fan-in and fan-out, closeness, eigenvector, hubs and authorities, clustering.
NETWORK.CLIQUESOpenBI- The network's cliques: groups where every node links to every other, the largest first.
NETWORK.COMMUNITIESOpenBI- Communities of nodes (Louvain, Leiden, Walktrap and others), with their modularity.
NETWORK.COMPAREOpenBI- How alike two partitions of the same nodes into communities are, such as two snapshots' (NMI, VI, adjusted Rand and more), over the nodes in both.
NETWORK.CUTSOpenBI- The weakest ways to split a network by capacity: each cut's capacity, the pairs of nodes it separates and its links.
NETWORK.CYCLESOpenBI- Round trips: cycles in the network, as money moving in a circle.
NETWORK.EDGESOpenBI- Lists the links a table's rows make, one row a link, with the weights of repeated rows added together.
NETWORK.EMBEDOpenBI- Coordinates for each node from the network's shape, to cluster with KMEANS.
NETWORK.FLOWOpenBI- The maximum flow between two nodes, and its cut.
NETWORK.GRAPHOpenBI- The whole network's measures in one row: size, density, components, diameter, modularity and more.
NETWORK.LAYOUTOpenBI- Coordinates to draw the network's nodes, heavier edges closer.
NETWORK.LINKSOpenBI- Measures every link of the network a table's rows make, as NETWORK.NODES does every node: its betweenness, whether it is a bridge and which block it is in.
NETWORK.MATCHOpenBI- Numbers one partition's communities as another's (an earlier snapshot's), each after the one it shares the most nodes with, so a community keeps its number and colour.
NETWORK.NODESOpenBI- Measures every node of the network a table's rows make: its links, PageRank, betweenness, community, component, core and whether it is a cut point.
NETWORK.PATHOpenBI- The shortest path, in hops, between two nodes.
NETWORK.PREDICTOpenBI- Likely links: pairs of nodes that are not linked but should be, by the neighbours they share or a hierarchical random graph, best first.
NETWORK.PROPAGATEOpenBI- Risk spread from flagged nodes (personalized PageRank): each node's risk and rank.
NETWORK.REACHOpenBI- The nodes reached from flagged ones, and in how many hops.
NETWORK.RINGSOpenBI- Rings: groups of nodes held together by triangles, such as accounts sharing phones and addresses, with their size, density and level.
NETWORK.ROLLUPOpenBI- Totals along every path from the starts, the weights multiplied along each path: a forecast exploded into parts, costs rolled up into products, effective shares through chains of ownership, costs allocated back and forth; loops refused or solved.
NETWORK.SENSITIVITYOpenBI- The links each node's standing depends on: how fast each node's PageRank, eigenvector centrality, strength, closeness, harmonic centrality or betweenness moves as each link's weight changes.
NETWORK.SIMILARITYOpenBI- Finds the nodes that are alike because they have the same neighbours, such as beers bought by the same customers.
NETWORK.SIROpenBI- Spreading through the network (the SIR model), step by step.
NETWORK.SIR.NODESOpenBI- Spreading through the network (the SIR model): how often and how soon each node is reached.
NETWORK.TRIADSOpenBI- The network's triads: patterns of three nodes, counted.
NETWORK.WEAKPOINTSOpenBI- Single points of failure: the nodes and links whose loss alone splits the network, with the nodes and pairs each one cuts off.
Survival analysis
15 functions, all OpenBI's own.
SURVIVAL.ATOpenBI- A Kaplan-Meier curve's value at a time, for a sentence or a card.
SURVIVAL.COXOpenBI- A Cox model: hazard ratios and the proportional hazards test.
SURVIVAL.COX.CURVEOpenBI- Survival curves adjusted by a Cox model, for each value of one covariate.
SURVIVAL.COX.CVOpenBI- A penalized Cox model's cross-validation: the deviance at each penalty, and the one chosen.
SURVIVAL.COX.PATHOpenBI- A penalized Cox model's coefficients along the penalty's path.
SURVIVAL.COX.PENALIZEDOpenBI- A penalized Cox model (the lasso and elastic net, as glmnet).
SURVIVAL.COX.PREDICTOpenBI- A Cox model's prediction for each row: risk, survival to a horizon, median, and survival from now.
SURVIVAL.COX.ZPHOpenBI- Whether each term's effect holds over time (scaled Schoenfeld residuals, as R's cox.zph).
SURVIVAL.COX.ZPH.SMOOTHOpenBI- The smoothed effect over time and its band, as R's plot.cox.zph draws it.
SURVIVAL.CURVEOpenBI- Kaplan-Meier survival curves, with censoring and delayed entry.
SURVIVAL.HAZARDOpenBI- The hazard rate over time, smoothed, with its band.
SURVIVAL.LIFETABLEOpenBI- An actuarial life table, in intervals of a width.
SURVIVAL.MEDIANOpenBI- A Kaplan-Meier curve's median, for a sentence or a card.
SURVIVAL.SUMMARYOpenBI- A survival curve's median and expected lifetime to a horizon.
SURVIVAL.TESTOpenBI- The log-rank test between groups' survival curves.
Many variables at once
7 functions, all OpenBI's own.
KMEANSOpenBI- Each row's k-means cluster, its distance to the centre and its silhouette.
KMEANS.CENTERSOpenBI- The centres of the k-means clusters, with their sizes and scatter.
KMEANS.ELBOWOpenBI- The scatter about the centres for each number of clusters, to choose it.
PCA.COMPONENTSOpenBI- Principal components: each one's variance and its share.
PCA.LOADINGSOpenBI- Principal components' loadings: how much each variable weighs in each.
PCA.SCORESOpenBI- Each row's place on the principal components.
UMAPOpenBI- A map of the rows in two dimensions, near rows near each other (UMAP).