Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Treemap slicer - multiple choice entry

Dear community,

 

I think I am missing something very easy here but cannot find my answer on the web.

I have the folowing table with each entry answering their gender, hair color and what do they eat/drink for breakfast.

 

User IDGenderHair colorDrink coffeeDrink teaDrink organge juiceEat porridgeEat cerealsEat bread
1FBrown1   1 
2FRed 11  1
3FBlond 1  1 
4MBrown11 1  
5MBrown1    1
6FBlond 11  1
7MBlond1    1
8FRed 1 1  
9MBrown1 1 1 

 

I have made a treemap from the answers of "What do you drink and eat for breakfast" hoping it would interact with the 2 other graphs I have on "Gender" and "Hair color". But it does not.

Should I change the format of my data set, or is there a setting I am missing? I have tried to "Edit interactions" in the Format tab but it does not work.

 

Many thanks for your help.

 

Marius

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , I think first you need to transform the power query like this

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIDYqei/PI8IA3iKyBhCD9WJ1rJCKoyKDUFSc4QRS1InTHMxJz8vBQUU9BNNAGyfNHsNsTQAVJpikWlghK6S0EqzXDYjulOc5iZUJW4zbTA6nNMV1ricCUqHRsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Gender = _t, #"Hair color" = _t, #"Drink coffee" = _t, #"Drink tea" = _t, #"Drink organge juice" = _t, #"Eat porridge" = _t, #"Eat cereals" = _t, #"Eat bread" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Gender", type text}, {"Hair color", type text}, {"Drink coffee", Int64.Type}, {"Drink tea", Int64.Type}, {"Drink organge juice", Int64.Type}, {"Eat porridge", Int64.Type}, {"Eat cereals", Int64.Type}, {"Eat bread", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"User ID", "Gender", "Hair color"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}})
in
    #"Changed Type1"

     

    Then you can use the data better for your need

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you very much for your reply AmitChandak.

      I am not advanced to be able to use these code lines. I have tried to copy paste your lines but there are errors (maybe the source of the table I guess).

      Could you just show me the result of the querry and I can see how the table is organised? Otherwise I would need a step by step guideline to use your querry lines, and I really don't want you to spend that much time on this 😉

      Thanks 

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    Is this the result you expect?

    Please check the attachment.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for your reply v-zhangti 

      The report you have can be filtered by Gender and Hair color. I think I came to the same result but I cannot filter by Breakfast item. What I mean is, if you click on "Drink Coffee", it will not show you how many women and men drink coffee or what are their Hair Color. The treemap does not interact with other graphs. If it does, let me know and I would be happy to know how you did it 😉

       

      Thanks again,

      Marius

      • v-zhangti's avatar
        v-zhangti
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, Anonymous 

         

        Because "Drink Coffee" is a column name, it cannot be an option for slicers. You can try the following methods to change the format of the table.

        Table:

        NewTable =
VAR table1 =
    SUMMARIZE (
        'Table',
        'Table'[User ID],
        'Table'[Gender],
        'Table'[Hair color],
        "Drink", IF ( SELECTEDVALUE ( 'Table'[Drink coffee] ) <> BLANK (), "coffee", BLANK () ),
        "Eat", IF ( SELECTEDVALUE ( 'Table'[Eat bread] ) <> BLANK (), "bread", BLANK () )
    )
VAR table2 =
    SUMMARIZE (
        'Table',
        'Table'[User ID],
        'Table'[Gender],
        'Table'[Hair color],
        "Drink",
            IF (
                SELECTEDVALUE ( 'Table'[Drink organge juice] ) <> BLANK (),
                "organge juice",
                BLANK ()
            ),
        "Eat", IF ( SELECTEDVALUE ( 'Table'[Eat cereals] ) <> BLANK (), "cereals", BLANK () )
    )
VAR table3 =
    SUMMARIZE (
        'Table',
        'Table'[User ID],
        'Table'[Gender],
        'Table'[Hair color],
        "Drink", IF ( SELECTEDVALUE ( 'Table'[Drink tea] ) <> BLANK (), "tea", BLANK () ),
        "Eat", IF ( SELECTEDVALUE ( 'Table'[Eat porridge] ) <> BLANK (), "porridge", BLANK () )
    )
RETURN
    UNION ( table1, table2, table3 )

        Measure:

        Drink Count = 
CALCULATE(COUNT(NewTable[Drink]),FILTER(ALL(NewTable),[Drink]=SELECTEDVALUE(NewTable[Drink])))
        Eat Count = 
CALCULATE(COUNT(NewTable[Drink]),FILTER(ALL(NewTable),[Eat]=SELECTEDVALUE(NewTable[Eat])))

        At this point in the chart, select "Coffee" can filter the data in the sector chart.

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.