topic Re: USERELATIONSHIP not working in DAX Commands and Tips

USERELATIONSHIP not working

Anonymous — Fri, 10 Jun 2022 07:32:36 GMT

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

Re: USERELATIONSHIP not working

johnt75 — Fri, 10 Jun 2022 09:15:32 GMT

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.

Re: USERELATIONSHIP not working

Anonymous — Fri, 10 Jun 2022 09:16:52 GMT

Is it possible without using a bridge table

Re: USERELATIONSHIP not working

johnt75 — Fri, 10 Jun 2022 09:25:45 GMT

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

Re: USERELATIONSHIP not working

tamerj1 — Fri, 10 Jun 2022 09:33:06 GMT

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

Re: USERELATIONSHIP not working

v-yanjiang-msft — Thu, 16 Jun 2022 06:53:23 GMT

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