Multiple copies of the same table

[Anonymous]

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.

[PC2790]

Hi @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

[Jimmy801 · Accepted solution]

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

[Vera_33]

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