Forum Discussion

Ben81's avatar
Ben81
Icon for Helper I rankHelper I
4 years ago
Solved

Userrelationship- not working

Hi, hoping I can find help on here as I have tried everything I know and can't ge the thing working. 

I have a date table and a financial table, they have 3 relationships from the Date to Finanace table, so;

Date > Sale Date (1 to *) - Active relationship

Date > Val Date (1 to *) - Inactive

Date > Can Date (1 to *) - Inactive

I removed all other joins to these two tables in my model so they are totally isolated. 

I created a measure to do a sum of a column using the Date to Val Date relationship but instead it filters the column based on the default active relationship Date > Sale.  

z_test1 =
VAR sale=
CALCULATE (
SUM ( <Fin Table>[Amount] ),
FILTER (
<Fin Table>,
<Fin Table>[Type] IN { 2, 3 }
)
)
return
CALCULATE(sale,
USERELATIONSHIP ('Date'[Date], <Fin Table>[Val Date] )
)


Any help would be appreciated 

Thanks,
Ben

  • johnt75's avatar
    johnt75
    4 years ago

    Try

    Test measure = CALCULATE( SUM( 'Fin table'[Amount]), 
USERELATIONSHIP('Date'[Date], 'Fin table'[Val date]),
TREATAS( { 2, 3}, 'Fin Table'[Type])
)

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    Move the USERELATIONSHIP into the initial CALCULATE, then just return sale

    • Ben81's avatar
      Ben81
      Icon for Helper I rankHelper I
      4 years ago

      Already tried that and still won't work 

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        Try

        Test measure = CALCULATE( SUM( 'Fin table'[Amount]), 
USERELATIONSHIP('Date'[Date], 'Fin table'[Val date]),
TREATAS( { 2, 3}, 'Fin Table'[Type])
)