cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## USERRELATIONSHIP gives wrong results

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

1 ACCEPTED SOLUTION
Helper I

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
3 REPLIES 3
Helper I

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
Helper I

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

Community Support

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,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.