Forum Discussion

Baerbel's avatar
Baerbel
Helper I
2 years ago
Solved

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
 
ProjectDC privateDC publicMC privateMC public
PR1 (DC1 = MC1)100 100 
PR2 (DC2 = MC1) 200200 
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's avatar
    Baerbel
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Baerbel's avatar
    Baerbel
    Helper I
    2 years ago

    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_IDDC:IDMC_IDExplanation
    1123888as the project is carried out as su-contractor, the direct customer (DC) differs from the main customer (MC)
    2432432project carried out as main contractor, so DC and MC are the same

     

    Table 3 consists of 5 colums - similiar like this:

    DC_IDMC_IDC_NameC_GroupC_Type
    123789Foxpublicenergy
    234999Fishprivateservices
    432888Birdprivateenergy

     

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

     

    Bärbel

  • Baerbel's avatar
    Baerbel
    Helper I
    2 years ago

    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