cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Combine data across multiple columns

Hello,

 

I have a flat file that I am working to shape into multiple tables in order to create a star schema. I have a few columns that use the same name and ID number system but they are split into separate columns based on admission and discharge (see below). I want to create a lookup/dimension table with DepartmentID and Unit name (don't ask me why it is called department for the ID and unit for the names... I have no idea!). I would then remove the unit name columns in the fact table and connect the dimension table ID to the fact table IDs in my data model.

 

cathoms_0-1603997023583.png

So, two questions:

1) can I use one dimension table for two different instances of dept/unit or should I just create two separate dimension tables? Essentially, can I link one primary key from DIM to two separate foreign keys in a fact table?

 

2) If the answer to 1 is yes, then is there a way I can get all the department IDs from AdmitDepartmentID and DischargeEpicDepartmentID into one column and all the unit names from AdmitUnit and DischargeUnit into another column? Ultimately the IDs are both EpicDepartmentID, just two temporally distinct instances.

 

Thanks!

1 ACCEPTED SOLUTION
Super User II
Super User II

1) You can create one dimension table (it's called a role-playing dimension).  You would create one active relationship to the fact table and one inactive relationship.  When you write a measure you state which relationship to use.

However, if the dimension table is relatively small, you can use 2 versions of the table (DimDischargeDept and DimAdmitDept) - the DAX will be simpler and you can put either or both slicers in a report.  You would use 2 active relationships.

 

2) Is this to create the Dimension table?  If so, you would do this in Power Query, just create a table from the Distinct values from the first 2 columns and then append the distinct values from the other 2 columns

View solution in original post

5 REPLIES 5
Regular Visitor

Dear all,

Please help me to transfer data like this:

ClassName1Name2Name3Name4Name5Score1Score2Score3Score4Score5
1A    6    
2B    7    
3CA   89   
4DABCA53437

Into:

ClassNameScore
1A6
2B7
3C8
3A9
4D5
4A3
4B4
4C3
4A7

 

Thanks,

Best regards,

@Zuy_NA   I think you should start a new thread to get your question answered.

 

Message me when you've done that and I'll help you out

Super User II
Super User II

1) You can create one dimension table (it's called a role-playing dimension).  You would create one active relationship to the fact table and one inactive relationship.  When you write a measure you state which relationship to use.

However, if the dimension table is relatively small, you can use 2 versions of the table (DimDischargeDept and DimAdmitDept) - the DAX will be simpler and you can put either or both slicers in a report.  You would use 2 active relationships.

 

2) Is this to create the Dimension table?  If so, you would do this in Power Query, just create a table from the Distinct values from the first 2 columns and then append the distinct values from the other 2 columns

View solution in original post

@HotChilli Thanks! Is it better to have a lot of small dimension tables each having only one active relationship with the main fact table or fewer, slightly larger dimension tables with multiple relationships to the fact table?

 

I should point out that for the example above the dimension table ends up with 2 columns and 72 rows. The largest dimension table that I built with the append method is 2 columns and 1527 rows. That one was built from 2 tables, one with 1118 and another with 1065 rows.

Well, there aren't many rows in these tables, so it makes it more attractive to use duplicate dimension tables. 

 

Make sure to remove duplicates from the (almost final) dimension tables

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors