Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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
Solved! Go to 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 )
)
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
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 )
)
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
I don't understand why the relationship is messed up, but cleaning up the data seems to fix the problem
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
9 |
User | Count |
---|---|
30 | |
26 | |
15 | |
13 | |
10 |