Forum Discussion
How should data be structured when each data point belongs to multiple categories?
- 2 years ago
Hi lottie - You are definitely on the right track. You should convert the comma separated list into a Power Query list and then expand the values to new rows, like the example below. You'll need to do that for genres and countries. Then you will have multiple tables in your model: Movies, MoviesGenre, MoviesCountry. Movies will have a one to many relationship with MovieGenre and a one to many relationship with MovieCountry. Depending on the other tables in your model, you may or may not need to also add a Country dimension table and a Genre dimension table, which would relate to MovieCountry and MovieGenre respectively. You would then add a DAX measure to return the value needed. Please let me know if this gets you what you are needing or if you need additional assistanance.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s0vy0w1VNJRckwuyczPU4rVwRDTUXBMKUvNKyktSiUgraPgW1lcklpUqRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, Genre = _t]), #"Replaced Value" = Table.ReplaceValue(Source,each [Genre], each Text.Split ( [Genre], ", "),Replacer.ReplaceValue,{"Genre"}), #"Expanded Column1" = Table.ExpandListColumn(#"Replaced Value", "Genre") in #"Expanded Column1"Here is an example of a data model that has a similar requirement. This is a data model for calendar events where each event can have one or more associated contacts and one or more associated categories.
jennratten I have now managed to create the list and expand the rows into two new tables as you suggested. I have established the one to many connections and have been able to improve upon the bar chart from the original dashboard as you can see below.
Just curious though, in what scenario would I need bridging tables as per your example?
Original
Improved