Where to begin: PowerPivot for Excel

To anyone who may be listening... This isn't so much a comment about this particular post, which is fine, or about PowerPivot, which is a great tool, but about the problems that the Office blog seems to have developed. I don’t know where else to post this comment.

Firstly, I need to say that I love Office 2010, and my comments have nothing to do with the software itself. I also really appreciate the extensive amount of material that the different blogs cover – much of it is genuinely helpful. However, it’s getting to be a real mess to navigate – there are simply too many blogs, too many overlapping sources of information, and no apparent quality control mechanism for  the material (videos especially, in my opinion, which can vary hugely in terms of content and production standards). Every time I come here I can find myself following some interesting trail of information from a link in a blog post, and end up in so many different places that I can never remember how I got there  (the latest being ‘Microsoft showcase’ which I’d never heard of before, and which frankly is a bizarre mishmash of different bits of software from different bits of MS – incoherent and confusing) – I know that I have seen a lot of interesting material whilst on these ‘hikes through the backwaters’ but now simply can’t remember where any of them are, and the search tools can’t seem to get me back there again.

Now it seems that blogs are being hit by spam, and some (‘Office Matters’?) are apparently not being updated at all – that one has been badly hit with spam BTW, none of which is being removed, which gives a really bad impression to new readers and looks very unprofessional.

The blogs also lack clear differentiation – what does ‘office matters’ provide that ‘office in education’ or ‘office IT pro’ don’t? It’s not clear – and that’s before we get to all of the separate blogs for each product. And let’s not forget that office.com has its own set or resources as well.

Can I reiterate that I am very grateful for all of the work that many people put into these blogs? I am, however, losing patience with the sheer complexity of it all – someone really needs to review all of these sites and try to streamline them into a set of resources which make life easier for readers. There’s a huge amount of quality stuff here, but it’s like hunting for a single product in a huge supermarket where none of the aisles are clearly marked – you know it must be there somewhere but can you be bothered to search all day for it?

@Huw Jones

Hi. I just responded to your comment on my post about the site's new look, but wanted to reiterate and add something here. First, thank you for such deep, thoughtful comments about the blog. As you can tell from the new design, we agreed with you on many points, and were already in final stages of preparing the new design when you offered your perspective. But no design is perfect, and the issues you raised here wrt video content, variability of quality, and having too many blogs across too many properties are all things we're still working on. The blogs' organization will improve in time as we consolidate and prune.

As for the video content, we serve up a huge variety of approaches to video information, from highly produced "shows" on the Office Show blog to very short how-to's. We're continuously experimenting. Because evaluating videos can be a real challenge (so much can depend on personal taste), any feedback you and other readers can offer helps us out. So please feel free to tell us what you like, don't like, would like to see covered, etc. And of course the same is true of non-video content as well.

So thanks again.

I was trying to understand the earlier function , by reading this social.technet.microsoft.com/.../powerpivot-dax-filter-functions.aspx, but I still don't get it.

Specifically in this part:

A new calculated column, SubCategorySalesRanking, is created by using the following formula.

= COUNTROWS(FILTER(ProductSubcategory, EARLIER(ProductSubcategory[TotalSubcategorySales])<ProductSubcategory[TotalSubcategorySales]))+1

The following steps describe the method of calculation in more detail.

The EARLIER function gets the value of TotalSubcategorySales for the current row in the table. In this case, because the process is starting, it is the first row in the table

EARLIER([TotalSubcategorySales]) evaluates to $156,167.88, the current row in the outer loop.

The FILTER function now returns a table where all rows have a value of TotalSubcategorySales larger than $156,167.88 (which is the current value for EARLIER).

The COUNTROWS function counts the rows of the filtered table and assigns that value to the new calculated column in the current row plus 1. Adding 1 is needed to prevent the top ranked value from become a Blank.

The calculated column formula moves to the next row and repeats steps 1 to 4. These steps are repeated until the end of the table is reached.

could you please describe in the example above, wat is the inner and the outer loop? And could you please describe what happens in these four steps for the second row.

I thought that the earlier function returned the value from the previous row, but I can see that it's more complicated than that.  To check this I created  a very simple table , and then I added a calculated column just with the earlier function , to see if the value from the previous row was returned, but I got errors.

Thanks in advance

@pmxgs0

"could you please describe in the example above, wat is the inner and the outer loop? And could you please describe what happens in these four steps for the second row"

The outer loop is defined by the values in the EARLIER function and the COUNTROWS function; the inner loop is developed in the FILTER function.

You have to remember that you are providing a count of rows for each row in the table (that how you are doing your ranking) and for each counting of rows you are creating a new filtered table.

So, once you have the COUNTROWS evaluated for the first time (where TotalSubcategorySales = $156,167.88) you move to evaluate the count of rows that are greater than $220,720.70

step 1 current row is defined as the second row; EARLIER gets the value of TotalSubcategorySales for the second row

step 2. EARLIER returns a value of $220,720.70 (the current row where the COUNTROWS function is being evaluated)

step 3. The FILTER function returns a copy of 'ProductSubcategory' table where all rows have a value of TotalSubcategorySales greater than $220,720.70

step 4. COUNTROWS returns the number of rows in the above filtered table and assigns that value to the SubcategoryRanking column for the second row (the value, according to the sample is 14).

Maybe, the key point here is to understand that at each evaluation of COUNTROWS (in every row) the FILTER function starts with a new copy of the 'ProductSubcategory' table and filters that copy.

I hope this helps you to understand the elusive EARLIER function. If you need more clarifications please don't hesitate to ask for them.

With my best regards,

JuanPablo Jofre

Technical Writer for the Analysis Services Team at Microsoft