Forum Discussion
Combine data across multiple columns
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
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,