Time Role Playing Dimension multiple relationships -- how to use

[sreachard]

I've got a simple model with a earnings(fact) table and a date dimension.
earnings have an earnings period and a systemasof date, earned premium is the amount earned for the earnings period, the systemasof date is the entry date.

the business use is to sum all premium earned for the selected periods, but only if the system as of date is also with the earnings period.

in this example http://sdrv.ms/XuqJoR
the earnings period for 12/25/2012 has an as of date in 2013 for this report it can not be counted for 2012 because it would not have existed until 2013

Selecting january to december 2012 should total 1100.

i have two inactive relationships set up to the date dimension and i am attempting to use the userelated() function to filter the dates but i can not get it to work with both relationships. it works with 2 date tables but not with one roleplaying and it works if i filter the fact but i'm hoping it is faster filtering the dimension and using a relationship.

here are the measures:

This one works but in the real model the earnings table has 250M rows performance should be better filtering against the lookup table

EarnedPrem:=CALCULATE(sum(Earnings[EarnedPremium]),USERELATIONSHIP(DimTime[CALENDARDATE],Earnings[EarningsPeriod]),
filter(values(Earnings[SystemAsOfDate]),Earnings[SystemAsOfDate]<=Max(MonthEnd[MeDate])))
​

This one does not work, the systemasof relationship is not working as i would expect

EarningsDimTime:=calculate(CALCULATE(sum(Earnings[EarnedPremium]),USERELATIONSHIP(DimTime[CALENDARDATE],Earnings[EarningsPeriod]))
,USERELATIONSHIP(Earnings[SystemAsOfDate],DimTime[Calendardate]),filter(dimtime,DimTime[Calendardate]<=Max(MonthEnd[MeDate])))
​

This one works but requires 2 time dimensions, if this is the only choice i'll go with this but dim time should roleplay

EarningsDimtime2:=calculate(CALCULATE(sum(Earnings[EarnedPremium]),USERELATIONSHIP(DimTime[CALENDARDATE],Earnings[EarningsPeriod]))
,USERELATIONSHIP(Earnings[SystemAsOfDate],DimTime2[Calendardate]),filter(dimtime2,DimTime2[Calendardate]<=Max(MonthEnd[MeDate])))​

 

[Laurent C]

You cannot use both relationships at the same time.
You may want to mark one of your relationships as active (this would then be the default one).
The way your measures are defined, you may encounter the following issue: say you have your report filter for 2012 with the quarters on rows. You could have a transaction in Q1 that was not in system before Q2. Then your result for Q1 would not return the same result as if your report had been filter on 2012 - Q1.
Is it OK? Because if it is not you might consider a calcualted column containing the appropriate reporting date, and then create an (inactive) relationship to your DimTime table, or simply have a calculated column already containing the logic of your calculation (IF(SystemAsOfDate ...) )
If it is OK, then you will indeed have to go through a FILTER.

 

[sreachard]

Thanks, thats disappointing, but that's the conclusion i was arriving at.

 

[AlbertoFerrari]

I would like to take a look at this, tried downloading the workbook but it does not work in Excel 2013, due to some conversion error, and it states the workbook is broken right after download. Can you please send me an email (alberto.ferrari@sqlbi.com) with the (working) workbook?
In any case, I would not be scared by filtering the fact table directly, at the end the engine is doing exactly this. I would like to test a couple of formulas, but I'd like to work on your example.

Thanks.

Alberto