Forum Discussion
Multiple copies of the same table
Hi all,
Simple question from a simple user. We have a table in our database that I'll call Users, which has the User Id and their name:
We then use this table across multiple entries elsewhere in the dataset, for example a simple Transactions log:
I wish to display a report that shows the name of the User instead of their ID. Sinply create a link from one table to the other on the appropriate field.
My question is: Do I have to create multiple copies of the User table and then link the ID to the ID fields in the second table (i.e. Entered By, Authorised By and Paid By)? I don't think I can link it to the same table twice, nor do I think I can link it across other tables (Account created By etc).
From a data load perspective, is this the optimum solution? I ask because in reality the 380 Users in our organisation reside within a Person table (Applicants, Contacts, Users etc), which has about 150,000 rows that we then filter down to select only the entries with the role of User (whihc is also stored in a different table, so we're bringing 300,000 rows in to select 380). To create 5-6 versions of the same table seems excessive and a waste of resources.
Hello Anonymous
no, it's not possible to create 2 relationships between two tables. Or you normalize you TA-table and then create the relationship or you can create a new column in your TA-table with the function LOOKUPVALUE
SoldByName = LOOKUPVALUE(User[Name],User[ID],TA[Sold by])
Another solution is to connect them 2 or 3 times (one times active and the other inactive)and then create a measure for each name like
CanceledBYName = if (Calculate(COUNTROWS(User),USERELATIONSHIP(User[ID],TA[Canceled by]))>1, blank(),calculate(values(User[Name]),USERELATIONSHIP(User[ID],TA[Canceled by])))now use the custom measures in your visual
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
3 Replies
Hello Anonymous
no, it's not possible to create 2 relationships between two tables. Or you normalize you TA-table and then create the relationship or you can create a new column in your TA-table with the function LOOKUPVALUE
SoldByName = LOOKUPVALUE(User[Name],User[ID],TA[Sold by])
Another solution is to connect them 2 or 3 times (one times active and the other inactive)and then create a measure for each name like
CanceledBYName = if (Calculate(COUNTROWS(User),USERELATIONSHIP(User[ID],TA[Canceled by]))>1, blank(),calculate(values(User[Name]),USERELATIONSHIP(User[ID],TA[Canceled by])))now use the custom measures in your visual
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi Anonymous ,
There may be multiple ways to do it, however the first approach that comes to my mind is by using DAX- LOOKUPVALUE
If there is no such specific requirement of doing it in Power Query, you can go for DAX as no relationship or duplicate tables will be required in this case.
You can add two new columns and write the formula in such a way that it gets Entered By, Authorised By and Paid By details from the above table.
Create calculated columns using the DAX given below:
Paid by(Name) = LOOKUPVALUE(UserTable[Name],UserTable[UserID],TransactionTable[Paid By]) Entedred by(Name) = LOOKUPVALUE(UserTable[Name],UserTable[UserID],TransactionTable[Entered BY]) Authorised by(Name) = LOOKUPVALUE(UserTable[Name],UserTable[UserID],TransactionTable[Authorised by])Thanks
Hi Anonymous
How about transforming your table to this? Then create a relationship:
Transaction ID Date Action User 1 01/01/2021 Entered 3001 2 02/01/2021 Entered 3002 3 03/01/2021 Entered 3003 1 01/01/2021 Authorised 3006 2 02/01/2021 Authorised 3006 3 03/01/2021 Authorised 3006 1 01/01/2021 Paid 3004 2 02/01/2021 Paid 3004 3 03/01/2021 Paid 3005
