Forum Discussion

Navya's avatar
Navya
Frequent Visitor
4 years ago
Solved

Grouping of Data based on multiple values

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 :

 

UserFunction Name
User 1Function 1
User 1Function 2
User 2 Function 2
User 3Function 1
User 3Function 2
User 4Function 1
User 4Function 2
User 4Function 3
User 5Function 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.

Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

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

     



3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

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

     



  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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"