Forum Discussion

Lottie89's avatar
Lottie89
Frequent Visitor
2 years ago
Solved

How should data be structured when each data point belongs to multiple categories?

Hello all,   I imagine this is quite a basic question given the standard of users on here. I am in my first week of using power BI and although I have solved so many problems using google, I am not...
  • jennratten's avatar
    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.