Welcome to the DAX Cheat Sheet for Beginners! If you're just getting started with DAX (Data Analysis Expressions), this blog can be a handy reference to guide you along the way. DAX is the formula language powering tools like Power BI, enabling you to create calculated columns, measures, and dynamic insights with ease.
This cheat sheet covers the essential fundamentals of DAX syntax, operators, and key functions—enough to help you get started and quickly apply them to your projects.
Syntax Operators
DAX operators are the foundation of writing formulas. From performing calculations to comparing values, these symbols help you manipulate and analyse your data effectively.
- + Addition
- - Subtraction
- * Multiplication
- / Division
- ^ Exponent
- == Equal
- > Greater than
- < Less than
- <> Not equal to
- >= Greater than or equal to
- <= Less than or equal to
- & Concatenate text
- && And
- ' Table Reference
- || Or
- IN Logical Or (given a list)
Mathematical Functions
If you’re working with numbers, these functions will do the heavy lifting. Whether it’s finding sums, averages, or the largest value in a dataset, mathematical functions are essential for any analysis.
- SUM: Returns the sum of the values in a column.
- AVERAGE: Returns the average of the values in a column.
- COUNT: Returns the number of rows in a table or the number of distinct values in a column.
- DISTINCTCOUNT: Returns the number of distinct values in a column.
- MAX: Returns the maximum value in a column.
- MIN: Returns the minimum value in a column.
Text Functions
Text data can be tricky, but DAX has functions to make it manageable. These tools let you clean, extract, and manipulate text strings with ease, so your data looks just the way you need it.
- LEFT: Returns a specified number of characters from the beginning (left) of a text string.
- RIGHT: Returns a specified number of characters from the end (right) of a text string.
- MID: Returns a specified number of characters from a text string, starting at a specified position.
- LEN: Returns the number of characters in a 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 text string.
- SUBSTITUTE: Replaces occurrences of a specified text within a string with new text.
- FIND: Returns the starting position of a text string within another text string.
Date and Time Functions
Dates are a big part of any dataset, and these functions make working with them straightforward. From getting today’s date to calculating time differences, DAX has all the tools you need.
- TODAY: Returns the current date.
- NOW: Returns the current date and time.
- DATE: Returns a specific date given year, month, and day values.
- YEAR: Returns the year from a date.
- MONTH: Returns the month from a date.
- DAY: Returns the day from a date.
- HOUR: Returns the hour from a time or date/time value.
- MINUTE: Returns the minute from a time or date/time value.
- SECOND: Returns the second from a time or date/time value.
- DATEDIFF: Returns the difference between two dates.
Logical Syntax
Logical functions let you build smarter formulas by testing conditions and returning results based on whether they’re true or false. These functions add flexibility and decision-making to your calculations.
- IF: Returns one of two values based on a specified condition. If the condition is true , it returns the value_if_true. If the condition is false, it returns the value_if_false.
- AND: Returns true if all the conditions provided as arguments are true. Otherwise, it returns false.
- OR: Returns true if at least one of the conditions provided as arguments is true . Otherwise, it returns false.