topic Re: USERRELATIONSHIP gives wrong results in DAX Commands and Tips

USERRELATIONSHIP gives wrong results

Baerbel — Sat, 13 Jul 2024 20:26:50 GMT

Dear all,

I'm struggling again with my Dax Code (I'm a beginner). 😖 I've the following tables:

tblValue with the fields PR_ID, Value,
tblProject with the fields PR_ID, DC_ID, MC_ID (to be noted, that ech Project has a DC and a MC and that DC_ID and MC_ID can be the same, but can be different)
tblCustomer with the fileds DC_ID, MC_ID, Type (to be noted that the same customer can be for one Project a DC and for another project the MC, but always the Type od the customer (lets say public, private) remains the same)

Between the tables the following active connections exist: Table1[PR_ID] >>> Table2[PR_ID]
Table2[DC_ID] >>> Table3[DC_ID]

Additionally I have a non-active connetion between Table2[MC_ID] >>> Table3[MC_ID]

I want to calculate the Sum of the values from Table1 and display them in a visal for the different types defined in Table3. Here I've created two measures

Measure 1 for the values if the Customer is a DC (active connection):

CALCULATE (

SUM ( tblValue[Value] )

)

Measure 2 for the values if the Customer is a MC (non-active connection)

CALCULATE (

SUM ( tblValue[Value] )

USERRELATIONSHIP(tblProject[MC_ID], tblCustomer[MC_ID])

)

What I would expect is the following result

Project	DC private	DC public	MC private	MC public
PR1 (DC1 = MC1)	100		100
PR2 (DC2 = MC1)		200	200
PR3 (DC3 = MC4)		300		300

So the summ should always for Total DC and for Total MC be the same, but it is'nt - my second measure calcuates only all MC where DC = MC, if DC <> MC the values are not summed.

If I only activate one connection Table2[MC_ID] >>> Table3[MC_ID] it works perfect, but I really need to compare both types in one visual.

Who can help me to find the msitake in my code or my thinking; my be there will be a better solution?

Thanks a lot to everybody who thinks about my challenge - any ideas ar much appreciated.🤔

Bärbel

Re: USERRELATIONSHIP gives wrong results

Anonymous — Mon, 15 Jul 2024 06:48:33 GMT

Hi Baerbel ,

I am not able to reproduce this question, is it possible to provide relevant data about the data model so that I can answer it for you as soon as possible.

Best Regards,
Adamk Kong

Re: USERRELATIONSHIP gives wrong results

Baerbel — Mon, 15 Jul 2024 11:47:04 GMT

Dear Adamk,

thanks a lot for taking time to try to help me.
As I'm not alloud to share the original structure I tried to modify it. You can see the structure and the connections of the tables. Except the Calendar_table, the other DIM-tables are for other calculations in other visuals and not relevant for my calculation.

I'm trying to calculate the sum(value) from table1 for a certain perion of time (Months, yréar) and differentiated by C_Group and C_type (C_type is a part of the C_Group [hierarchy].

Table 2 consist of colums silimiar like this

PR_ID	DC:ID	MC_ID	Explanation
1	123	888	as the project is carried out as su-contractor, the direct customer (DC) differs from the main customer (MC)
2	432	432	project carried out as main contractor, so DC and MC are the same

Table 3 consists of 5 colums - similiar like this:

DC_ID	MC_ID	C_Name	C_Group	C_Type
123	789	Fox	public	energy
234	999	Fish	private	services
432	888	Bird	private	energy

I hope I could explain it a bit better. Thanks a lot in advance

Bärbel

Re: USERRELATIONSHIP gives wrong results

Baerbel — Mon, 22 Jul 2024 19:42:35 GMT

Dear all,

thanks to everybody who tried to think about the challenge - I now solved it by simply not repeating

CALCULATE (

SUM ( tblValue[Value] )

)

in the second (userrelationship) measure, but refering here to the first measure

CALCULATE (

[Measure1],

USERRELATIONSHIP(tblProject[MC_ID], tblCustomer[MC_ID])

)

Now it works - even if I still don't exactely understand why

All the best

Bärbel