Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

USERELATIONSHIP not working

I have a table NU with the below data :   AC No        SA   LC 156657 898 a 156657 -90 b 877879 213 c 877879 242 d   34 e   Table RR with the below data :   Cust   ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Some times you need to CROSSFILTER the other relationship to None

     

    FA cus = 
CALCULATE (
    SUM ( 'RR'[FA] ),
    USERELATIONSHIP ('RR'[Cust], 'NU'[AC No] ),
    CROSSFILTER ( 'RR'[LC], NU[LC], NONE )  
)

     

johnt75's avatar
johnt75
Super User
4 years ago

After using Power Query to replace the null values in NU[Ac no] with -1 I created a dimension table for all account & customer numbers with

Account numbers = DISTINCT( 
    UNION(
        ALLNOBLANKROW(NU[AC No]),
        ALLNOBLANKROW(RR[Cust])
    )
)

I then deleted the many-to-many relationship between the two existing tables and added 1-to-many relationships from the new Account Numbers table to both. One of these relationships has to be inactive due to the existing relationship between tables, it doesn't matter which one.

I then created a measure

FA by ac no = CALCULATE( SUM(RR[FA]), USERELATIONSHIP('Account numbers'[AC No], RR[Cust]))

put the 'Account numbers'[AC No] field into a table with the new measure and you get the correct results.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Is it possible without using a bridge table

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    I don't think so. I created a measure

    Num cust = COALESCE( CALCULATE( COUNTROWS(RR), USERELATIONSHIP(NU[AC No], RR[Cust])), 0)

    which gives very strange results

    I don't understand why the relationship is messed up, but cleaning up the data seems to fix the problem