Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
matheus
New Member

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

 

IDYearValue1Value2Value3Value4Value5Value6
12022111111
220221011 0
32021111  0
4202110001 

 

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

 
1 ACCEPTED SOLUTION
AntonioM
Solution Sage
Solution Sage

For something this I think the best thing would be to unpivot those value columns in power query. Then you'd have something like:

 

IDYearValueNumberValue
2202211
2202220
2202231
2202241
220225 
2202260
3202111

 

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.

View solution in original post

2 REPLIES 2
PaulDBrown
Community Champion
Community Champion

Ideally you would unpivot the Values column. You then create dimensions for ID, Year and Value field Unpivot.gif

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






AntonioM
Solution Sage
Solution Sage

For something this I think the best thing would be to unpivot those value columns in power query. Then you'd have something like:

 

IDYearValueNumberValue
2202211
2202220
2202231
2202241
220225 
2202260
3202111

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.