| Aggregation |
AVG(X) |
`AVERAGE(X)` |
|
| Aggregation |
COUNT(X) |
`COUNT(X)` or `COUNTA(X)` |
`COUNT` counts numerical values; `COUNTA` counts non-blank values. |
| Aggregation |
COUNT_DISTINCT(X) |
`DISTINCTCOUNT(X)` |
|
| Aggregation |
MAX(X) |
`MAX(X)` |
|
| Aggregation |
MEDIAN(X) |
`MEDIAN(X)` |
|
| Aggregation |
MIN(X) |
`MIN(X)` |
|
| Aggregation |
PERCENTILE(X,N) |
`PERCENTILE.EXC(X, N/100)` or `PERCENTILE.INC(X, N/100)` |
`N` in DAX is a decimal value between 0 and 1. `PERCENTILE.EXC` is exclusive, `PERCENTILE.INC` is inclusive. |
| Aggregation |
STDDEV(X) |
`STDEV.S(X)` or `STDEV.P(X)` |
`STDEV.S` calculates the sample standard deviation; `STDEV.P` calculates the population standard deviation. |
| Aggregation |
SUM(X) |
`SUM(X)` |
|
| Aggregation |
VARIANCE(X) |
`VAR.S(X)` or `VAR.P(X)` |
`VAR.S` calculates the sample variance; `VAR.P` calculates the population variance. |
| Arithmetic |
ABS(X) |
`ABS(X)` |
|
| Arithmetic |
ACOS(X) |
`ACOS(X)` |
|
| Arithmetic |
ASIN(X) |
`ASIN(X)` |
|
| Arithmetic |
ATAN(X) |
`ATAN(X)` |
|
| Arithmetic |
CEIL(X) |
`CEILING(X, 1)` or `ROUNDUP(X, 0)` |
`CEILING(X, 1)` rounds up to the nearest multiple of 1. `ROUNDUP(X, 0)` rounds up to zero decimal places. |
| Arithmetic |
COS(X) |
`COS(X)` |
|
| Arithmetic |
FLOOR(X) |
`FLOOR(X, 1)` or `ROUNDDOWN(X, 0)` |
`FLOOR(X, 1)` rounds down to the nearest multiple of 1. `ROUNDDOWN(X, 0)` rounds down to zero decimal places. |
| Arithmetic |
LOG(X) (base 2) |
`LN(X) / LN(2)` |
DAX's `LN` function is for natural logarithm (base e). To get log base 2, divide by `LN(2)`. |
| Arithmetic |
LOG10(X) |
`LOG10(X)` |
|
| Arithmetic |
NARY_MAX(X, Y [,Z]*) |
`MAX(X, MAX(Y, Z))` (nested `MAX` for multiple arguments) |
DAX doesn't have a direct `NARY_MAX` but you can achieve the same result by nesting `MAX` functions. |
| Arithmetic |
NARY_MIN(X, Y [,Z]*) |
`MIN(X, MIN(Y, Z))` (nested `MIN` for multiple arguments) |
Similar to `NARY_MAX`, you can nest `MIN` functions. |
| Arithmetic |
POWER(X, Y) |
`POWER(X, Y)` |
|
| Arithmetic |
ROUND(X, Y) |
`ROUND(X, Y)` |
|
| Arithmetic |
SIN(X) |
`SIN(X)` |
|
| Arithmetic |
SQRT(X) |
`SQRT(X)` |
|
| Arithmetic |
TAN(X) |
`TAN(X)` |
|
| Conditional |
CASE WHEN condition THEN result [ELSE else_result] END |
`SWITCH(TRUE(), condition1, result1, condition2, result2, ..., else_result)` |
A `SWITCH` statement with `TRUE()` as the first argument allows for evaluating multiple conditions sequentially. |
| Conditional |
CASE input_expression WHEN expr_to_match THEN result [ELSE result] END |
`SWITCH(input_expression, expression_to_match1, result1, expression_to_match2, result2, ..., else_result)` |
This `SWITCH` form is for direct matching against an `input_expression`. |
| Conditional |
COALESCE(field_expression[,field_expression, ...]) |
`COALESCE(field_expression1, field_expression2, ...)` |
|
| Conditional |
IF(condition, true_result, false_result) |
`IF(condition, true_result, false_result)` |
|
| Conditional |
IFNULL(input_expression, null_result) |
`IF(ISBLANK(input_expression), null_result, input_expression)` or `COALESCE(input_expression, null_result)` |
`ISBLANK` checks for blank values, then returns the `null_result`. `COALESCE` is also a concise option. |
| Conditional |
NULLIF(input_expression, expression_to_match) |
`IF(input_expression = expression_to_match, BLANK(), input_expression)` |
Returns `BLANK()` if the two expressions match. |
| Date |
CURRENT_DATE([time_zone]) |
`TODAY()` |
`TODAY()` returns the current date. For time zone specificity, you might need to handle this in Power Query or through Power BI's regional settings. |
| Date |
CURRENT_DATETIME([time_zone]) |
`NOW()` |
`NOW()` returns the current date and time. |
| Date |
DATE(year, month, day) |
`DATE(year, month, day)` |
|
| Date |
DATE_DIFF(X, Y) (days) |
`DATEDIFF(Y, X, DAY)` |
Note the order of arguments in `DATEDIFF` to get `X - Y`. |
| Date |
DATE_FROM_UNIX_DATE(integer) |
`DATE(1970,1,1) + integer` |
This adds the number of days to the Unix epoch date. |
| Date |
DATETIME(year, month, day, hour, minute, second) |
`DATE(year, month, day) + TIME(hour, minute, second)` |
If your column is a datetime type, direct assignment works. Otherwise, combine `DATE` and `TIME` functions. |
| Date |
DATETIME_ADD(datetime_expression, INTERVAL integer part) |
`datetime_expression + INTERVAL(integer, part)` or `DATEADD(datetime_expression, integer, part)` |
`INTERVAL` is used for adding simple whole number units. `DATEADD` is more flexible and commonly used with date tables. |
| Date |
DATETIME_DIFF(date_expression, date_expression, part) |
`DATEDIFF(date_expression2, date_expression1, part)` |
Calculates the difference between two dates/datetimes in specified units (`DAY`, `MONTH`, `YEAR`, `HOUR`, `MINUTE`, `SECOND`). |
| Date |
DATETIME_TRUNC(date_expression, part) |
`STARTOF...` functions (e.g., `STARTOFMONTH(date_expression)`) |
DAX often uses specific `STARTOF...` or `ENDOF...` functions (`STARTOFMONTH`, `STARTOFYEAR`, etc.) for truncation. |
| Date |
DAY(date_expression) |
`DAY(date_expression)` |
|
| Date |
EXTRACT(part FROM date_expression) |
`YEAR(date_expression)`, `MONTH(date_expression)`, `DAY(date_expression)`, etc. |
You'll use the specific DAX function corresponding to the `part` you want to extract. |
| Date |
FORMAT_DATETIME(format_string, datetime_expression) |
`FORMAT(datetime_expression, format_string)` |
DAX `FORMAT` strings may differ slightly; refer to DAX documentation for exact formats. |
| Date |
HOUR(datetime_expression) |
`HOUR(datetime_expression)` |
|
| Date |
MINUTE(datetime_expression) |
`MINUTE(datetime_expression)` |
|
| Date |
MONTH(date_expression) |
`MONTH(date_expression)` |
|
| Date |
PARSE_DATE(format_string, text) |
`DATEVALUE(text)` or a combination of text functions and `DATE` |
`DATEVALUE` works for common date string formats. For complex custom formats, you'll need to parse the text manually using `LEFT`, `MID`, `RIGHT`, etc., and then use `DATE`. |
| Date |
PARSE_DATETIME(format_string, text) |
`DATETIMEVALUE(text)` or a combination of text functions and `DATETIME` |
Similar to `PARSE_DATE`, `DATETIMEVALUE` handles common formats. |
| Date |
QUARTER(date_expression) |
`ROUNDUP(MONTH(date_expression) / 3, 0)` |
Calculates the quarter based on the month number. |
| Date |
SECOND(datetime_expression) |
`SECOND(datetime_expression)` |
|
| Date |
TODATE(X, Input Format, Output Format) |
`FORMAT(X, "Output Format")` (after ensuring X is a date/datetime) |
This typically involves formatting an existing date/datetime value into a string. |
| Date |
TODAY([time_zone]) |
`TODAY()` |
|
| Date |
UNIX_DATE(date_expression) |
`INT(date_expression - DATE(1970,1,1))` |
Returns the number of days since January 1, 1970. |
| Date |
WEEK(Date) |
`WEEKNUM(Date, 1)` or `WEEKNUM(Date, 2)` |
`WEEKNUM` has an optional second argument to specify the start day of the week (1 for Sunday, 2 for Monday). |
| Date |
WEEKDAY(Date) |
`WEEKDAY(Date, 1)` or `WEEKDAY(Date, 2)` |
`WEEKDAY` also has an optional second argument for start day of the week. |
| Date |
YEAR(Date) |
`YEAR(Date)` |
|
| Date |
YEARWEEK(Date) |
`FORMAT(Date, "YYYY") & FORMAT(WEEKNUM(Date, 1), "00")` |
This concatenates the year and a two-digit week number. |
| Geo |
TOCITY(X [,Input Format]) |
No direct DAX equivalent. |
These typically require external data sources, lookup tables, or custom visual/Power Query solutions. DAX does not natively include geographical lookup functions. |
| Geo |
TOCONTINENT(X [,Input Format]) |
No direct DAX equivalent. |
|
| Geo |
TOCOUNTRY(X [,Input Format]) |
No direct DAX equivalent. |
|
| Geo |
TOREGION(X [,Input Format]) |
No direct DAX equivalent. |
|
| Geo |
TOSUBCONTINENT(X [,Input Format]) |
No direct DAX equivalent. |
|
| Miscellaneous |
CAST(field_expression AS TYPE) |
`CONVERT(field_expression, TYPE)` |
`TYPE` can be `INTEGER`, `TEXT`, `DATETIME`, etc. |
| Miscellaneous |
HYPERLINK(URL, link label) |
Set **Data Category** of a column to "Web URL". The "link label" is the column's displayed value. |
DAX itself doesn't have a direct display function like `HYPERLINK`. The column's data category in the Power BI model handles this. |
| Miscellaneous |
IMAGE(Image URL, [Alternative Text]) |
Set **Data Category** of a column to "Image URL". |
Similar to `HYPERLINK`, this is handled by the data model's data category. Alternative text is not directly supported by a DAX function for image display. |
| Miscellaneous |
NATIVE_DIMENSION("SQL_EXPRESSION","TYPE") |
No direct DAX equivalent. |
DAX operates on the data model, not raw SQL expressions from the underlying data source. You'd typically use Power Query to prepare the data with SQL expressions before it reaches the DAX model. |
| Text |
CONCAT(X, Y) |
`X & Y` or `CONCATENATE(X, Y)` |
The `&` operator is a common and concise way to concatenate strings. |
| Text |
CONTAINS_TEXT(X, text) (case-sensitive) |
`FIND(text, X, 1, 0) > 0` |
`FIND` is case-sensitive. The `1` is the start position, `0` is the value returned if `text` is not found. |
| Text |
ENDS_WITH(X, text) (case-sensitive) |
`RIGHT(X, LEN(text)) = text` |
Compares the rightmost `LEN(text)` characters of `X` to `text`. |
| Text |
LEFT_TEXT(X, length) |
`LEFT(X, length)` |
|
| Text |
LENGTH(X) |
`LEN(X)` |
|
| Text |
LOWER(X) |
`LOWER(X)` |
|
| Text |
REGEXP_CONTAINS(X, regular_expression) |
No direct DAX equivalent for regular expressions. |
DAX does not have built-in regular expression functions. You'd need to use a combination of standard text functions (`FIND`, `SEARCH`, `LEFT`, `MID`, `RIGHT`, `REPLACE`, `SUBSTITUTE`) or leverage Power Query (M language), or integrate with Python/R in Power BI for regex capabilities. |
| Text |
REGEXP_EXTRACT(X, regular_expression) |
No direct DAX equivalent for regular expressions. |
|
| Text |
REGEXP_MATCH(X, regular_expression) |
No direct DAX equivalent for regular expressions. |
|
| REGEXP_REPLACE(X, regular_expression, replacement) |
No direct DAX equivalent for regular expressions. Use `SUBSTITUTE` for exact string replacement. |
|
| Text |
REPLACE(X, Y, Z) |
`SUBSTITUTE(X, Y, Z)` |
Replaces all occurrences of `Y` in `X` with `Z`. |
| Text |
RIGHT_TEXT(X, length) |
`RIGHT(X, length)` |
|
| Text |
STARTS_WITH(X, text) (case-sensitive) |
`LEFT(X, LEN(text)) = text` |
Compares the leftmost `LEN(text)` characters of `X` to `text`. |
| Text |
SUBSTR(X, start index, length) |
`MID(X, start index, length)` |
The `start index` in `MID` is 1-based, matching your description. |
| Text |
TRIM(X) |
`TRIM(X)` |
Removes leading, trailing, and repeated spaces. |
| Text |
UPPER(X) |
`UPPER(X)` |
|
Comments
Post a Comment