Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
Solved! Go to Solution.
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
Proud to be a Super User!
Paul on Linkedin.
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!