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:
- I’m going to use videos whenever I am introducing something new, that benefits from the explanatory power of video
- 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
- 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
(These are the fixed versions from 11/24/09)
Videos Summary
- Quick demo of conditional formatting and number formatting, and how surprisingly impactful they are
- The DAX Measure Dialog
- Qty per Day can be calc’d using =SUM([Quantity]) / COUNTROWS(RELATEDTABLE(DimDate))
- Simple formula, but how does it work? Please consult…
- …The Five Golden (and Simple) Rules of DAX Measures
- Home Table = The table where the numeric columns are
- Columns always wrapped in aggregation functions like SUM()
- Calcs always are working against the source tables, not the pivot report
- Two phases: Filter, then Calculate
- Think of things as if they happen cell-by-cell
- Once you grasp those, you can suddenly do AMAZING things
The videos are interesting and easy to follow.. Keep posting more videos. Thanks.
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
Rob,
Excellent.
Can we keep DAX a secret between you, me & Kasper?
-Greg
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.
hope ms can make colin happy! rob?
Colin,
Lack of STD is not a very high wall. No higher than 2 feet. You can easily jump over it.
Use:
SQRT(
Sum(x^2)/countrows() –
(sum(x)/countrows())^2
)
some use N-1 instead of N (i.e. countrows()-1)
Look for more details at
http://knol.google.com/k/suresh-emre/how-to-compute-standard-deviation-in/35vsnxisjn2mw/128#
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))
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.
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.
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
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.
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!
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
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.
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
Sorry Kasper, I fell behind and did indeed forget about this one. I’ll track down some answers and get back to you.
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.
[...] Posts Introducing PowerPivot DAX MeasuresWhat is PowerPivot?Visualizing SQL Indices with [...]
[...] 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 [...]
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?
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.
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.
[...] DISTINCT – returns the set of unique values of a particular column within the current pivot filtering context, very useful (so far) when combined with COUNTROWS. [...]
[...] 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 [...]
[...] 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 [...]
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.
[...] 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 [...]
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!)
Thank you Kate, that is very nice to hear
-rob
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