USERRELATIONSHIP gives wrong results

[Baerbel]

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

[Baerbel · Accepted solution]

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

[Baerbel]

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

[Anonymous]

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