Varying People Names on different tables

[swampiedonkey]

I have multiple tables pulled from multiple sources all referring to the same people.  The problem I am having is that the names of the people vary slightly due to nicknames or shortend names, ex. Mike and Michael, Ed and Eddie, Matt and Matthew.  I do have a key table that has all unique rows with all of the alternate possibilities of the names.  Unfortunately, I have no way of knowing which table uses which alternate, and sometimes they have combinations of multiple alternates.

I do have relationships setup, but as I said before, I cannot just point one table at a specific column in my key table, and have ALL of the names be correct.  

The end result I would like to see is the ability to create one slicer for the key table, and have it filter any table based off of one column from the key table.

[v-yuta-msft · Accepted solution]

Hi swampiedonkey,

What does "multiple alternated" mean? You said " I cannot just point one table at a specific column in my key table, and have ALL of the names be correct", what does this mean?

In addtion, basically, you should have unique values in your key table, and should have foreign columns which contains the same column with that column in your key table so that you can use the key column to filter other tables.

Regards,

Jimmy Tao

[swampiedonkey]

Thank you for the reply.  I am sorry about the typo of alternated, I meant alternate or nickname or alias.  What I mean by that is that in one table I may have Mike, Ed, and Matt.  Then in another, I will have Michael, Eddie and Matt.  Then in yet another, I will have Mike, Eddie, and Matthew.  But there are approximately 200 employees that need to be part of this filter with a very large number of various alias'.  I attempted creating new columns in the key table that the other tables could reference, but that did not work.

After talking it over with my wife who is also vaugly familiar with Power BI, and she essentially recommended what you have.  Add a calcualated column on each of the foreign tables that does a lookup on the key table, and assigns the value based off of a single column on the key table.  I have not had a chance to do this yet, but believe that it will work.