Kasper de Jonge Video

November 24, 2009

I just “met” Kasper a few weeks ago via Twitter, and quickly found out he’s a basketball nut – he plays the game a lot, and watches NBA broadcasts at some very strange hours :)

I challenge you to watch this video, listen to his voice, and imagine him as anything other than a seven-foot-tall shot-blocking monster in the lane.  Yeah, he claims he is six-foot-three and a perimeter shooter.  Sure he is.  Self-report is a notoriously unreliable survey technique Kasper, so I’ll just stick to extrapolating wildly from small amounts of unrelated data, OK? :)

Jokes aside, it’s a great video.  He shows off some things that I have not, such as pulling data into PowerPivot from an SSAS cube, and using the Remove Duplicates feature in Excel to clean one of his tables.  Bravo!

http://business-intelligence.kdejonge.net/using-powerpivot-to-combine-cube-ssas-data-with-manual-data-the-movie

6 Comments | PowerPivot Links | Tagged: | Permalink
Posted by powerpivotpro

Thank you

November 24, 2009

Saturday morning, less than a month after I started this site, I took the following screenshot off of the stats page:

                  10k Views

Wow.  That is incredibly gratifying, folks.  It helps, of course, to have a hot product like PowerPivot to talk about.

But this means a bit more to me than you might think.

This summer, my ex moved to Cleveland with the kids, which meant that I was soon to follow.  After a career of developing “big box” software while employing a heavily “in-person” style, suddenly I was across the country and wondering how I was going to remain in the game.  Cleveland is not renowned for big-box software development :)

Much of that remains to be seen – I’m still with MS, still part of the PowerPivot team, and we’re still figuring out the best fit for me going forward.  But it’s a good kind of uncertainty now.

I was worried, leaving Redmond.  I’m not worried anymore.  In fact I have not felt this recharged in years.  This site, and the people I’ve met through it, is a HUGE part of that.  If I end up leaving MS at some point, this site goes with me, which is why I am here instead of on MSDN.  This is very much my personal creation, and at my expense.  It’s worth it.

And hey, I got to take a cross-country roadtrip with the kids.  How often does that end up feeling like the practical thing to do?  Truly a once in a lifetime experience:

               Chicago Museum of Science & Industry

                                                The PowerPivotProgeny

So…  thanks folks.  I’ll keep working to make this place worth visiting.  You keep sending comments and questions :)

Thanks,

-Rob

9 Comments | Hello World / Administrivia | Permalink
Posted by powerpivotpro

Fixed Version of the “Qty per Day” Measure

November 24, 2009

“Unless I am wrong, and I am never wrong, they are headed dead into the Fire Swamp.”

-Prince Humperdink

A funny thing happened today.  Kasper de Jonge asked a couple of questions (in comments) about the Introduction to DAX Measures post.  I answered the “easy” question, and then went off to find the answer to the difficult questions.

Heh heh, I had them backwards.  The “easy” question led to an in-depth discussion with Howie and Marius from the DAX team, during which I discovered an error in the “Qty per Day” measure.

I’ve uploaded fixed versions of the videos, so anyone watching for the first time (or re-watching now) will get the proper information.

And the workbook file available here is also fixed.

If you watched the vids already and don’t want to watch them again, here’s a quick summary of what I changed:

  1. Remember the golden rule, “Filter then Calculate?” 
  2. Filter only impacts the “home” table (aka Fact table).  So far so good.
  3. But the filtered home table is never then used to then filter other tables during the Calculate phase.  Not even if you use RELATEDTABLE().  So for example:
    1. In my former example, I used COUNTROWS(RELATEDTABLE(DimDate)) as the denominator of the measure
    2. That expression, however, was returning the grand total number of rows in DimDate, no matter how the pivot was sliced.  (Because of point 2 above)
  4. The fix was to use the [OrderDateKey] column from the FactInternetSales table (the home table) and do a COUNTROWS of the DISTINCT set of values in that column.
  5. Note that if there had been a slicer (or row/column field) from the DimDate table in the pivot, then the DimDate table would have been filtered, but:
    1. Not as a result of the Fact table being filtered
    2. It is strictly filtered only by fields in the pivot that came from the DimDate table
    3. And it is therefore much less filtered than the Fact table, which is filtered by all of the fields in the pivot
  6. Along the way I ran into the fact that the DISTINCT function requires an explicit reference to TableName[ColumnName], and does not accept the shorter [ColumnName] only syntax.

…yeah, the revised videos might still be worth a look :)

4 Comments | DAX, Non-Football Examples | Tagged: | Permalink
Posted by powerpivotpro