Lookup table doubles as fact table

[Anonymous]

Hello - I have imported the Users table from Salesforce into Power Query. The Users table is a list of all the users in the system and their attributes, including the user ID of their manager. In order to find the name of the manager I have to take the manager's user ID and look it up against same Users table. I know that I could accomplish this by duplicating the Users table and creating a relationship between the manager ID and the User ID on copy #2 of the Users table. Is there a more elegant way of accomplishing this? This method requires duplicating data, which seems unnecessary and bad practice.

Thanks,

Ben

[Anonymous]

Hi @Anonymous ,

I am wondering if there is a way to create a relationship between 2 fields in the same table?

To my knowledge, it's not possible.

And as I mentioned before , in this case I would suggest you create a new column.

Best Regards,
Eyelyn Qin

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your description—— In order to find the name of the manager I have to take the manager's user ID and look it up against same Users table. 

It seems that userID , managerName are in the same table, so the data table may be like:

If you want a managerID column:

Column = LOOKUPVALUE('Table'[userID],'Table'[userName],[managerName])

Or you want a manager table:

Manager Table = ADDCOLUMNS( VALUES('Table'[managerName]),"managerID",LOOKUPVALUE('Table'[userID],'Table'[userName],[managerName]))

Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Evelyn,

My Users table looks like this, but has many more columns, including email, department, hire date, etc.

 

So in the case above, if I want to get Belinda's managers name, I need to take the manager ID and look it up in the ID column. I know that I can duplicate the Users table and create a relationship between Manager ID in the table above and user ID in the new copy, or create additional columns in the table per your suggestion. Both of those techniques require duplicating information that already exists though, so I am wondering if there is a way to create a relationship between 2 fields  in the same table? If not, what is the best practice here?

Thanks,

Ben

[jennratten]

If you are only trying to accomplish what is stated in your message, you can simply load the table to the data model, add a table visual to a report page with the necessary columns and add a slicer visual with the user ID field to the same report page.  Then you can filter the user table by user ID and view the results.  If you are needing to do something different, please explain.