Get data ready for Venn diagram from customer logins by channel

[Sullyvall2]

I want to use the Venn diagram by MAQ LLC visual to show the numbers of customers that log in during a time window (Google analytics data source). I have two channels: "mobile" and "desktop". The Venn will show the count of customers that logged in mobile only, desktop only and the intersection both mobile and desktop. Values should update as I move my date slider filter. My log in data is in this format:

A summary of this information can be seen in the Pivot table:

The Venn diagram by MAQ LLC requires the data to be in this format...

...where 1 represents use of that chaannel type

I've tried various combinations of CALCULATETABLE(), SUMMARIZE() AND ADDCOLUMNS() and INT() but to no avail. Any assistance welcome.

Many thanks

[Anonymous · Accepted solution]

Hi @Sullyvall2 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUcrNT8rJTAUyDA30jSz0jQyMjJVidSByKanF2SX5BRRLWqJKIltpbICQc8JnKqYkkqnOeEzFkDNEyLngM9QFj6Gu+DS64tHohifI3fHIeeCSiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [user_id = _t, device_category = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"user_id", type text}, {"device_category", type text}, {"date", type date}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[device_category]), "device_category", "user_id", List.Count),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Count", each [moblie]+[desktop]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [moblie],each if [moblie] > 0 then 1 else [moblie] ,Replacer.ReplaceValue,{"moblie"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [desktop],each if [desktop] > 0 then 1 else [desktop] ,Replacer.ReplaceValue,{"desktop"})
in
    #"Replaced Value1"

Best Regards

[Sullyvall2]

Thanks. I couldn't see the wood for the trees. It never dawned on me to do it in Power Query. I was focused on getting it in DAX. I had another case where the data was already pivoted so could use this dax to get the data in the shape needed for the Venn.

MPF_Venn = CALCULATETABLE(

                ADDCOLUMNS(

                    SELECTCOLUMNS('MyTable'

                    ,"event_date",'MyTable' [event_date]

                    , "sub_product", 'MyTable' [question_1]

                    , "question_2", 'MyTable' [question_2]

                    )

                ,"Product B", SWITCH([question_2],"Yes",1,0)

                ,"Product A1", SWITCH([question_1],"Just A1",1,"I need both",1,0)

                ,"Product A2", SWITCH([question_1],"Just A2",1,"I need both",1,0)

                )

            , 'MyTableWithOtherData' [form_product]="Products A or B"

            )

Thanks for the Power Query solution...much appreciated

[Sullyvall2]

Data as a table for assistance

user_id	device_category	date
A	moblie	28/10/2023
A	desktop	28/10/2023
A	desktop	28/10/2023
A	desktop	29/10/2023
A	moblie	30/10/2023
B	desktop	28/10/2023
B	desktop	29/10/2023
C	moblie	30/10/2023
C	moblie	31/10/2023
D	desktop	29/10/2023
D	moblie	30/10/2023
E	desktop	29/10/2023
E	moblie	30/10/2023
F	moblie	28/10/2023
G	moblie	28/10/2023
H	moblie	28/10/2023