DAX Basics

Like any other language, DAX (Data Analysis Expressions) may seem intimidating at first, but with consistent practice and patience, it becomes much more approachable and manageable. The aim of this blog post is to go over some basic syntax and functions, providing a solid foundation for learning DAX.

What is DAX?

DAX is a formula language used in PowerBi - not to be confused with M, used in Power Query. DAX is also a case-insensitive formula language, meaning functions and column names, such as SUM() and sum(), are treated the same. It operates in two key contexts: row context, which applies calculations row by row, and filter context, which filters data based on visual or calculation setups. DAX also distinguishes between measures and calculated columns; measures are dynamic calculations that adjust based on filters in visuals (e.g., SUM(Sales[Amount])), while calculated columns compute values for each row and store them in the data model (e.g., Sales[Total] = Sales[Price] * Sales[Quantity]).

DAX Basic Functions

• LEFT(): Extracts a specified number of characters from the beginning of a text string.
• RIGHT(): Extracts a specified number of characters from the end of a text string.
• MID(): Extracts characters from a text string, starting at a specified position and for a specified length.
• LEN(): Returns the total number of characters in a text string.
• LOWER(): Converts all characters in a text string to lowercase.
• UPPER(): Converts all characters in a text string to uppercase.
• TRIM(): Removes leading and trailing spaces from a text string.
• CONCATENATE(): Combines multiple text strings into a single string.
• SUBSTITUTE(): Replaces specified text in a string with new text, optionally for a specific instance.
• FIND(): Returns the starting position of a text string within another string.

Date Functions

• TODAY(): Returns the current date.
• NOW(): Returns the current date and time.
• DATE(): Creates a specific date using year, month, and day values.
• YEAR(): Extracts the year from a date.
• MONTH(): Extracts the month from a date.
• DAY(): Extracts the day from a date.
• HOUR(): Extracts the hour from a time or date/time value.
• MINUTE(): Extracts the minute from a time or date/time value.
• SECOND(): Extracts the second from a time or date/time value.
• DATEDIFF(): Calculates the difference between two dates.

Logical Operators

• IF: Returns a value based on a condition. If the condition is TRUE, it returns value_if_true; otherwise, it returns value_if_false.
• AND: Returns TRUE if all specified conditions are TRUE; otherwise, it returns FALSE.
• OR: Returns TRUE if at least one of the specified conditions is TRUE; otherwise, it returns FALSE.