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.
In PowerPivot, tables may have more than one relationship to each other but only one may be active at any time (inactive relationships are denoted by a dotted line). All DAX expressions use the active relationship by default. However, you can change this behavior with the USERELATIONSHIP function. The USERELATIONSHIP function is a filter function that must be used in conjunction with another function that takes a filter as an argument like CALCULATE.
To answer my “how many promotions started/ended?” question, I created two new measures using the USERELATIONSHIP function:
Promotions Started=
CALCULATE(COUNTROWS(DimPromotion),
USERELATIONSHIP(DimPromotion[StartDate],
DimDate[Datekey]
)
)
Promotions Ended=
CALCULATE(COUNTROWS(DimPromotion),
USERELATIONSHIP(DimPromotion[EndDate],
DimDate[Datekey]
)
)
Slicing my pivot table by the calendar and using these three measures gives me:
Checking these numbers against the data in the Promotion table shows that each of the columns gives the correct values. The items on the “(blank)” row are because there are Promotion rows that have dates that are outside the range in the Calendar table.
Including both the Active Promotions measure and the Promotions Started and Ended measures lets us do some interesting things like compare the number of promotions starting and ending in a time period to the total that are active in that time period and then even plot those values against a current sales trend.
Hopefully this helps someone out. Removing the relationships from a model isn’t always the right solution but it is in some cases. The USERELATIONSHIP function coupled with inactive relationships gives you the ability to do a lot more with your models and calculations.
