DAX vs M Language.

You've heard of them but you're not sure of the differences.

Is M the language Judi Dench speaks in James Bond movies? Let me tell you, no.

In this post, I'll walk you through what they are, mistakes you can make with them, and how to choose the right language for the job. 

Table of Contents

Introduction 

What are DAX and M languages? 

What are the key differences between them? 

Which language should you use? 

Mistakes people make with DAX and M 

Tips for choosing the right language for certain problems 

Conclusion 

Bonus 

10 common DAX expressions

10 common M expressions

Resources for learning DAX and M

What is DAX?

DAX, or Data Analysis Expressions, is a formula language used in Power BI and Power Pivot in Excel. It is used to perform calculations on data and create measures and calculated columns. DAX is a powerful language that can be used to perform a wide range of tasks, including:

• Performing arithmetic and logical operations on data
• Creating conditional expressions
• Aggregating data
• Filtering data
• Creating relationships between tables

DAX was developed by the SQL Server Analysis Services team at Microsoft as part of Project Gemini and released in 2009 with the first version of the PowerPivot for Excel 2010 Add-in.

DAX is a relatively new language, but it has quickly become one of the most popular languages for data analysis and reporting. This is due to its power, flexibility, and ease of use.

DAX is also well-integrated with other Microsoft BI tools, such as Power BI and Excel, which makes it easy to use DAX to create and share reports and dashboards.

What is M? 

M language, or Power Query M, is a formula language used in Power Query to transform and shape data.

M language was developed by Microsoft as part of Power Query, which was first released in 2011. Power Query is a powerful data manipulation tool that can be used to transform and shape data from a variety of sources, including Excel tables, CSV files, databases, and web pages.

Here is a brief history of M language:

• 2011: Power Query is released, and M language is introduced as the formula language for transforming and shaping data.
• 2013: Power Query is integrated into Excel 2013.
• 2015: Power Query is renamed to Get & Transform Data, and M language is renamed to M.
• 2016: Power Query is renamed to Power Query again.
• 2017: M language is integrated into Dataverse.
• 2023: M language is still actively developed by Microsoft, and it is used in a variety of Microsoft BI tools and services.

M language is also well-integrated with other Microsoft BI tools, such as Power BI and Excel, which makes it easy to use M language to transform and shape data for use in these tools.

M language is a powerful and versatile language that can be used to transform and shape data in a variety of ways. It is a valuable skill to have for anyone who works with data.

Key Differences between DAX and M 

DAX and M have different purposes.

DAX is used for data analysis and calculations.

M is used for data transformation and cleaning.

Here is a table that summarizes the key differences between DAX and M:

Which language should you use?

The language you should use depends on the task you are trying to accomplish. If you need to perform calculations on data or create measures and calculated columns, you should use DAX. If you need to clean and transform data, merge and append data from multiple sources, or reshape data into a different format, you should use M.

In some cases, you may need to use both DAX and M. For example, you may need to use M to clean and transform your data before you can use DAX to perform calculations on it.

Examples

Here is an example of a DAX calculation:

Code snippet 

Sales by Product Category = SUMX(
    Table1,
    Table1[Product Category],
    Table1[Sales]
)

This calculation calculates the total sales for each product category.

Here is an example of an M transformation:

Code snippet

This transformation removes duplicate rows from the table.

Mistakes people make with DAX and M

• Using the wrong language for the task at hand. DAX and M are designed for different purposes, so it's important to use the right language for the task at hand. For example, if you need to clean and transform data, you should use M. If you need to perform calculations and analysis on data, you should use DAX.

• Not understanding the syntax of the language. Both DAX and M have their own syntax, which is the set of rules that govern how expressions are written. It's important to understand the syntax of the language you're using in order to write correct and efficient expressions.
• Not using the right functions. Both DAX and M have a wide range of functions that can be used to perform various tasks. It's important to choose the right function for the task at hand in order to get the desired results.

Tips for choosing the right language for certain problems

• When to use DAX
• When you need to perform calculations or analysis on data.
• When you need to create measures or calculated columns.
• When you need to create relationships between tables.
• When to use M
• When you need to clean and transform data.
• When you need to merge or append data from multiple sources.
• When you need to reshape data into a different format.

If you're not sure which language to use, it's always best to start with M. M is a more versatile language, and it can be used to perform many of the same tasks as DAX. Once you have your data in the desired format, you can use DAX to perform calculations and analysis.

Conclusion

DAX and M are two powerful languages that can be used to get the most out of Power BI. By understanding the differences between the two languages and how to use them effectively, you can create more powerful and informative reports and dashboards.

Bonus

10 Common DAX Expressions

Here are 10 common DAX expressions and syntax:

• SUM() - Returns the sum of all the values in a column.
• AVERAGE() - Returns the average of all the values in a column.
• MEDIAN() - Returns the median of all the values in a column.
• COUNT() - Returns the number of rows in a table.
• COUNTROWS() - Returns the number of rows in a table, excluding blank rows.
• MIN() - Returns the smallest value in a column.
• MAX() - Returns the largest value in a column.
• DISTINCTCOUNT() - Returns the number of unique values in a column.
• CALCULATE() - Evaluates an expression in a context that is modified by the specified filters.
• IF() - Returns one value if a condition is met, and another value if it is not met.
• SWITCH() - Returns one of multiple values based on the result of a condition.
• VAR() - Declares a variable that can be used in an expression.

Here are some examples of how to use these DAX expressions:

Code snippet

These are just a few examples of DAX expressions. There are many other expressions and functions available, so you can create complex calculations and analyses to meet your specific needs.

