DAX Formulas for PowerPivot – by Rob Collie

DAX Formulas for PowerPivot:  The Excel Pro's Guide to Mastering DAX

The First PowerPivot Formulas Book Written Specifically for Excel Users

PowerPivot’s formula language (DAX) was created by Microsoft to turbocharge Excel’s existing capabilities.  It puts truly revolutionary power in the hands of YOU, the “spreadsheet heroes” of the world.

DAX Formulas for PowerPivot is the Excel user’s ideal roadmap for harnessing PowerPivot’s career-changing capabilities, utilizing the style developed and refined during Rob Collie’s 3+ years of training fellow Excel users on PowerPivot:

1. Example-Driven:  each concept, function, and technique is accompanied by a tangible example.  Everything is both explained AND demonstrated.

2. Visual – the book features more than 300 illustrations, diagrams, and screenshots.  You SEE everything.

3. Jargon-Free – no prior experience required with programming or business intelligence technologies.  If you’ve created a PivotTable before, you are ready.

Buy the Book or E-Book

Buy from MrExcelBuy from Amazon

Australian Customers!

Reader Reactions

“The style of the book is very visual and easy going, there are no dry explanations of database concepts or theory, it’s all about real examples of everyday data analysis problems and how to solve them using DAX. Also included are the little tricks and tips… which you are unlikely to see in any of the more theoretical books.”-Jacob Barnett, Commercial Analyst (Australia)
“Despite being a fairly accomplished Excel pro, I have been unable to wrap my arms around DAX. But that is over. Four days of working through this book with my own data and I am now making some damn cool stuff that eluded me before.”-Ken Puls, Founder of ExcelGuru and Microsoft-Recognized Excel MVP (Canada)
“Human… this is how technical books should be written.”-Tom LaRock “…writing style is very addictive… it’s a real page-turner.”-Siraj Samsudeen
“Rob has a gift for explaining things in a way that makes it easy to understand.”-Shawn Frizzell “…extremely well written, clear, concise and flows logically down your DAX adventure.”-Dave Boylan
“This book gets to grips with the real problems faced when implementing a powerpivot solution. The section on performance was especially worthwhile and the examples meaningful.”-Dave Pitts “Packed with loads of super useful tips and tools. After getting through the first chapters I had already gotten my money’s worth. Best gift to data analysis in the 21st century.”-Dan Wesson

Sample Contents


This diagram from an early chapter of the book illustrates Rob’s
“Learn incrementally and at your own pace” philosophy

clip_image002_thumb5 clip_image0024_thumb5 clip_image0026_thumb5

A Few Sample Pages – Click Each for Larger Version

Data Sources and Workbooks From the Book

PowerPivot Book Sample Files

All Files Used in the Book Are Available for Free Download
Click Image to Visit the Download Page

