Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to show top-2 Groups for within a single row for each userId

Hi All, I am new to PowerBI and BI work.

 

Can you help me to understand how can I achive this output in powerBI.

 

Sample Data :

 

"UserID"     "IncludedGroup"       "Specialty"
101a1S-51
101a2S-51
101a3S-51
101a2S-51
101a5S-51
101a1S-51
101a2S-51
201a1S-9
201a3S-9
201a3S-9
201a4S-9
201a5S-9
201a5S-9
201a1S-9
301a1S-15
301a1S-15
301a5S-15

 

 

I am trying to get below output where each user have single Specialty, and need to populate top-3 of his groups within a single list like below :

"UserID"     "Specialty"     "Top-3-IncludedGroups"
101S-51[a2,a1,a3]
201S-9[a1,a3,a5]
301S-15[a1,a5]

Here for UserID-101 is included 3-times in a2 group, 2-times in both a1 and a3. so that's the order.

 

I know Rankx function, but not able to get only top-3.

Can someone help me here? 

2 ACCEPTED SOLUTIONS
dax
Community Support
Community Support

Hi @Anonymous , 

You could try to use M code to add an index column, then use this in measure. You could refer to my sample for details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRSgQRwbqmhkqxOnAxIyxixkSqM8UiRtAOI2R1lshCxkQJmWAKmRIlhGyjMbKQoSkhMVNkMYxAigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"UserID   " = _t, #"  ""IncludedGroup""    " = _t, #"   ""Specialty""" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID   ", Int64.Type}, {"  ""IncludedGroup""    ", type text}, {"   ""Specialty""", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"  ""IncludedGroup""    ", "IncludedGroup"}, {"   ""Specialty""", "Specialty"}, {"UserID   ", "UserID"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Specialty", "UserID", "IncludedGroup"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{ {"Specialty", Order.Ascending},{"Count", Order.Descending}}),
    #"Grouped Rows1" = Table.Group(#"Sorted Rows", {"Specialty"}, {{"all", each _, type table [Specialty=text, IncludedGroup=text, Count=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([all],"index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"UserID", "IncludedGroup", "index"}, {"UserID", "IncludedGroup", "index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"all"})
in
    #"Removed Columns"

Best Regards,
Zoe Zhi

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

View solution in original post

harshnathani
Community Champion
Community Champion

HI @Anonymous ,

 

Solution provided in this post

 

https://community.powerbi.com/t5/Desktop/Filter-top-3-rows-for-each-userID-in-PowerBI/m-p/1177509#M530280

 

I think  this is the similar post as earlier.

 

Regards,

Harsh Nathani


Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

4 REPLIES 4
harshnathani
Community Champion
Community Champion

HI @Anonymous ,

 

Solution provided in this post

 

https://community.powerbi.com/t5/Desktop/Filter-top-3-rows-for-each-userID-in-PowerBI/m-p/1177509#M530280

 

I think  this is the similar post as earlier.

 

Regards,

Harsh Nathani


Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

dax
Community Support
Community Support

Hi @Anonymous , 

You could try to use M code to add an index column, then use this in measure. You could refer to my sample for details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRSgQRwbqmhkqxOnAxIyxixkSqM8UiRtAOI2R1lshCxkQJmWAKmRIlhGyjMbKQoSkhMVNkMYxAigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"UserID   " = _t, #"  ""IncludedGroup""    " = _t, #"   ""Specialty""" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID   ", Int64.Type}, {"  ""IncludedGroup""    ", type text}, {"   ""Specialty""", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"  ""IncludedGroup""    ", "IncludedGroup"}, {"   ""Specialty""", "Specialty"}, {"UserID   ", "UserID"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Specialty", "UserID", "IncludedGroup"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{ {"Specialty", Order.Ascending},{"Count", Order.Descending}}),
    #"Grouped Rows1" = Table.Group(#"Sorted Rows", {"Specialty"}, {{"all", each _, type table [Specialty=text, IncludedGroup=text, Count=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([all],"index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"UserID", "IncludedGroup", "index"}, {"UserID", "IncludedGroup", "index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"all"})
in
    #"Removed Columns"

Best Regards,
Zoe Zhi

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

Anonymous
Not applicable

Can you please provide me with example or sequence of functions to apply?

 

Till now I have created a summary table and ranked it with filter with "ranking = RANKX(FILTER(summary,summary[UserID]=EARLIER(summary[UserID])),summary[countof],,DESC,Dense)" 

Screenshot : 

skondi_0-1592909048430.png

Now I am not sure how to pick topN rows for each UserID and even 

CONCATENATEX combining all rows of other UserId instead of its own rows.
Can you help me out?
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.