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.
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.