Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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 @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.
@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?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 @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.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |