Forum Discussion
Dimensions and Facts table
I have a big fact table that have multiple columns with the same type of data. From what I understand reading the star schema doc (https://docs.microsoft.com/en-us/power-bi/guidance/star-schema), I would need a dimension table for every column, but if that's the case my model is going to have multiple dimension tables with the same information, only changing the column relationship with the fact table because Power BI doesn't allow the same table column to have multiple relationships. There is another better way to define the dimension other than creating a table for every column? Below is an exemple of the dataset
| ID | Year | Value1 | Value2 | Value3 | Value4 | Value5 | Value6 |
| 1 | 2022 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2022 | 1 | 0 | 1 | 1 | 0 | |
| 3 | 2021 | 1 | 1 | 1 | 0 | ||
| 4 | 2021 | 1 | 0 | 0 | 0 | 1 |
The dimension for all of the Values columns would be 1 - yes, 2 - no, null - no response.
To create the views that I need, from my understanding of the star schema, a dimention table is required for these columns.
An example of view I want to create is a column bar chart grouped by year and Value3
For something this I think the best thing would be to unpivot those value columns in power query. Then you'd have something like:
ID Year ValueNumber Value 2 2022 1 1 2 2022 2 0 2 2022 3 1 2 2022 4 1 2 2022 5 2 2022 6 0 3 2021 1 1 Then you only need to join the dimension table to one column, and for measures/visual you could then filter by ValueNumber to see the different values for each.
2 Replies
For something this I think the best thing would be to unpivot those value columns in power query. Then you'd have something like:
ID Year ValueNumber Value 2 2022 1 1 2 2022 2 0 2 2022 3 1 2 2022 4 1 2 2022 5 2 2022 6 0 3 2021 1 1 Then you only need to join the dimension table to one column, and for measures/visual you could then filter by ValueNumber to see the different values for each.
Ideally you would unpivot the Values column. You then create dimensions for ID, Year and Value field
