Trying to get top 5 users with replaced profile images

[JimmyWork]

Hi

I'm trying to get the top 5 users that have the most assigned tickets to them in a helpdesk and display the profile picture.

The data source is a SharePoint list.

Everything works i can do count and rank, however some users does not have a profile picture and i want to display a general profile picture for this. 

Columns I used.

Assigned To.picture (This is the Web image URL for the sharepoint profile picture)

Assigned To.id (unique id for the user)

TicketID (All tickets)

User Ticket Count = COUNTROWS('Helpdesk')

 

To my issue i cant seem to rank using Assigned To.id but I can rank with Assigned To.picture

User Rank = 
VAR RankingTable =
    ADDCOLUMNS(
        ALL('Helpdesk'[Assigned To.id]),
        "Rank", RANKX(ALL('Helpdesk'[Assigned To.id]), [User Ticket Count], , DESC)
    )
VAR FilteredRankingTable =
    FILTER(RankingTable, [Rank] <= 5)
RETURN
    IF(
        RANKX(FilteredRankingTable, [User Ticket Count], , DESC) <= 5,
        RANKX(FilteredRankingTable, [User Ticket Count], , DESC),
        BLANK()
    )

My other real issue is that when i try to replace a user profile image by creating a new column I get the correct profile image however the ranking becomes wrong as all users that did not have a working profile image are now counted as one, this makes sense due to my ranking but how can I get this to work they way I want it to.

CustomProfileImage

= Table.AddColumn(#"Removed Columns", "CustomProfile", each if Text.EndsWith([Assigned To.picture], "jpg") then "https://contoso.com/pictures/user.png" else [Assigned To.picture])

[Anonymous · Accepted solution]

Hi @JimmyWork ,

For the ranking issue, you can try creating a new column that combines the and information.

NewColumn = Table.AddColumn(#"Previous Step", "CombinedIdPicture", each [Assigned To.id] & "|" & [Assigned To.picture])

CustomProfileImage = Table.AddColumn(#"Previous Step", "CustomProfile", each if Text.EndsWith([Assigned To.picture], "jpg") then [Assigned To.picture] else "https://contoso.com/pictures/user.png")

Best Regards,

Neeko Tang

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