PowerPivotPro

Note from Rob

Post is late today NOT because of Chris, but because of me.  Chris had this to me last week in fact, so don’t go thinking that he was slow or something

I am particularly interested in this post because I have personally not yet used USERELATIONSHIP() yet.  OK, take it away Chris…

I couldn’t stop there…

I was feeling pretty good with the result from Tuesday…I helped my customer out. I got a new DAX tool for my toolbox. Life was good. But then I got thinking, “But what if they want to know how many promotions started or ended in May?” “I need that relationship back!”

To tackle this, I created two relationships between DimDate and DimPromotion. One on the StartDate of DimPromotion and one on the EndDate. The key to doing this without breaking my previous measure was to make these relationships inactive.

Read the rest of this entry »

The “Return Blank for Totals” Measure Written Three Different Ways – via HASONEVALUE, ISFILTERED, and HASONEFILTER.  So far, only HASONEVALUE seems to have a flaw.

Picking up from Tuesday’s post, which drew some quick comments, let’s add one more function to our evaluation:  HASONEFILTER().

Here are the three measure formulas from the pivot above.  They are all the same except for the highlighted function:

IF(HASONEVALUE(Stores[StoreName]), [Units Sold], BLANK())

IF(ISFILTERED(Stores[StoreName]),[Units Sold],BLANK())

IF(HASONEFILTER(Stores[StoreName]),[Units Sold],BLANK())

As covered on Tuesday, HASONEVALUE “fails” when you have a parent item (Region 0, in this case) that only has one child item (Store 26 in this case). 

But ISFILTERED and HASONEFILTER do not have that problem, and they seem to be returning the exact same results.  Let’s “differentiate” them shall we?

Bring in the Slicer!

Read the rest of this entry »

A Common Trick:  Having a Measure Do Something Different on SubTotal and GrandTotal Cells

A Progression of Technique

It’s been awhile since I’ve written a good old honest formulas and techniques post, so I figured today would be a good time to “get my hands dirty” again.

Ever since PowerPivot v1, I’ve been writing measure formulas that “detect” whether the current pivot cell is a “total” cell (either a subtotal or grandtotal), and then doing something different if it is.

In the image above, my [Blank if Total] measure returns BLANK() for all totals, otherwise it returns the same value as the original [Units Sold] measure.

In PowerPivot v1, that formula would have looked like:

=IF(COUNTROWS(VALUES(Stores[StoreName]))=1,
    [Units Sold],
    BLANK()
)

…which basically says “if I have more than one value for StoreName, return BLANK(), but in cases where I have only one value, return the original measure.”

That was clumsy – a lot to write for a simple “do I have one value” test.  So in PowerPivot v2, they gave us the HASONEVALUE() function which makes things cleaner:

=IF(HASONEVALUE(Stores[StoreName]),
    [Units Sold],
    BLANK()
)

Read the rest of this entry »