Introduction to DAX

21 August 2012 · 4 comments · 5583 views

Data Analysis Expressions Fundamentals

Using Aggregation Iterators (SUMX) to Calculate Gross Profit Margins

Introduce yourself to Data Analysis Expression (DAX) by learning the syntax and the fundamental functions of this language in this 40-minute video, which includes 9 demos, by Marco Russo and Alberto Ferrari, world-known SSAS experts from sqlbi.com, and recognized speakers at international conferences like TechEd, PASS Summit and SQLBits.

DAX is the language used to define calculation expressions in PowerPivot. It works in Excel 2010, and it is in the core of Excel 2013. DAX is also the programming language for Microsoft SQL Server Analysis Services Tabular Models. It has a simple syntax that will be instantly familiar to Excel users, because it replicates Excel syntax wherever possible. Nevertheless, DAX also introduces many new functions, in order to express tabular and columnar concepts, which are not part of Excel workbooks—Excel is focused on cells and ranges, while DAX focuses on entire columns and tables. By watching this video, you will become familiar with the basic syntax of DAX, and you will be ready to write your first calculations.

DAX has its own data types, and an automatic type conversion system which, in most cases, does not require using specific conversion functions. In any DAX expression you can reference a column by using the syntax ‘TableName’[ColumnName]. It is possible to omit the table name in certain conditions, though for clarity’s sake, and to avoid errors, it is better to always include table names. In the first demo you will see how to create a calculated column by using the proper syntax. Always remember to include the table name referenced in an expression and to rename the calculated column, so that they will be meaningful to your users.

You will see how to create a typical measure, frequently used for common calculations, such as ratios. The syntax for measures requires the inclusion of aggregators in their expressions. Measures usually contain DAX aggregation functions such as SUM or DAX aggregation iterators such as SUMX. To make this clearer, Alberto shows a demo explaining the differences between these group of functions, because a measure cannot be used as a parameter in an aggregation functions, requiring an iterator instead.

There is much more that you will learn in this video. You will also hear, and see in demos, about: error handling using ISERROR and IFERROR functions, counting values using DISTINCTCOUNT, logical functions, mathematical operations including rounding, and date and time calculation functions—all of which are essential to every-day business analytics.

Log in or purchase a Full Access Membership to access the premium version of this content.

Buy Full Access Membership

Access all content, including full-length training videos, demo files, and articles, right now! Payment is instant, and you will receive a tax invoice, straight away.

Our content is updated every month—get the best value by buying a 1 year membership.

Your satisfaction is paramount: we offer a no-quibble refund guarantee.