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

156657898a
156657-90b
877879213c
877879242d
 34e

 

Table RR with the below data :

 

Cust       FA      LC

15665720a
15665729a
15665730a
87787950a
87787970b

 

The relationship is as shown : (Many to many inactive relationship btw Ac no and Cust)

 

 

I have created a measure 

FA cus = CALCULATE(SUM('RR'[FA]),USERELATIONSHIP('RR'[Cust],'NU'[AC No]))
to activate the inactive relationship
 

Now i want AC no against FA cus

But the expected value is 79 and im getting 199, seems like the inactive relationship is not turned on? what is the issue?

 

File attached:

 

https://drive.google.com/file/d/1vK3tM1YEFigmzzrln66jA0rzWW01ZzWu/view?usp=sharing

 

 

  • 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 )  
)

     

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

    Best Regards,
    Community Support Team _ kalyj