Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Antmkjr
Post Patron
Post Patron

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)

 

AnuTomy_0-1654845064824.png

 

I have created a measure 

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

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

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Antmkjr 
Some times you need to CROSSFILTER the other relationship to None

1.png

 

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

2.png3.png

 

View solution in original post

5 REPLIES 5
v-yanjiang-msft
Community Support
Community Support

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
Super User
Super User

Hi @Antmkjr 
Some times you need to CROSSFILTER the other relationship to None

1.png

 

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

2.png3.png

 

johnt75
Super User
Super User

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.

Is it possible without using a bridge table

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

johnt75_0-1654853099692.png

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.