More Fun With IF(VALUES())

  “I mean, IF() I’m paying top dollar, I want a little production VALUE()” I bet you thought I was out of movie quotes for IF(VALUES()) didn’t you!  If I had a fake ID, my name would be “McPivot.” Bag of Tricks OK, once you have a reasonable level of comfort with the workings of IF(VALUES()), whether that comfort is “I understand it” or even just “I can make it give me the right results,” you are in for some…

Read the Rest

The Magic of IF(VALUES())

  “I'm Dan Marino, and IF() anyone knows the VALUE() of protection, it's me.” Boom!  An Ace Ventura quote finally graces the blog.  Yes folks, that fine work of American cinema did indeed feature Dan Marino reprising his real-world ad for Isotoner.   (UPDATE:  I had the val_if_true, val_if_false arguments reversed in my sample formulas, even though I had them correct in Excel.  I’m rusty .  Fixed now.) But more importantly, I wanted to revisit this little two-function combo:  IF(VALUES()). …

Read the Rest

Six Months With PowerPivot, part one

    “If you don’t know me by now…  you will never never never know me…” In honor of PowerPivot v1’s public release, I thought I’d offer up my thoughts on my last six months working with the product.  Remember, I physically left Redmond in August and then officially left Microsoft in February, but have been putting PowerPivot through its paces that whole time, with a wide variety of data sets and business scenarios. So in some sense, you can’t really…

Read the Rest

Quick Tip: Don’t Over-Use FILTER()

The other day I was working on an updated version of my Team Playcalling report, which, by the way, look FABULOUS with Slicers: Only problem with it was that it was taking 90 seconds to refresh.  Unacceptable. Howie Dickerman and Marius Dumitru, two of the Superheroes of DAX at Microsoft, pointed out that my measures were using the FILTER function in places that were not necessary. For instance:   [New Measure] = [Original Measure] (                        FILTER(DataTable[PlayType]=”Pass”)                                               ) Can…

Read the Rest

RANK measure finished

    “…I always see the job through.” -Angel Eyes Yep, I said I would return and finish off the Rank measure.  Time to deliver.   (And this makes three straight Spaghetti Western movie quotes!) Where we left off – Unknown ranks #1 Last time, I’d written a mostly-working version of the measure that ranks a player by his Rushing Yards value, and that doesn’t return an error when I leave the Unknown player in the pivot:   But the…

Read the Rest

Rank continued – dealing with unknown members

    TUCO:  “There’s no name on it!” BLONDIE:  “Well, there’s no name here, either.”     Ode to YouTube HD When I used to work on Bing and would look at search logs, I was always puzzled by some of the search strings that went by.  Well today I gave them one of my own –“Clint Eastwood holding rock,” among others.  I hope that confuses someone. YouTube in HD, though – you are my new best friend for finding…

Read the Rest

Writing a RANK measure (and living to tell the tale)

  “Number One, I order you to take…  a number two.” -Captain Jean-Luc Butthead     Before I get started, do you have any idea how hard it is to find cartoon images from the pre-Internet era?  I just lost ninety minutes, first looking for the Far Side cartoon of “Custer’s Last Group Photo” (where everyone is holding up the “we’re number 1!” finger – get it?) and then struggling to find a version of the Beavis and Butthead image…

Read the Rest