Grouping of Data based on multiple values

[Navya]

Hi,

I am working on text data and need help with grouping rows. Data has 2 columns, below is an example data I am working on :

User	Function Name
User 1	Function 1
User 1	Function 2
User 2	Function 2
User 3	Function 1
User 3	Function 2
User 4	Function 1
User 4	Function 2
User 4	Function 3
User 5	Function 1

I want to group data based on Functions and count the number of unique users. But it is not a straight forward way. I need to find the number of unique users who use following function combinations :

1. Function 1 : 4 [User 1, User 3, User 4, User 5]

2. Function 1, Function 2 (should use both) : 3 [User 1, User 3, User 4]

3. Function 1, Function 2, Function 3 : 1 [User 4]

After the grouping, I need to visualize these values.

[Vijay_A_Verma]

Here is Power Query solution which is dynamic i.e. not dependent upon the number of functions.

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately.)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVIwVNJRcivNSy7JzM8DcmJ1sIkbIcSNcIgb4zDHGId6ExzqTYhRb4wQN0UzJxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, #"Function Name" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Function Name"}, {{"Temp", each Text.Combine([User],","), type nullable text}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.FirstN(#"Added Index"[Function Name],[Index])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Function Group", each Text.Combine([Custom],", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Function Name", "Custom"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "User List", each Text.Split([Temp],",")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Temp"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns1", "Custom", each List.Count(List.Accumulate(List.FirstN(#"Removed Columns1"[User List],[Index]),[User List],(s,c)=>List.Intersect({s,c})))),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom3",{"Index", "User List"})
in
    #"Removed Columns2"

[Fowmy · Accepted solution]

@Navya 

You need to create three measures as follows

Fn 1 = 
CALCULATE(
    DISTINCTCOUNT( Table6[User] ),
    Table6[Function Name] = "Function 1"
)
----------------------------------------------------
Fn 1 & 2 = 
COUNTROWS(
    FILTER(
        VALUES(Table6[User]) ,
        CALCULATE( 
            "Function 1" in VALUES( Table6[Function Name] )  &&  
            "Function 2" in VALUES( Table6[Function Name] )  
        )
    ) 
)

-------------------------------------------------------

COUNTROWS(
    FILTER(
        VALUES(Table6[User]) ,
         CALCULATE( 
            "Function 1" in VALUES( Table6[Function Name] )&&  
            "Function 2" in  VALUES( Table6[Function Name] ) &&
            "Function 3" in  VALUES( Table6[Function Name] )
        )
    ) 
)

[Navya]

Thank You !! This worked