DAX 101: What are Data Analysis Expressions?

By Philip Seamark

Data Analysis Expressions (DAX) is both a query and functional language. It made its first appearance back in 2009 as part of an add-in to Microsoft Excel 2010. The primary objective of DAX is to help organize, analyze, understand, and enhance data for analytics and reporting.

DAX is not a full-blown programing language and does not provide some of the flow-control or state-persistence mechanisms you might expect from other programming languages. It is designed to enhance data modeling, reporting, and analytics. DAX is constantly evolving with new functions added on a regular basis.

DAX is described as a functional language, which means calculations primarily use functions to generate results. A wide variety of functions are provided to help with arithmetic, string manipulation, date and time handling, and more. Functions can be nested but you cannot create your own. Functions are classified into the following categories:

• DateTime
• Filter
• Info
• Logical
• Mathtrig
• ParentChild
• Statistical
• Text

There are over 200 functions in DAX. Every calculation you write will use one or more of these. Each function produces an output with some returning a single value and others returning a table. Functions use parameters as input. Functions can be nested so the output of one function can be used as input to another function.

Unlike T-SQL, there is no concept of INSERT, UPDATE, or DELETE for manipulating data in a data model. Once a physical table exists in a Power BI, SSAS Tabular, or Excel PowerPivot data model, DAX cannot add, change, or remove data from that table. Data can only be filtered or queried using DAX functions.

About the Author

Philip Seamark is an experienced Data Warehouse and BI Consultant with a deep understanding of the Microsoft stack and extensive knowledge of data warehouse (DW) methodologies and enterprise data modeling. He is recognized for his analytical, conceptual, and problem-solving abilities with more than 25 years of commercial experience delivering business applications across a broad range of technologies. His expertise runs the gamut from project management, dimensional modeling, performance tuning, ETL design, development and optimization, report and dashboard design to installation and administration. 

In 2017 he received a Microsoft Data Platform MVP award due to his contributions to the PowerBI community site, as well as speaking at many data, analytic, and reporting events around the world. Philip is also the founder and organizer of the Wellington Power BI User Group.

This blog post was contributed by Philip Seamark, an experienced Data Warehouse and BI Consultant with a deep understanding of the Microsoft stack and extensive knowledge of data warehouse (DW) methodologies and enterprise data modeling. Philip is the author of “Beginning DAX with PowerBI.”