Linking Two Dimension Table having Primary Key

[peterpan]

I have two dimension table, project table with user id and user name table. They are connected to fact table where current projects are listed. I want to know which project is assigned to user name. I can't use crossfilter or bidirectional filter since fact table only has projects currently going on. Any idea on how to get this result?

[danextian]

Hi @peterpan,

It would be easier for us to understand what you're tryint to achieve if you posted a sample data along with the sample result from that data. Please make it workable, something we can copy paste or better yet a post a link to an Excel file stored in the cloud.

[peterpan]

Here's a sample data attached for your reference https://drive.google.com/file/d/1_HEtVC6u6BFFJglrziWrH31XdFC3Bswb/view?usp=sharing . The result I'm trying to get is - 

Project ID	User Name
884	John
885	Martin
886	Bob

[danextian · Accepted solution]

It appears that the primary and forein keys are all User ID so why not create just a single dimensions table for simplicity's sake? But if you really want them seprate and still want Username in Projects table, you can use

User Name =
LOOKUPVALUE ( User[User Name], User[User ID], Project[User ID] )

or do  a merge in power query.

[peterpan]

Thank you so much!