Project Botticelli

Status message

Video playback issues? See tips for supported browsers. Clearing cookies and cache often helps.

DAX in Action! Purchase this course

30 July 2012 · 14 comments · 30626 views

Loading the video player ...

If this message stays on, please ensure JavaScript is enabled and that you are using a supported device and browser. Ad blockers may also cause this issue.

For more information please visit our device support page.

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, 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. 

Purchase This Course or Full Access Subscription

Single Course


Access this course for its lifetime*.
Subscription Best Value


Access all content on this site for 1 year.
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

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

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.


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.


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?


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


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



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 ?


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:
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

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!

paatshala1 · 18 October 2015

Hi, I'm starting with DAX and I really love it.
Watching this video about counting customers (and its comments) I managed to create my own data model, however I have a problem and a see the same problem in the video: the monthly totals and general total are wrong, because obviouly we can have the same number to buyingcustimers and newcustomers.

I acctually solved the "<=" issue but the error still remains...can you help me please??

kuntphong · 6 February 2016

How about calculating totalsales from only new customers.

gveg · 30 October 2017

Great video! Thanks.
I have a question: is there a special reason to have OrderDateKey in Calendar? Can't we just link Sales[OrderDateKey] to Calendar[FullDate]?

Jump to a chapter

Online Courses