skip to Main Content

Up until this point, I’ve just used DAX (PowerPivot’s extension to the Excel formula language) to create calculated columns in the PowerPivot window.

There’s a lot of utility in DAX calc columns, but that’s not where DAX really shines.  DAX measures let you do things in Pivot reports that simply weren’t possible in Excel before.

In the last video, I mentioned that merely showing raw sales amount didn’t really tell me whether my products sold better or worse in varying temperatures, since there are varying numbers of days of each temperature – perhaps the relatively low sales on Hot days is just because there weren’t many Hot days?

Enter DAX measures, a PowerPivot feature on par with relationships, slicers, and mashups.  But you might overlook them on a casual tour through the PowerPivot for Excel addin, since they live behind a single ribbon button.

DO NOT OVERLOOK DAX MEASURES!  There is literally a world of power lurking there.  I want to make absolutely sure that you grasp them – both their power as well as the “how to,” so I will spend the next couple of posts on them.

First, a word about videos vs. text and screenshots

Awhile back I asked for feedback on which format was preferred – text and screenshots, or videos.  Videos got more votes, by about two-to-one.  But my text-and-screenshot posts are getting more views than my video posts.

Clearly, those of us who are too busy to watch videos are also too busy to respond to surveys 🙂

I respect that – personally I think I prefer to consume text and screenshots over videos.  A video seems like a commitment, whereas scanning text is on my terms, even if on net I spend the same amount of time on either.  So here is my new philosophy, always subject to revision:

  1. I’m going to use videos whenever I am introducing something new, that benefits from the explanatory power of video
  2. Quick tips and tricks, as well as “power” techniques – I think I’m going to lean toward text and screenshots for these, unless it’s an in-depth technique
  3. Even when I use video, I’m going to try to summarize the content of the videos – not as an attempt to replicate their content, but more as a table of contents so you know why I think it’s worth your time to watch the video

So, I’ll share the videos first, and a summary after.

The Videos

PowerPivot DAX Measures Pt 1

PowerPivot DAX Measures Pt 2

(These are the fixed versions from 11/24/09)

