Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Build the correct relationship between tables

Hi all,    I have issues trying to relate a table which hasn't a unique primary key. The situation is the following:    I have the data of user's queries in a software: the resources they are vis...
Show and Tell
Tips and Tricks
Tutorial Requests
parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous you expained the problem very well but it is not very clear what is the underline issue and what you are trying to achieve?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi parry2k ,

 

The issue is that I can't relate the tables (with many to many relationship it doesn't work correctly) so I can not build the adequate dashboards with all data. 

 

Thanks!

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    We modify the formula using the new table name, please try to create a calculated column using this formula

     

    User = 
MAXX (
    FILTER (
        'dUser md',
        'dUser md'[Role] = EARLIER ( Table1[ROLE] )
            && 'dUser md'[User]
                IN SELECTCOLUMNS (
                    FILTER (
                        'Query detail [...]',
                        'Query detail [...]'[Resource] = EARLIER ( Table1[RESOURCE] )
                    ),
                    "User", [User]
                )
    ),
    [User]
)

     

    we also want to know will an activity be assigned wil multi user in considering the Resource and Role?

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hellov-lid-msft , parry2k,

       

      Sorry for being so insistent..

       

      Tha fact is that when I build the column you propose, the result is the following: 

       

       

      As you see, the activities are disaggregated in 4 levels. The Level 4 is the last activity. Each activity (level 4) has multimple resources (To complete an activity users must visit multiple resources, Workbooks). 

      As you also see, each activity has just one 'Main Business Process' which is the role of the users (Users have Main Business Process in User table). What happens is that multiple users can have the same 'Main Business Process', so when I calculate the column it returns just one user, so I lose the rest of users with same role.

       

      If I didn't explain myself tell me and I try to explain it again

       

      Thanks very much,