USERELATIONSHIP

[jaxdave]

I have three (relevant) tables: a dimension table of Current Employee Contracts (Dim_Contract) – each row is a contract and each contract has a unique contract id – a two-column dimension table (Dim_PrevContract) – each row is the contract id of the employee's current contract and the related contract of the employee's prior contract (if any) – and a fact table of Employee Compensation (Fact_Compensation) – each row lists the salary for each year of the contract.  

The relationships are as follows:

Dim_Contract		Fact_Compensation
Dim_Contract[ContractID]	1:*	Fact_Compensation[ContractID]
Dim_PrevContract		Fact_Compensation
Dim_PrevContract[PrevContractID]	1:* (inactive)	Fact_Compensation[ContractID]
Dim_Contract		Dim_PrevContract
Dim_Contract[ContractID]	1:1	Dim_PrevContract[ContractID]

I created the following measure [Actual Compensation] to calculate the compensation each employee earned under each contract.  The salary is calculated on a pro-rated basis – 1/nth for each week of the sales year that the employee remains under contract.  (For example, if the sales year is 24 weeks and the employee was under contract for 12 weeks, then an employee with a salary of 50,000 would have an Actual Compensation of 25,000 i.e., 50,000 / 24 * 12.  Different sales years may have a different number of weeks and each contract may only overlap a portion of a sales year.  The first part of the measure – “totalweeks” – retrieves the number of weeks in the sales year.  The second part of the measure – “earnedweeks” – calculates the number of overlapping weeks between the sales year and the contract. 

Actual Compensation = 
VAR _tbl = 
SUMMARIZE (
    ADDCOLUMNS (
        Fact_Compensation,
        "totalweeks", RELATED ( salescalendar[weeks] ),
        "earnedweeks", 
            VAR _contractstart = RELATED ( Dim_Contract[SignDate] )
VAR _contractend = IF ( ISBLANK ( RELATED ( Dim_Contract[ContractEndDate] ) ), TODAY(), RELATED ( Dim_Contract[ContractEndDate] ) )
            VAR _yearstart = RELATED ( salescalendar [seasonweekstart] ) 
            VAR _yearend = RELATED ( salescalendar [seasonweekend] )
            VAR _mintbl = 
            {   DATEDIFF ( _contractstart, _contractend, WEEK ), 
                DATEDIFF ( _seasonstart, _contractend, WEEK ), 
                DATEDIFF ( _contractstart, _seasonend, WEEK ), 
                DATEDIFF ( _seasonstart, _seasonend, WEEK ) 
            }
            RETURN
            MAX ( MINX ( _mintbl, [Value] ), 0 )
    ),
    SectionYearRep[ContractID],
    SectionYearRep[Season],
    [totalweeks],
    [earnedweeks]
)
RETURN
SUMX (
    _tbl,
    DIVIDE ( [Expected Compensation], [totalweeks] ) * [earnedweeks] 
)

The measure correctly calculates the Actual Compensation for current contracts but produces the wrong result when applied to the prior contract using the USERELATIONSHIP function i.e.,

Actual Compensation – Prior Contract = 
CALCULATE (
    [Actual Compensation],
    USERELATIONSHIP ( Fact_Compensation[ContractID], Dim_PrevContract[PrevContractID] )   
)

Based on the results, the variable _tbl is returning the table/values associate with the current contract.  A little research search suggests this is because CALCULATE, which is necessary for the  USERELATIONSHIP function removes the row context, but I am unsure how to fix/work around this.  I have a Calculated Column solution but would prefer to use a measure.  Any help is greatly appreciated.  For propietary reasons, I am unable to upload the file.  Thanks in advance. 

[v-jingzhang]

Hi @jaxdave 

I guess the problem may be in the following section. 

        "earnedweeks", 
            VAR _contractstart = RELATED ( Dim_Contract[SignDate] )
VAR _contractend = IF ( ISBLANK ( RELATED ( Dim_Contract[ContractEndDate] ) ), TODAY(), RELATED ( Dim_Contract[ContractEndDate] ) )

You may try modify the RELATED(Dim_Contract[xxxxx]) parts to use USERELATIONSHIP directly. For example,

var _contractstart =
CALCULATE (
    RELATED ( Dim_PrevContract[SignDate] ),
    USERELATIONSHIP ( Fact_Compensation[ContractID], Dim_PrevContract[PrevContractID] )
)

* Modify RELATED ( Dim_Contract[ContractEndDate] ) part similarly. I think here you should get SignDate and ContractEndDate from previous contracts table rather than the current contracts table. 

Hope it helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[parry2k]

@jaxdave it will be much easier to answer if you post sample data and the expected output.