Build the correct relationship between tables

[torraro]

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 visiting, when they do it, how... There are 2 main tables (which are successfully related) and another one I have no clue how to relate correctly. 

First table (Query details):

Date	User	Resource
09-04-19 5:31	USER 1	Resource 1
09-04-19 5:31	USER 2	Resource 1
09-04-19 5:31	USER 2	Resource 2
09-04-19 5:31	USER 3	Resource 2

Second table (User table)

User	Name	Role
User 1	Pepe	Production Planner
User 2	Ann	Master Data
User 3	Rick	Demand Planner

These 2 tables are related 1 to many (User [User table]-User [Query Details])

Then, I have the last table, which has no unique primary key. In these table we have the information of the activities that have to be done, the Resources used for eack activity and the role responsible of the activity:

Third table (Activities):

ACTIVITIES	RESOURCE	ROLE
Activity 1	Resource 1	Production Planner
Activity 2	Resource 2	Production Planner
Activity 3	Resource 2	Master Data
Activity 4	Resource 4	Demand Planner

Now I have tried to relate ROLE with many to many relationship. The fact is that is impossible then to filter by activities or to get dashboards ans tables with the correct data. 

Any help will be amazing,

Many thanks

[v-lid-msft]

Hi @torraro ,

We can create a calculated table in activity table to create relation based on user column:

User = 
MAXX (
    FILTER (
        'User table',
        'User table'[Role] = EARLIER ( Activities[ROLE] )
            && 'User table'[User]
                IN SELECTCOLUMNS (
                    FILTER (
                        'Query Detail',
                        'Query Detail'[Resource] = EARLIER ( Activities[RESOURCE] )
                    ),
                    "User", [User]
                )
    ),
    [User]
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

BTW, pbix as attached.

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.

[torraro]

Hello @v-lid-msft ,

Thank you for your answer and time. I have tried to build your calculated table and It returns me an error in the EARLIER function: 

(I must say the tables on the post were a simplification of the ones I have) I now attach a pic where you will see the real ones

Table users is called 'dUser md', Query detail is 'Query detail [...]' and Activities is 'Table1'. 

In Query detail table I have so many columns that I can't show all.. but there's User ID and Workbook (Workbook is the resource)

Hope you can still help me, 

Many thanks!

[v-lid-msft]

Hi @torraro ,

Sorry for my mistake, we are creating a calculated column in Activity Table.

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.

[parry2k]

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

[torraro]

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]

Hi @torraro ,

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.

[torraro]

Hello@v-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,