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
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,