Project Botticelli

Status message

See the new version of this article on Tecflix.com! Why? Read our announcement.

Introduction to DAX in Excel Purchase this course

21 August 2012 · 6 comments · 9056 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 access below to the premium version of this content.

Purchase A Full Access Subscription

Subscription Best Value

$480/year

Access all content for 1 year.
  Purchase on Tecflix

  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

Comments

arthurjenkins1969 · 24 August 2012

Hello again Alberto. I wanted to inform you that you have your numerator and denominator reversed in your GrossMarginPctMeasure.

Regards

Arthur

Marco Russo · 24 August 2012

Arthur,
you're right! Despite all of our reviews, we missed the wrong formula...
So for everyone: the formula for GrossMarginPctMeasure in the demo (at minute 09:10) made by Alberto should by
= SUM( Sales[GrossMargin] ) / SUM( Sales[SalesAmount] )

Thanks for your help!

Marco

mqikram · 31 October 2012

how do i download these videos? thanks.

Rafal Lukawiecki · 31 October 2012

Hi, I am sorry, but we do not support any ability to download the videos. Our videos are only available for online streaming. I would be very interested in your feedback, however, if you feel that downloading videos is important to you. Please use the Contact page, many thanks for that.

agerasimenko · 17 November 2016

Hello Marco and Alberto.
Thank you very much for such useful videos. I started my professional career in PowerPivot and DAX with your books and videos and still following you.

More than a year I am puzzling over a question about passing parameters from Excel sheet to queries which populate tables in PowerPivot data model.
For example, there is an Excel PowerPivot report that displays different KPIs for the last 12 months. Every month I have to adjust query(ies) for fact table(s) to shift this diapason. Or another example, when we pull information for “Product Report” with high level of granularity, I have to re-run queries with each product value for “product parameter”, otherwise the fact table will contain too many rows with no chance for processing calculations.
In the perfect scenario, specialists of different departments would be able to use prepared for them Excel PowerPivot Data Model and “play” with parameters with no waiting in the line to the person who can read SQL-T language and know PowerPivot data modeling.

What do you think, is it possible in Excel? Somehow with linked tables or even VBA? Or the only answer is Data Warehouse + SSRS + SharePoin?

Please help me to find the answer and my ability to sleep again.

Thank you in advance,

Andrei.

Marco Russo · 17 November 2016

Andrei, in similar scenarios I imported in Power BI a table from Excel where I used a single row of data corresponding to the parameters I wanted to pass to the transformations in Power Query. Is this approach suitable for your needs?
Best regards,

Marco