Treemap slicer - multiple choice entry

[Anonymous]

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 ID	Gender	Hair color	Drink coffee	Drink tea	Drink organge juice	Eat porridge	Eat cereals	Eat bread
1	F	Brown	1				1
2	F	Red		1	1			1
3	F	Blond		1			1
4	M	Brown	1	1		1
5	M	Brown	1					1
6	F	Blond		1	1			1
7	M	Blond	1					1
8	F	Red		1		1
9	M	Brown	1		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

[v-zhangti]

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]

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]

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.

[amitchandak]

@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]

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