Forum Discussion
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 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
7 Replies
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?
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!
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.
Hi Anonymous ,
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.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!
Hi Anonymous ,
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.
