Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
FRCKJ
New Member

Dimension Table when same information exists in multiple columns

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!

 

1 ACCEPTED SOLUTION
Peter_Beck
Resolver II
Resolver II

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:

 

ScreenHunter_120 Mar. 20 19.16.jpg

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:

 

ScreenHunter_122 Mar. 20 19.18.jpg

 

Hope this helps -

 

Peter

View solution in original post

2 REPLIES 2
Peter_Beck
Resolver II
Resolver II

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:

 

ScreenHunter_120 Mar. 20 19.16.jpg

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:

 

ScreenHunter_122 Mar. 20 19.18.jpg

 

Hope this helps -

 

Peter

Hi Peter

 

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

 

Thank you!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.