USERELATIONSHIP not working

[Antmkjr]

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       FA      LC

156657	20	a
156657	29	a
156657	30	a
877879	50	a
877879	70	b

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

[v-yanjiang-msft]

Hi @Antmkjr ,

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

[tamerj1 · Accepted solution]

Hi @Antmkjr 
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]

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.

[Antmkjr]

Is it possible without using a bridge table

[johnt75]

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