Tips for writing DAX expressions

Here are some tips for writing DAX expressions:

• Use the CALCULATE() function to modify the context in which your expression is evaluated. This allows you to create more complex and flexible calculations.
• Use the VAR() function to declare variables that can be used in your expression. This can make your code more readable and maintainable.
• Use the IF() and SWITCH() functions to create conditional expressions. This allows you to return different values based on different conditions.
• Use the DISTINCTCOUNT() function to count the number of unique values in a column. This can be useful for identifying the most popular or least popular products, customers, etc.

10 Common M Expressions

Here are 10 common M expressions and syntax:

• (#"Removed Duplicates") - Removes duplicate rows from a table.
• (#"Split Column by Delimiter") - Splits a column into multiple columns based on a delimiter.
• (#"Merged Tables") - Merges two tables together.
• (#"Filtered Rows") - Filters a table based on a condition.
• (#"Sorted Rows") - Sorts a table based on the values in a column.
• (#"Changed Type") - Changes the data type of a column.
• (#"Replaced Values") - Replaces values in a column with new values.
• (#"Added Column") - Adds a new column to a table.
• (#"Removed Column") - Removes a column from a table.
• (#"Extracted Values") - Extracts values from a column or list.
• (#"Expanded Table") - Expands a table by creating new rows from the values in a column or list.
• (#"Grouped Rows") - Groups rows in a table based on the values in a column or list.

Here are some examples of how to use these M expressions:

Code snippet

// Remove duplicate rows from a table.

#"Removed Duplicates" = Table1 |>

    RemoveDuplicates()

// Split a column into multiple columns based on a delimiter.

#"Split Column by Delimiter" = Table1 |>

    SplitColumn("Column1", ",")

// Merge two tables together.

#"Merged Tables" = Table1 |>

    Merge(Table2, On="Column1")

// Filter a table based on a condition.

#"Filtered Rows" = Table1 |>

    Filter(Rows = [Column1] > 100)

// Sort a table based on the values in a column.

#"Sorted Rows" = Table1 |>

    Sort(Rows = [Column1], Ascending = True)

// Change the data type of a column.

#"Changed Type" = Table1 |>

    ChangeType(Column1, type = Text)

// Replace values in a column with new values.

#"Replaced Values" = Table1 |>

    ReplaceValues(Column1, {"OldValue": "NewValue"})

// Add a new column to a table.

#"Added Column" = Table1 |>

    AddColumns(Column2 = "Value")

// Remove a column from a table.

#"Removed Column" = Table1 |>

    RemoveColumns(Column1)

// Extract values from a column or list.

#"Extracted Values" = Table1 |>

    ExtractValues(Column1)

// Expand a table by creating new rows from the values in a column or list.

#"Expanded Table" = Table1 |>

    ExpandTable(Column1)

// Group rows in a table based on the values in a column or list.

#"Grouped Rows" = Table1 |>

    GroupRows(Column1, {

        "Group1": {

            "Column2": SUM(Column2)

        }

    })

These are just a few examples of M expressions. There are many other expressions and functions available, so you can perform a wide range of data transformations and manipulations.

Tips for writing M expressions

• Use descriptive names for your columns and variables. This will make your code more readable and maintainable.
• Use comments to explain what your code is doing. This is especially important for complex expressions.
• Use the table view to preview the results of your expressions. This can help you to catch errors and ensure that your expressions are returning the desired results.
• Break down complex expressions into smaller, more manageable expressions. This will make your code more readable and maintainable.
• Test your expressions thoroughly before using them in production. This will help to ensure that your expressions are working as expected.

Resources for learning DAX and M

Here is a list of 10 resources to learn DAX and M:

• Microsoft Learn: Microsoft Learn offers a free course on DAX and M called "Data Analysis Expressions (DAX) and Power Query M". This course covers the basics of DAX and M, including how to write expressions to perform calculations, filter data, and create relationships between tables.
• SQLBI: SQLBI is a website that offers a variety of resources for learning DAX and M, including blog posts, tutorials, and courses. SQLBI also offers a DAX and M reference guide, which is a valuable resource for looking up specific functions and syntax.
• Curbal: Curbal is a YouTube channel that offers a variety of videos on DAX and M. Curbal's videos are well-produced and easy to follow.
• DAX Patterns: DAX Patterns is a website that offers a collection of DAX patterns. DAX patterns are reusable solutions to common data analysis problems.
• Pro DAX: Pro DAX is a book by Alberto Ferrari and Marco Russo. This book is a comprehensive guide to DAX, covering everything from the basics to advanced topics.
• M is for (Data) Monkey: M is for (Data) Monkey is a book by Ken Puls. This book is a comprehensive guide to M, covering everything from the basics to advanced topics.
• Power Query in a Nutshell: Power Query in a Nutshell is a book by Greg Deckler. This book is a concise guide to M, covering the basics of data transformation and shaping.
• DAX Cookbook: DAX Cookbook is a book by Alberto Ferrari and Marco Russo. This book is a collection of DAX recipes for solving common data analysis problems.
• M Cookbook: M Cookbook is a book by Ken Puls. This book is a collection of M recipes for performing common data transformation and shaping tasks.
• DAX for Beginners: DAX for Beginners is a book by Greg Deckler. This book is a gentle introduction to DAX, covering the basics of writing DAX expressions and creating measures and calculated columns.
• Power Query for Beginners: Power Query for Beginners is a book by Greg Deckler. This book is a gentle introduction to M, covering the basics of data transformation and shaping.

I hope this list of resources is helpful. Good luck learning DAX and M!