Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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" |
101 | a1 | S-51 |
101 | a2 | S-51 |
101 | a3 | S-51 |
101 | a2 | S-51 |
101 | a5 | S-51 |
101 | a1 | S-51 |
101 | a2 | S-51 |
201 | a1 | S-9 |
201 | a3 | S-9 |
201 | a3 | S-9 |
201 | a4 | S-9 |
201 | a5 | S-9 |
201 | a5 | S-9 |
201 | a1 | S-9 |
301 | a1 | S-15 |
301 | a1 | S-15 |
301 | a5 | S-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" |
101 | S-51 | [a2,a1,a3] |
201 | S-9 | [a1,a3,a5] |
301 | S-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?
Solved! Go to Solution.
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.
HI @Anonymous ,
Solution provided in this post
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)
HI @Anonymous ,
Solution provided in this post
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)
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 , You can use concatenatex
https://docs.microsoft.com/en-us/dax/concatenatex-function-dax
You can use this with Top N
For Rank Refer these links
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
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 :
Now I am not sure how to pick topN rows for each UserID and even
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |