DAX (Data Analysis Expressions) is a powerful formula language used in Microsoft Power BI, Excel, and other data tools. It allows users to create calculated columns, measures, and custom tables to enhance data analysis.
In this article, we’ll explore various categories of DAX functions, along with their syntax and a brief description of each.
📅 Date and Time Functions
- DATE(year, month, day) – Creates a date from year, month, and day components.
- DATEDIFF(start_date, end_date, interval) – Returns the number of intervals between two dates.
- DAY(date) – Returns the day part from a date.
- MONTH(date) – Extracts the month from a date.
- HOUR(time) – Returns the hour from a time value.
- TODAY() – Returns the current date.
- TIME(hour, minute, second) – Constructs a time value from its components.
- WEEKDAY(date) – Returns the day of the week (1 = Sunday, 7 = Saturday).
🔀 Logical Functions
- AND(condition1, condition2) – Returns TRUE if both conditions are TRUE.
- FALSE() – Returns the logical value FALSE.
- IF(condition, true_value, false_value) – Executes logic based on a condition.
- IN(value, values) – Checks if a value exists in a given list.
- NOT(condition) – Returns the opposite of a logical value.
- OR(condition1, condition2) – Returns TRUE if either condition is TRUE.
- SWITCH(expression, value1, result1, …, else_result) – Compares an expression with values and returns matching results.
- TRUE() – Returns the logical value TRUE.
🔤 Text Functions
- BLANK() – Returns a blank value.
- CODE(text) – Returns the ASCII code of the first character in a string.
- EXACT(text1, text2) – Checks if two strings are exactly the same (case-sensitive).
- FIND(find_text, within_text, [start_num]) – Finds one string inside another.
- CONCATENATE(text1, text2) – Joins two strings together.
- LEN(text) – Returns the number of characters in a string.
- LOWER(text) – Converts text to lowercase.
- REPLACE(old_text, start_num, num_chars, new_text) – Replaces part of a string with another string.
📊 Statistical Functions
- ADDCOLUMNS(table, column_name, expression) – Adds calculated columns to a table.
- AVERAGE(column) – Returns the average value.
- COUNT(column) – Returns the number of values.
- MEDIAN(column) – Returns the median (middle) value.
- MAX(column) – Returns the highest value.
- MIN(column) – Returns the lowest value.
- GENERATE(table1, table2) – Produces a table with all combinations of rows from two tables.
- CROSSJOIN(table1, table2) – Performs a cross join on two tables.
ℹ️ Information Functions
- CONTAINS(table, column, value) – Checks if a table contains a specific value.
- ISBLANK(value) – Determines if a value is blank.
- ISERROR(value) – Checks if a value results in an error.
- ISEVEN(number) – Checks if a number is even.
- ISLOGICAL(value) – Determines if a value is a logical TRUE or FALSE.
- ISTEXT(value) – Checks if a value is text.
- USERNAME() – Returns the domain\username of the current user.
- ISODD(number) – Checks if a number is odd.
| Function | Syntax | Description |
| LEFT | LEFT(text, num_chars) | Returns the specified number of characters from the start of a text string. |
| LEN | LEN(text) | Returns the number of characters in a text string. |
| LOWER | LOWER(text) | Converts all letters in a text string to lowercase. |
| MID | MID(text, start_num, num_chars) | Returns a string of characters from the middle of a text string. |
| REPLACE | REPLACE(old_text, start_num, num_chars, new_text) | Replaces part of a text string with a different text string. |
| REPT | REPT(text, number_times) | Repeats text a given number of times. |
| RIGHT | RIGHT(text, num_chars) | Returns the specified number of characters from the end of a text string. |
| SUBSTITUTE | SUBSTITUTE(text, old_text, new_text) | Replaces existing text with new text in a text string. |
| TOJSON | TOJSON(table) | Converts the records of a table into a JSON text. |
| TRIM | TRIM(text) | Removes all extra spaces from a text string. |
| UNICHAR | UNICHAR(number) | Returns the Unicode character referenced by the given numeric value. |
| UNICODE | UNICODE(text) | Returns the code point for the first character of the text. |
| UPPER | UPPER(text) | Converts a text string to all uppercase letters. |
| VALUE | VALUE(text) | Converts a text string that represents a number to a number. |
| TOCSV | TOCSV(table) | Converts the records of a table into a CSV text. |
📌 Final Thoughts
Understanding and using DAX functions effectively can transform how you analyze data in Power BI. These functions empower users to manipulate and summarize data in meaningful ways.
If you found this useful, bookmark it or share it with your data team!