Videos Summary

  1. Quick demo of conditional formatting and number formatting, and how surprisingly impactful they are
  2. The DAX Measure Dialog
  3. Qty per Day can be calc’d using =SUM([Quantity]) / COUNTROWS(RELATEDTABLE(DimDate))
    1. Simple formula, but how does it work?  Please consult…
  4. …The Five Golden (and Simple) Rules of DAX Measures
    1. Home Table = The table where the numeric columns are
    2. Columns always wrapped in aggregation functions like SUM()
    3. Calcs always are working against the source tables, not the pivot report
    4. Two phases:  Filter, then Calculate
    5. Think of things as if they happen cell-by-cell
  5. Once you grasp those, you can suddenly do AMAZING things

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 34 Comments
  1. Hi Rob,

    Great explanation. I think for the BI pros this is self explanatory since it is very similar to OLAP. But I do like those new DAX function like relatedtables, wish i have those available in SSAS :).

    Kasper

  2. Rob, excellent videos! Interestingly, I came up with a “golden rules” list for my own reference, but your list is much better articulated. The one rule I would add to your list is this: Break down complex calculated measures into partial calculated measures. It makes the overall calculation easier to follow and improves calculation performance. This was actually suggested by Karen Aleksanyan, and it’s helped me *a lot* with complex calculated measures.

    I’ve painfully discovered that DAX isn’t a total panacea though. To help get comfortable creating DAX calculated measures, I attempted to redo some of my existing solutions using PowerPivot. Lo and behold, I actually hit a wall before too long. The problem? Perhaps the biggest one is the lack of a standard deviation function. There are huge classes of business problems that use standard deviation. The omission is quite puzzling. Consider this: Virtually every database on the planet (databases aren’t known for their number crunching prowess) includes SD as a standard aggregation. Furthermore, in almost every area of Excel where aggregations exist, *including* the list of PivotTable standard aggregations, SD shows up. I simply assumed that it was going to be added to CTP3 along with the other new functions. Anyway, I’ve added this one to my growing v2 wish list.

    There are other things I can’t do that require Excel functions that aren’t currently supported. What’s come out from this exercise is that I have a much better understanding of where I’m limited with DAX and the future projects that may not be a good fit for PowerPivot. I haven’t totally given up though. My next exercise is to see how I might derive the functions I need using available function primitives.

    1. Thanks for the above it pointed me in the right direction.
      This worked for me. I know my style is to over use brackets where they are not needed, maybe someone can tidy them up. SquareActualPrice is a calculated column added to the underlying data.


      =SQRT((Sum([SquareActualPrice])-(count([Actual Price])*((sum([Actual Price])/count([Actual Price]))^2)))/(count([Actual Price])-1))

  3. Amir, thanks. I planned out a solution for SD based on a couple of calculated measures. I haven’t been able to test this yet because I uninstalled Office 2010 TP a few days ago. I’ve just installed the new Office 2010 Beta but now I’m waiting for PowerPivot CTP3 🙂 Using your version of the formula for a range in Excel, I got an error for N-1 because the second term was larger than the first (before calling SQRT). However, N returned the correct result for STDEVP. Another thing – depending on the fields in the PivotTable, a filter might have to be removed (using ALL([column]), but that’s a minor detail.

    Admittedly, SD (and its sidekick, variance) are among the simpler derivations using math and basic aggregate functions. Figuring out inverse chi-square is another matter. However, I have to appreciate the current limits of the product. It’s not designed for statistical analysis, although it’s great for a lot of other types of analysis. Excel is no better, unless you use VBA…which isn’t supported by Excel Services…in case you want to publish your model.

  4. Rob,

    It would be very helpful to have a little more explanation of the RelatedTables function. Although it makes sense, having some data in the DimDate table in the video would really help drive the concept.

  5. Hi Rob,

    I’m playing around with DAX but have some trouble.

    I have created a measure in the FactIntetsales that determines the max month:
    =max(‘DimDate'[MonthNumberOfYear]) what happens is that this returns the max month in the current year (i put year on the x-ax). No Related function was needed? How does this work ?

    I have thrown my initial idea of sets overboard (from my business case blog post) and decided to fix it with formulas. I want to create a formula that calculates the sum of orderquantity from the last month in a year. I have created the following formula:
    =CALCULATE(sum(‘FactInternetSales'[OrderQuantity]),DATESMTD(LASTDATE (‘DimDate'[FullDateAlternateKey])))
    This works ok for all the years that have an entire year. The year 2008 has only 7 months and doesn’t show a value. When i select month 7 it shows the value. What happens here? Looks like he always wants to go to month 12 ?

    And lastly i want to get to YTD of previous year, i created the following formula:
    =CALCULATE(sum(‘FactInternetSales'[OrderQuantity]),DATESYTD(PREVIOUSYEAR(‘DimDate'[FullDateAlternateKey])))
    This doesn’t show a value.. what happens here ?

    Could you point out where I go wrong with my reasoning? I have the idea i’m missing one essential idea here..

    Thanks,
    Kasper

    1. Kasper, as to your first question: yes, RELATEDTABLE is not required. I was being overly explicit in the video. The measure I created there is 100% identical if I omit the RELATEDTABLE function and simply do COUNTROWS(DimDate)

      I will need to post a follow-up clarifying that.

      Looking into your other two questions, getting someone from the team.

    2. Wow, Kasper. Even your first question has touched off a bit of a debate internally. There are beliefs about how things should be working but those aren’t yet matching up with what I am seeing.

      So the plot thickens 🙂 Stay tuned. I love betas that uncover stuff like this. Too often we release a beta and never hear much back from customers. Kudos to you Kasper!

      1. Hi Rob,

        Glad that I can help, I try to take scenario’s as i see them in my daily work and try to solve them in PowerPivot and i always want to know how things work :). And i’m glad you guys have created such a community where we can talk with you guys before the product is on the market (I’m bugging Dave as well)

        I will stay tuned!

        Kasper

    3. OK Kasper. Your first question merely resulted in me re-recording two videos, updating the downloadable workbook, and posting an announcement about the changes 🙂

      Second question. Howie answered it thusly:

      When using Time intelligence functions like LASTDATE or DATESMTD, in CTP3, it is necessary to add another argument which is ALL (TimeTable). This won’t be needed post CTP3. Without this, only the selected dates are considered, and you can’t find the last month unless you are in the last month. Note that if his data only goes to month 7, then that is correctly the last date.

      Similarly, he needs ALL(TimeTable) as an argument to PREVIOUSYEAR and DATESYTD in CTP3.

      …on to your next question that reshuffles my entire day 🙂 Keep ’em coming, this was good.

      1. Thanks Rob,

        I have the third function working by adding the the filter:
        =CALCULATE(sum(‘FactInternetSales'[OrderQuantity]),DATESYTD(PREVIOUSYEAR(‘DimDate'[FullDateAlternateKey])),ALL(‘DimDate’))
        This gives me the ytd of the previous year. Great!

        But the second “Total last month of year” still keeps showing up empty in 2008 that goes to month 7, the other years that go to month 12 show ok. I have added the All(‘DimDate’) as calculate filter so all the dates are scanned
        =CALCULATE(sum(‘FactInternetSales'[OrderQuantity]),DATESMTD(LASTDATE (‘DimDate'[FullDateAlternateKey])),ALL(‘DimDate’))
        I have added my sheet to my skydrive so you can see for yourself:
        http://cid-7f4e0559cc74581a.skydrive.live.com/self.aspx/Openbaar/testdax.xlsx

        But what i understand is that the timeintelligent functions will be working differently in post CTP3?

        One more question that popped up, i could add the ALL (TimeTable) to my YTD measure, this should make sure i always get the value of the entire year. But adding this =CALCULATE(TOTALYTD(sum(‘FactInternetSales'[OrderQuantity]),’DimDate'[FullDateAlternateKey])),ALL(‘DimDate’))
        Gives me an empty values.

        So what does CALCULATE( ,ALL(‘DimDate’)) do? Select all dates within current x and y contexts regardless of filter?

        Kasper

      2. OK, Kasper. Time to finally answer your question. My apologies.

        Marius looked at your workbook and here is his reply:

        For 2008, Kasper has rows in the Date table for the first 8 months, so DatesMTD/LastDate should indeed return the quantity number of August.
        However, he only has data for the first 7 months (January thru July), which is why we return a blank cell – there were no sales transactions for August 2008, even though the days of that month does show up in the calendar table.

        So, Kasper’s options are:
        1. Do nothing – the results are correct, since there were no sales in the last month of 2008 (August).
        2. Use LastNotBlank() instead of LastDate(), if that’s the actual intention of the calculation.
        3. Remove/filter out the August 2008 rows from DimDate when importing that table, if those rows weren’t supposed to be there in the first place.

        Howie also added this suggestion:

        I think he wants something like this:

        =CALCULATE(sum(‘FactInternetSales’[OrderQuantity]),DATESMTD(DimDate[FullDateAlternateKey]), ALL(DimDate))

        Note: is [fulldatealternatekey] really the datetime column in DimDate? If not, please use a datetime column.

  6. […] the time intelligent functions are available i decided to go and use them, thanks to Rob at his PowerPivotPro’s DAX post who gave me the […]

  7. Through constant trial and error, I’m trying to work around ‘the wonders of Dax Measures” (and I thank you for your work so far!!). By the way – is there some online DAX/Powerpivot Chat forum to make resources (such as yourself) quicker?.. but I digress.

    So, I have one sales table, cust.#, InvoiceDate, Sales, Qty and a few calculated measures – month, year, qtr, Volumes and a couple of others. What I was trying to do with Measures was produce the same effect as the slicers I have set up – nice and customizable results, however being REALLY new and trying to grasp certain concepts like adding measures to dax for calculations, I was moving nowhere fast. Rewatching the videos, I was intrigued by the Distinct() function. It seems to work for a simple calculation but … I DON’T KNOW WHY.

    =CALCULATE(SUM(‘Sales'[Sales]),’Sales'[CustomerNumber]=”415247″,DISTINCT(‘Sales'[Sales])) – sure,it may not be the prettiest layout, but it did produce a correct sum. What has me stumped is that it produced the sum for 2009. My sales database has sales going back to 1998. So, great – Distince works, giving me correct sales, but why does it default to one year (last full year) only?

    1. Hello John! Glad I’ve been of help so far, let’s see how I do on this one 🙂

      First of all – forums. Here are two. I am not active on either of them, but other folks are:

      http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/threads
      http://powerpivot-info.com/forum/4-powerpivot-and-dax-forum

      I’m a bit worried about the DISTINCT fxn and its use in that formula – can you describe what the measure is intended to do? If it’s just sum of sales for that customer, it seems you could omit that entire last argument.

      As for why you are seeimg just 2009 sales, my obvious question is whether that customer bought anything in other years. After that, I wonder about the DISTINCT fxn and its effect on the formula.

  8. This table is just getting funky. I’ve moved a copy to my 64 bit system. I’ve removed the Distinct formula and I getting the same result. The only problem is that this customer has total sales history of over $3 million. It is showing 2009 total and I don’t know why. Lots of trial and error to figure out what’s going here. Thanks for the additional sources.

  9. Kasper de Jonge BI Blog » PowerPivot time intelligent functions revisited: why use ALL() and how to work around it says:

    […] expected. The reason is because the time intelligent function requires an additional parameter, in a blog post at PowerPivotpro.com the PowerPivot product team gave the following answer to my question to why the result is not as […]

  10. I have a question about Standard Deviation and Six Sigma related to PowerPivot. I understand how to calculate it from the above information, but what I don’t understand is how can I use it as a value on a Power PivotChart.

    What I am trying to do is view the standard deviation across a time scale, and then use different variables to slice it, to see where things are going wrong.

    I have the raw data values prior to the pivotchart, as I would have to calculate every value based on every combination of variables, but what I really want to do is only calculate it based on when the user selects a different slice.

  11. […] the Excel’s PivotTable and an Excel-like formula language (DAX) bolted on. PowerPivot models built with an understanding of the usefulness of the star-schema […]

  12. Wow – this is so awesome! This video, this blog, the way you write articles – completely matches how i think and process information. You have a fan for life and you are now on my bookmark bar (expensive real estate!)

  13. Hi Rob,
    Thank you for the videos. They are simply the great and really helpful.

    I’ve a requirement to write a measure that searches for a substring and count the number of rows. Tried and failed to get it. Could you please help? Thanks in advance.

    thanks
    Shyam

  14. When creating a YTD Measure for multiple tables that are in my Excel Powerpivot I get some that return data correctly and some ytd measures that return blanks. I am not sure why when the measure worked the last time I opened the file.
    “YTDE:=calculate(sum(extmth1[approved_amt]),datesytd(dates[date]))”
    I have rebuilt the relationships rebuilt the formula and refreshed all related tables with no luck. Could this just be a glitch in powerpivot or am I missing something? I have a similar formula working on another table.
    “YTDB:=calculate(sum(budgets([budget]),datesytd(dates[date]))”
    Thanks in advance.

  15. I used the standard deviation formula – it worked for Standard Deviation (N) -however I could not get the (n-1) method to work…so I reworked the formula for both and I found this works.
    Standard Deviation (P) N
    SQRT(SUMX(Products,Products[UnitsInStock]^2-SUMX(Products,Products[UnitsInStock]/COUNTROWS(Products))^2)/COUNTROWS(Products))

    Standard Deviation (S) n-1
    SQRT(SUMX(Products,Products[UnitsInStock]^2-SUMX(Products,Products[UnitsInStock]/COUNTROWS(Products))^2)/(COUNTROWS(Products)-1))

    I am new to this so any input on if this is correct would be great – it did work on my end in both cases (for what I was working on).

    Best,
    Scott

  16. I have a P/Pivot table with two columns of interest – Date (of transaction) and Qty of Hire Days. This is a table of assets recording hire transactions to customers over time (there can be multiple records for each asset per month with Qty of Hire Days having two possible values: =0 (no hires in month, 1 record for the month) and > 0 (quantity of days on hire to a particular customer, could be multiple records in a month)).

    How do I display in a pivot table the date each asset was last hired to a customer?

Leave a Comment or Question