Dimension Table when same information exists in multiple columns

[FRCKJ]

Hi

I've just joined the community so apologies if this isn't the right way to ask a question

I'm designing a Power BI report and have two fact tables, one that has a column for staff members and the other has three columns for staff members- each for different reasons.

In the table with 3 staff member columns (for context the data is a list of completed recruitment candidates) one of the 3 columns shows which staff member sourced the candidate, one column shows who completed the recruitment and the other shows who owned that job. That owner column is not essential for the analysis (so I may consider removing that altogether) but the other two are essential and often not the same staff member.

I'm looking to create a Staff Dimension table that will cover these two columns, and the one column in my first fact table all in one

Is this possible or do I need multiple staff dimesnion tables

Thanks for any help!

[Peter_Beck · Accepted solution]

Hi -

If I understand you correctly, you will need to have two staff "dimension" tables. The reason is that only one relationship can be active at a time from the dimension table:

Note the dotted line for one of the relationships - this means it exists, but is not active, you can only have one active relationship at a time.

By creating two dimensions, you can have one active relationship for each field in your fact:

Hope this helps -

Peter

[FRCKJ]

Hi Peter

That makes sense, and would solve the problem. I can now move forward with the build!

Thank you!