50 Responses to DAX Formulas for PowerPivot – by Rob Collie

  1. Hi Rob,

    The calculator icon on measures in the Pivot File list appears to be missing for Excel 2013. Is there a way to turn this feature on? Looking at the book (p. 41) It appears to be very helpful.


    • powerpivotpro says:

      I am afraid there’s no way to turn that back on in 2013. In my opinion, 2013 is temporarily a step backward from 2010 PowerPivot in multiple ways. I’ve been talking to MS about that problem (and others) and am confident we’ll eventually get fixes. Just not sure when yet.

  2. Shlomi says:

    Hi Rob,
    Just want to say I really(!) love your book.
    It opens me a whole new world in Excel , which I truly LOVE , and use for the last 15 years.
    (and also BI world which was not familiar to me whatsoever till I got your book from Amazon).

    Thanks my friend!
    (and by the way, I was happy to see that the “father” of PowerPivot (Amir Netz) is Israeli guy, like myself 😉 )

    all the best and thanks for sharing your knowledge.

  3. Keith Barney says:

    I’m not sure this is the right avenue for asking but…. Two Questions from the book: 1) Pg. 181, Figure 306 you mention you’ll explain the formula later but I can’t find it later. 2) Pg. 181 again, I’m struggling to understand the last part of that formula and how it does the YTD calc. Can’t get my mind around what it is doing with the comparisons on the PeriodID. Thanks!

    • powerpivotpro says:

      Hi Keith! I believe the explanation starts on page 203, with the EARLIER function.

      For the second question, you *might* need to rewind to the start of Ch 18 and re-read how the whole FILTER() thing works, but the PeriodID logic basically says:

      Select all periods that meet BOTH of these criteria:

      a) less than or equal to the highest-number period corresponding to the current pivot cell
      b) greater than or equal to the period number that is *the first period in the year* corresponding to the current pivot cell

      Step through this VERY mechanically in your head to see why it works. You have to think like the machine, not like a human :)

  4. Charles Brown says:

    Great content … thanks for bring the magic of PowerPivot to the analyst masses.

    One note, the Kindle version of the book does not have a linked Table of Contents or a Linked Index — makes it a little hard to find things.

    Thanks again for your work.


  5. Isken says:

    Hi Rob

    Is the book suited for excel 2010 or 2013?

    • powerpivotpro says:

      Both. The book is written using Excel 2010. In terms of formulas and techniques, 2010 and 2013 are IDENTICAL. 2013 has a few cosmetic differences, plus it has Power View, but everything covered in the book translates 100% into Excel 2013.

  6. Miguel Chavez Garcia says:

    Hello , Is there a way to get the Metadata in order to create a Technical document . List of Measures, List of Dim, Diagrams , etc.

  7. Hi Rob,

    I want to know about your new book PowerPivot Alchemy: Patterns and Techniques for Excel.What is the difference between your early book and this book. When It will going to available in india.


    Rajeev Gautam

    • powerpivotpro says:

      Alchemy is more about all of the neat tricks you can perform with Power Pivot once you have the building blocks covered in my first book.

      It’s a collection of the most magical/impactful tricks covered on the blog over the past 4 years.

  8. Michelle says:

    Hi Rob.

    I’m a “pro” in PowerPivot v1, but I’ve struggled with v2. Rather than make my new company go back to v1 (which I could do since I’m the only PowerPivot developer), I really want to step up my game with v2. Is your new book geared toward new v2 functionality & issues (hello circular references) or is it focused on v1 and more general PowerPivot functionality? Thank you so much!

    • powerpivotpro says:

      Hi Michelle! The book was written entirely using v2. Of course, there’s very little in the book that isn’t also relevant to v1 or even 2013. The fundamentals are the same, but v2 is *quite* a bit better than v1, and I leverage the “new in v2″ features and functions quite heavily in the book.

  9. Sharon Hadley says:

    I am a numbers person. Which means I truly dislike reading… Especially technical (yawn) reading. I could not put your book down. Of course, I love excel and power pivot already, but I have struggled knowing how to get it to yield answers to non-excel lovers in my organization. After reading the book, with all of your fabulous examples, screen shots, and most importantly humor drenched real life recommendations. I have so many ideas on how to implement power pivot analysis in my organization. I love my job! Thank you!

  10. Mark says:

    I have just returned to the office after a weeks holiday in Far North Queensland (the top of Australia) where I finished the book in two days sitting by the swimming pool. For a technical book it was a very easy and enjoyable read – well done Rob. I now can’t wait to get some more PowerPivot models up and running.
    One comment – the screenshots above are in colour but my book was in black and white, a bit of colour would have been nice.

  11. eric menard says:

    I went out and bought the book, at full cover price, at Barnes and Nobel.
    It may have been $10 less at Mr.Excel , but that is the cost of intant gratification.
    I will go without food for a few days, but can’t wait to go to work on Monday.
    I have been using Access 2010 to query, group and sum data, just to paste into Excel.
    This is from the new POS on a Mysql DB, last years numbers are in excel, from an old fashioned cash register. To find last months department sales and Qnty, compaired to previous years takes my boss 4 hours of cut n paste. And i’m unsure of the expression to find previous months #s. Now it will take a push of The Refresh button.
    I’ve been day dreaming of new ways to “slice and dice” the raw data. I never knew of vlookup nor that relationships were even possible in excel.
    -‘I look for ways to concatenate data throughput, to maximize data utilization.’,
    Kelso’s Dad from “That 70’s Show”

    • powerpivotpro says:

      Eric you found the book at a physical book store? I’d love a picture of that if you ever find yourself in that store again, I haven’t seen one myself yet :)

      Nice quote/reference too!

      • eric menard says:

        I bought the only copy :-). I am in Bend Oregon, we really don’t have any big business here.
        I actually had hoped to find “Powerpivot Alchemy”,as it was supposed to be released on Oct. 31st, 2013. ( newer is newer) In no way was i dissapointed, to find your ealier “Dax formulas for Power pivot” I learned alot of valueble tips. I might of settled for some VBA for Access mistake if I didn’t keep looking past the Office books, your book was peeking out after the Excell manuals.
        As excited I was to have MS Access finally. Access is SO October, 2 -thousand thirteen, Powerpivot is LIKE two thousand fourteen!!
        Thank you for shairing, and not hording.
        If I have a reason to publish data, your Pivotstream.com will be the way to do it.
        And, p.s. I actually have food,(i forgot to insert the smily)
        In fact I’d put the bookmark in right before the time function chapter, to save that info for desert. As I took my homemade pizza out of the oven, i posted the above post. I still can’t wait to go into work on Monday!!

  12. Jeff "DrSynthetic" Wilson says:

    I actually had hoped to find “Powerpivot Alchemy”,as it was supposed to be released on Oct. 31st, 2013. Hey Rob “Chudzinski” Collie where can we find this book as Jeff “Mike McCarthy” Wilson is looking for some Alchemy to get me through these next few weeks I need a Discount Daaable Check guy with the franchise and my fantasy football QB on the shelf –

    Thank you

    Sheybogan Sausage Addict
    from ” Grill Class”

  13. Fran Reed says:

    I just finished reading “Dax Formulas for Powerpivot” cover to cover and just loved it. Presentation was easy to understand, humorous and very informative. Can’t wait to use the power of Powerpivot on some client projects. And to try out the various measures described. The Ending Chapter on Excel Pro’s gave a lot of pause to how to market oneself. And the information on Slicer’s and general Excel Pivot database setup was invaluable for reporting design. Thanks…

  14. James M says:

    Hey Rob, I just got your book today and started reading it. I realized there was a kindred spirit while reading the first chapter when I read this passage:

    “A woman takes her seat for a cross-country business flight and is pleased to see that her seatmate appears to be a reasonably normal fellow. They strike up a friendly conversation, and when he asks her what she does for a living, she gives the usual reply: “I’m a marketing analyst.”
    That answer satisfies 99% of her single-serving friends, at which point the conversation typically turns to something else. However, this guy is the exception, and asks the dreaded follow-up question: “Oh, neat! What does that mean, actually?”
    She sighs, ever so slightly, because the honest answer to that question always bores people to death. Worse than that actually: it often makes the single-serving friend recoil a bit, and express a sentiment bordering on pity.
    But she’s a factual sort of person, so she gives a factual answer: “well, basically I work with Excel all day, making PivotTables.” She fully expects this to be a setback in the conversation, a point on which she and her seatmate share no common ground.
    Does this woman’s story sound familiar? Do you occasionally find yourself in the same position?”

    As a recipient of the blank stares and the follow up “Wait… you LIKE using Excel?” questions, this part made me laugh out loud!

    I’m really enjoying your book, thanks!

  15. mrxlsx says:

    Hi Rob,

    Good evening,

    I am reading your book DAX for PowerPviot..! I have a doubt which nobody answered.

    I have a bunch of sheets in my Excel file. When I try to bring the same to the Powerpivot environment, at some point, it will show us the list of sheets available under the pop up List of Tables and Views under which all the sheets/tables are listed out to be checked and selected.
    In that list, I have a sheet named “Salesbook”. I can see the same in the List of Tables and Views as “Salesbook$” also “Salesbook$xlnm#_FilterDatabase”. Where in the actual list of sheets, I have only one sheet with that name. Also I have no filters applied.

    What is the significance of this sheet/table? Is this actually exists or not, if yes why this?

    Thanks for your effort.


  16. I just bought your book and am trying to find the access and Excel sheets. I get an error when I use http://ppvt.pro/bookfiles

  17. P. Bangaru Rayudu says:


    I bought this book. The contents are self explanatory and useful for quick reference.

    Best regards

    P. Bangaru Rayudu

  18. Derek D says:

    Are there digital/PDF versions of the pages like the “How the DAX Engine Calculates Measues or the Commonly-Used Functions and Techniques? I have the book and it’s ok to reference them, but I would like to print and hang on my wall so I can just glance at them when I need to reference.

    By the way, love the book and it’s been a great learning tool so far. Can’t wait to finish it and move on to the Alchemy book

  19. Michael says:

    I´m using MS Excel 2010 with PowerPivot-Version 11.0.3000.0.

    The tables and sheets could be opened. But when i try to start the PowerPivot add-in i recieve an error message in kind of “This workbook contains a data-model which is created with a newer version of excel. You can open this workbook with an older excel-version but you are not able to load or edit …” (sorry for the bad translation – i´m german).

    Is it possible that there are problems because you used PowerPivot under Excel 2013?

    How can i start PowerPivot in Excel 2010 with you tables?

    • powerpivotpro says:

      Hi Michael. You should not be having that problem. I am running the same version of Power Pivot and just now was able to open the Power Pivot window with no errors – using Ch5.xlsx

      Are you seeing this problem with all of the workbooks or just specific ones?

      • Michael says:

        Seem to be like the problem is only in ch20.xlsx !

        I think you edited this one it with Excel 2013? This file i could open with Excel 2013 without problems. But there are relations missed in the SALES

  20. Mike H says:

    Seriously great book…I was looking for a solution for a low cost BI tool to accelerate our ERP use and I continue to be shocked at how powerful powerpivot is. Your book not only does a fantastic job teaching the concepts but also talks to many of the issues/needs that we encounter daily in multinational companies.

  21. Hutch says:

    Hi: I bought the book, but I am not able to work with the downloaded examples.

    I get the error “You are attempting to access PowerPivot data that was added using a newer version of PowerPivot. This action is not supported, and you cannot access the data. To continue working with the PowerPivot data, you must use the latest version of PowerPivot”

    I am using Office/Excel 2010 and powerpivot version 10.50.4000.0 and excel is 14.0.7128.5000 (32 bit)


  22. alfranco17 says:

    I love the book. I had been struggling with DAX Formulas. I just could not understand them. I’m on page 99 of the book and I finally understand (and like) CALCULATE and FILTER enough to do some field testing.

    Great job!

  23. Arjun M Shetty says:

    Hi Rob,

    Good evening,

    I am reading your book DAX for PowerPviot..! I have a doubt which nobody answered.

    I have a bunch of sheets in my Excel file. When I try to bring the same to the Powerpivot environment, at some point, it will show us the list of sheets available under the pop up List of Tables and Views under which all the sheets/tables are listed out to be checked and selected.
    In that list, I have a sheet named “Salesbook”. I can see the same in the List of Tables and Views as “Salesbook$” also “Salesbook$xlnm#_FilterDatabase”. Where in the actual list of sheets, I have only one sheet with that name. Also I have no filters applied.

    What is the significance of this sheet/table? Is this actually exists or not, if yes why this?

    Thanks for your effort.


  24. king says:

    Hello Rob, it’s about Chap 9-ALL()… I read the page and could find how you derive the [Net Sales]. pls advice. Thanks

    Your explanantion and examples are making more realistic and practical to use Power Pivot.


  25. Bubba says:

    I’m trying to go through the examples in the book and I’m having minor difficulty. On Page 44 when you are introducing the [Transactions] measure, the book shows = COUNTROWS(Sales) I kept getting errors. It took me a while to search and find the real problem is the assignment operator should be := and not =. It is this way throughout the book.

    Page 63 when you introduce Transactions of a Certain Type, you are trying to filter on a TransType from the sales table. In the book database that I downloaded, there is no such column as TransType. Therefore, I’m stuck there.

  26. Hamp Yearwood says:

    I just started the DAX Formulas for PowerPivot and am confused. The book chapters and sample workbooks don’t seem to sync up.

    Chapter 5 – Intro to Calculated Columns and book chapter 6 also but only if you start with the workbook from Chapter 5. I got totally lost when I got to the section Other Fundamental Benefits of Measures which seems to refer to workbook labeled ch6 but when I open the it, the example in the book does not match the book illustration.

  27. Fio says:

    HiI am not able to find the Access DB used in the book. When I click on Data Sources and Workbooks from the book above, I get a Page Not found. Can someone please send me the link where I can download the same DB? Thanks!

  28. Mahit says:

    Hi, the link that says “Data Sources and Workbooks From the Book” is not working. Please give us the link which leads to the files.

  29. Su says:

    Hi, I have the same problem as Mahit does – can’t download the sample files. The page says “Not Found”. Eager to learn but get stuck here. Please help! Thanks!

  30. Su says:

    This link works to get all the sample files: http://www.powerpivotpro.com/book-files/

  31. Marco says:

    Hi Rob,
    I read on Amazon the announcement of the upcoming 2nd edition of your book on 1st of October 2015: there are big differences between the two editions?
    Better to wait?

  32. Brian says:


    I have enjoyed your book very much. It has been a great learning tool. One item that came to my attention in chapter 18 with the GFITW.

    =CALCULATE([Sales per Day in Period],
    – COUNTROWS(Periods)
    – COUNTROWS(Periods)

    You will notice I took out the first ALL to see what would happen, and the results was exactly the same results and was wondering if I am missing something ???

    Thanks Brian

Leave a Comment or Question