DAX in Action!

30 July 2012 · 11 comments · 18902 views

Loading the player...

If this message stays on, check that you have the latest Flash Player installed (http://get.adobe.com/flashplayer) and that your browser supports JavaScript.

Data Analysis Expressions for Counting Customers

See the power of Data Analysis Expression (DAX) language in PowerPivot, and in SQL Server Data Tools (SSDT), in this free, 20-minute video by Alberto Ferrari, a renowned book author on the subject, consultant at sqlbi.com, and the winner of the Best Overall Session at Microsoft TechEd Europe 2012.

DAX has been introduced in PowerPivot in 2010 and, in 2012, it has been integrated in SQL Server 2012 Analysis Services (SSAS) Tabular Mode, which  examplify the new BI Semantic Model (BISM). One of the main reasons for the creation of this new language was the introduction of a new, simpler, columnar modelling paradigm in both PowerPivot for Excel, and in SSAS Tabular (0:41). Together, they leverage the incredibly fast xVelocity In-memory Analytics Engine: a powerful columnar database that is able to store billions of rows of data in a compact way, leading to ultra-fast, yet flexible, querying and calculating options.

In the first demo shown in this video, we create a simple data model (1:58), based on the AdventureWorks sample database (download here). Instead of querying it in just a few simplistic ways, we show you how to write a much more challenging formula: counting how many new and returning customers make a purchase every month (7:18). First, you learn how to count customers using DISTINCTCOUNT (8:17). Counting new customers requires the use of DAX expressions such as: CALCULATE, FILTER, and COUNTROWS (9:24).

PowerPivot for Excel is particularly useful for quick prototyping of your tabular expressions, before deploying them to SSAS for security, partitioning, and other reasons, which are explained in depth in this video, which is part of our Introduction to SQL BI online course.

After you learn how to count returning customers (13:46), Alberto shows you how to deploy our solution just created in PowerPivot to a SQL Server Analysis Services Tabular Model database (15:26).

As simple as it seems, this calculation required complex multidimensional modeling in previous versions of SSAS, leading to the need to modify the original data structures through some ETL code. Using DAX, it wasn’t even necessary to change the data model. Working on the simple, relational star-schema structure of the AdventureWorks data warehouse, we just wrote a slightly more complex DAX code that solved the problem without requiring a single step of ETL. Writing such DAX is not an easy task at first, it requires the attitude to learn, digest, and master the philosophy of DAX, which is quite different from the query language of previous versions of SSAS: MDX (Multidimensional Expressions).

Thinking in DAX represents a new attitude. If you would like to gain this skill, this video, first one in our new series focusing on DAX, shows you what could be accomplished without having to change the model of your data. Learning how to write DAX is a more advanced task, which we will cover in subsequent videos.

We also recommend Alberto and Marco’s book, co-authored with Chris Webb: Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Modes. 

Comments

DoctorDave · 31 July 2012

Great information, Alberto. Thanks for sharing and being a great advocate of DAX.

arthurjenkins1969 · 13 August 2012

Hello Alberto. I just signed up for a 1 year membership. I learned a lot from DAX in Action. Please give us more.

Regards

Arthur Jenkins
Los Angeles, CA

arthurjenkins1969 · 13 August 2012

I have a couple of questions about DAX in Action. When building a measure, how do you move the cursor to the next line in order to parse the measure to make it more readable? Next, what is the signifiance of “Min”, which you use compare earlier dates to the date being evaluated? And does “Max” work equally well?

Thanks.

Alberto Ferrari · 13 August 2012

Arthur, you’re welcome!
Alt-ENTER or Shift-ENTER will do the magic. Moreover, it might be useful to user CTRL-mouse wheel to zoom in inside the formula textbox, as years are passing I find these tricks more and more useful, maybe it’s just that my eyes are getting worse and worse?
Regarding MIN, it is used to search for sales before the first day of the selected range. BTW, I discovered too late that the correct formula should LESS THAN MIN and not LESS THAN OR EQUAL MIN… anyway, the overall logic still work. :)

arthurjenkins1969 · 14 August 2012

Alberto,

Thank you for your prompt reply. Looking forward to more DAX lessons.

Regards

Arthur

GF3L · 18 August 2012

Hello Alberto,

Thanks for this awesome video. I have a question regarding the formula to calculate “NewCustomers” :
When I look at the sum in the pivottable for “NewCustomer”, I don’t understand the result find “per year” , it’s not the same as the Sum of NewCustomer per month. Can you explain me why ?

Thanks

Alberto Ferrari · 5 September 2012

You are right, the result shown in the video is wrong. The reason is that the innermost CALCULATE should filter in this way:
FILTER (
ALL (Calendar),
Calendar[FullDate] < MIN (Calendar[FullDate])
)
During the recording, I have used LESS OR EQUAL and, thus, the results are not the correct ones. Using LESS THAN, you will get correct behavior and the new customers correctly aggregate with SUM over the months.
Thanks for noting this.

Marco Russo · 21 August 2012

I am not sure about your question - the number of Distinct Count might be different for months and years because the same customer wouldn’t be counted twice in several months. However, this shouldn’t be the case of NewCustomer, because of the fomula used. Alberto is on holiday a couple of weeks and I cannot check it now - so we’ll check the formula and answer to your question at the beginning of September.

GF3L · 21 August 2012

Thanks for your answer Marco. I had this question when looking at the Excel pivot table at 13:30 in the video.

jojx · 13 September 2012

Hi,
Thanks for a great video. I try to convert the “New Customer” measure to a calculated column. The reason is that I want to filter my results to see wish customer that are new in a month. But I’m failing trying to build the calculated column. I am stuck in the Row context. Any tip? Thanks!

cosmini · 10 February 2013

awesome videos & feedback regarding corrections!