Joining multiple columns from dimension table to fact table

[Anonymous]

Hi all,

Summary:

I am struggling with efficiently joining these two tables together.

I've merged Table one with Table two about 5 times within Power Query, and it's causing the dataset to time out when refreshing from the SQL database.

Example Data:

Table on the left (Table 1) is the report data, Table on the right (Table 2) is the User table.

Table 1 has around 30 columns with 5 of them (Displayed) linking to the User table.

Desired Result:

 

For my report I want to replace the ID's with the matching Names, and additionally be able to filter on the User's Classification for CreatedBy and ActionedBy.

Additional Information:

• The User table links off to another table for each User Type (i.e. There's a Client Table, Staff Table, etc.)
  • The relationship between Table 1 and Table 2 is built from those User Type tables:
    i.e. Table 1's ClientId links to the Client Table which then links to the User Table

Previously attempted to use measures to replace the values, but wasn't sure if that was efficient considering the amount of data.

Also want to be able to filter from the User table.

Appreciate any help on this!

[amitchandak]

@Anonymous , One of the ways is to have 5 copies of this table and join them in the model with each column.

Another way is to have inactive join and use userelationship

refer: https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

But I think you need the first option

[Anonymous]

So 5 copies of the User table, linking each one to the relevant column? 

Is that not the same load on the refresh since it's loading the User table 5 times for each row?