Combine data across multiple columns

[cathoms]

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.

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!

[Zuy_NA]

Dear all,

Please help me to transfer data like this:

Class	Name1	Name2	Name3	Name4	Name5	Score1	Score2	Score3	Score4	Score5
1	A					6
2	B					7
3	C	A				8	9
4	D	A	B	C	A	5	3	4	3	7

Into:

Class	Name	Score
1	A	6
2	B	7
3	C	8
3	A	9
4	D	5
4	A	3
4	B	4
4	C	3
4	A	7

Thanks,

Best regards,

[HotChilli]

@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

[HotChilli · Accepted solution]

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

[JuliaMcLaughlin]

Can you explain how to do this in Power Query? I am very new to Power BI!

I am trying to address a similar issue where I have multiple columns of data per row that I could ideally report on as if they all fell within the same column. Each row is a donor and subsequent columns include the Date and Amount of each of their gifts. I'd like to make a matrix that can show me a sum total of all their gifts by month. 

[HotChilli]

@JuliaMcLaughlin it's a very old thread  Please post a new question with sample data and desired output.

If you mention me in the new post I may be able to help but someone will help.

[cathoms]

@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.

[HotChilli]

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