PowerPivot

I just published another PowerPivot article. This one is on using a HASONEVALUE DAX function in an IF statement to flip the sign on a single portion of a PivotTable.

You can find the article by clicking here

Comments welcome both here or on the article.

I just added 2 new PowerPivot articles to the knowledgebase on this website.

The first is the formal article for Creating a Spacer Column in a PowerPivot PivotTable, which orginally came from Monday's blog post. I actually updated that article already, as I found that a single space wasn't enough to keep the spacer column width sufficient.

The second article is on Hiding Calculated Items With Zero Totals in PowerPivot PivotTables, which was inspired by Debra's blog post ...

I was amused to read Mike Alexander's blog post on Add Column Spacing In A PivotTable this morning, as I did that exact thing yesterday while working through a PowerPivot solution. At least, I did the same thing, but I did it using DAX. And in this case, DAX is even easier to use that the old method, taking one less step.

Keep in mind I'm using Excel 2013 for this, but it should be the same in 2010.

First I created a new Calculated Field (Measure in 2010):

...

As many readers of this blog will know, I am a huge fan of PowerPivot, and honestly believe that this is THE most important feature to hit Excel since VBA was introduced to the product. And anyone who has ever taken a course from me knows that VBA is the greatest thing to be added to Excel since the grid was mapped into rows and columns.

For those who don’t know, PowerPivot was a free add-in to Microsoft Excel 2010, and is now baked into the Excel 2013 release. It basically allows us ...

I've been doing a lot of work with PowerPivot where I connect to databases. My normal development cycle is as follows:

• Connect to a view or table, pulling in ALL columns
• Work through my data scenario until I've worked out my logic and solve the issue I'm trying to solve
• Cut the table/view query down to just the required columns
• Add WHERE clauses to cut the data down to just the bare minimum I need
• Deploy to my users

By doing all this, I really focus on trying ...