Measure to generate employee rank within team

[Anonymous]

Hi guys,

So we're an organization that has about 200 or so different officers in teams processing applications. We're focused on improving processing speed, and I want to be able to write a measure that, when filtered by a slicer on employee and month, gives the rank of that employee in terms of processing speed within their team.

I have three tables in my data model: a list of applications, a list of officers (with their respective teams), and a calendar. The list of applications has:

Application Number	Days to Process	Officer Name	Date Processed
BKJ9087	56	Bob Andrews	July 1, 2019
BKJ9089	24	Bob Andrews	July 28, 2019
BKJ9090	101	Shelly Thomson	July 4, 2019
BKJ9091	77	Kelle White	July 17, 2019
BKJ9092	34	Nav Gunaratna	July 8, 2019
BKJ9093	33	Shelly Thomson	June 24, 2019

List of officers:

Officer Name	Team Name
Bob Andrews	Team ABC
Shelly Thomson	Team ABC
Nav Gunaratna	Team ABC
Kelle White	Team XYZ

"Date Processed" on the list of applications is linked to the calendar, and the two "Officer Name" fields are linked together.

In one of my reports in the Report View, I have two slicers on a page: one that filters everything to just "Bob Andrews" (using the field "Officer Name" from the list of officers) and one that filters everything to just "Jul-2019" (using the field "ShrtMnth" from the calendar).

I'd like to be able to write a measure that I can put in a card to return me the rank of Bob Andrews within his team (Team ABC) in terms of percentage of his applications that were completed in 35 days or less (of those completed in July 2019). Could anyone advise me on the best way to do this? I've been playing around with "RANKX", but I've only been able to get something that returns a ranked list of all employees in a table (and isn't limited to just the members of the team):

RANKX(ALL('Staff List'[Employee Name]),COUNTX(RELATEDTABLE(Applications),CALCULATE(COUNTROWS(Applications),Applications[Days to Process]<=35))/COUNTX(RELATEDTABLE(Applications),CALCULATE(COUNTROWS(Applications))))

How do I get it to return a single value and to limit it to just members of the relevant team?

[v-lid-msft]

Hi @Anonymous ,

We can try to create following measure to meet your requirement:

Measure =
VAR tname =
    CALCULATE ( MAX ( 'Staff List'[Team Name] ) )
VAR v =
    CALCULATE (
        SUM ( 'Applications'[Days to Process] ),
        'Applications'[Days to Process] <= 35
    )
VAR t =
    CALCULATETABLE (
        'Staff List',
        REMOVEFILTERS ( 'Staff List'[Officer Name] ),
        'Staff List'[Team Name] = tname
    )
VAR t2 =
    FILTER (
        CALCULATETABLE (
            'Applications',
            REMOVEFILTERS ( 'Applications'[Officer Name] ),
            REMOVEFILTERS ( 'Staff List'[Officer Name] )
        ),
        [Days to Process] <= 35
            && [Officer Name] IN SELECTCOLUMNS ( t, "name", [Officer Name] )
    )
VAR i =
    RANKX ( t2, [Days to Process], v, ASC )
RETURN
    i / COUNTROWS ( t2 )

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

Best regards,

[Anonymous]

Hi @v-lid-msft ,

Thanks very much for your help! I think this is on the way, but the number that should be returned for "Bob Andrews" is "2". His performance at meeting the 35 day target in July is 50% (1 of his 2 applications was processed in 35 days or less). His team is Team ABC, and so he has two other team members, Shelly Thomson and Nav Gunaratna. Shelly Thomson's performance was 0% (her application took 101 days) and Nav Gunaratna's performance was 100% (his application took 34 days).

Therefore the Team ABC ranking is like this:

Rank	Name of Team ABC person	Performance in July
1	Nav Gunaratna	100%
2	Bob Andrews	50%
3	Shelly Thomson	0%

And so, with the filters "Bob Andrews" and "July" that you show, the measure should return "2", Bob Andrews's ranking in July among the members of his team.

[v-lid-msft]

Hi @Anonymous ,

We can try to create a measure use following formula to meet your requirement:

Rank =
VAR tname =
    CALCULATE ( MAX ( 'Staff List'[Team Name] ) )
VAR t1 =
    FILTER (
        CALCULATETABLE (
            'Applications',
            REMOVEFILTERS ( 'Applications'[Officer Name] ),
            REMOVEFILTERS ( 'Staff List'[Officer Name] )
        ),
        [Officer Name]
            IN SELECTCOLUMNS (
                CALCULATETABLE (
                    'Staff List',
                    REMOVEFILTERS ( 'Staff List'[Officer Name] ),
                    'Staff List'[Team Name] = tname
                ),
                "name", [Officer Name]
            )
    )
VAR t2 =
    ADDCOLUMNS (
        GROUPBY ( t1, Applications[Officer Name] ),
        "Performance",
        VAR n = [Officer Name]
        RETURN
            COUNTROWS ( FILTER ( t1, [Days to Process] <= 35 && [Officer Name] = n ) )
                / COUNTROWS ( FILTER ( t1, [Officer Name] = n ) )
    )
RETURN
    MAXX (
        FILTER (
            ADDCOLUMNS ( t2, "Rank", RANKX ( t2, [Performance],, DESC, DENSE ) ),
            [Officer Name] IN FILTERS ( 'Staff List'[Officer Name] )
        ),
        [Rank]
    )

Best regards,

[Anonymous]

Hi @v-lid-msft ,

Thanks very much! This seems like exactly what I need. Only issue is that when I transpose the formula into my file and drag the measure into a card "SQLDUMPER.EXE" opens temporarily in a command prompt window and then I get this error back:

"Couldn't load the data for this visual"

"An unexpected error occurred (file 'xmvsquerry.cpp', line 3174, function 'XMVSColumn::Bind')."

[v-lid-msft]

Hi @Anonymous ,

Could you please share about what kind of data source and connective mode you are using? Do you have a very large dataset? Will you occor this error when open the sample pbix file in my previous post? Have you tried to use the latest version Power BI Desktop?

Please don't have any Confidential Information or Real data in your reply.

Best regards,

[Anonymous]

Hi @v-lid-msft ,

It's strange because I'm just using Excel files and your PBIX works fine on my system. I'm using Version: 2.75.5649.861 64-bit (November 2019). Dataset is 175,000 rows but filtering it down to 33,000 or so doesn't seem to make a difference.

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following measure to fix this isuse:

Rank = 
VAR tname =
    CALCULATE ( MAX ( 'Staff List'[Team Name] ) )
VAR t1 =
    FILTER (
        CALCULATETABLE (
            'Applications',
            REMOVEFILTERS ( 'Applications'[Officer Name] ),
            REMOVEFILTERS ( 'Staff List'[Officer Name] )
        ),
        [Officer Name]
            IN SELECTCOLUMNS (
                CALCULATETABLE (
                    'Staff List',
                    REMOVEFILTERS ( 'Staff List'[Officer Name] ),
                    'Staff List'[Team Name] = tname
                ),
                "name", [Officer Name]
            )
    )
VAR t2 =
    ADDCOLUMNS (
        GROUPBY ( t1, Applications[Officer Name] ),
        "Performance",
        VAR n = [Officer Name]
        RETURN
            DIVIDE(COUNTROWS ( FILTER ( t1, [Days to Process] <= 35 && [Officer Name] = n ) ),
                 COUNTROWS ( FILTER ( t1, [Officer Name] = n ) ),0)
    )
RETURN
    MAXX (
        FILTER (
            ADDCOLUMNS ( t2, "Rank", RANKX ( t2, [Performance],, DESC, DENSE ) ),
            [Officer Name] IN FILTERS ( 'Staff List'[Officer Name] )
        ),
        [Rank]
    )

Best regards,

[Anonymous]

Hi @v-lid-msft ,

That's working great now, thanks Dong. Just one last (hopefully!) weird issue. 

In my actual data, I'm using a number, "Employee ID" (another column of the "Staff List") to identify the staff and link the tables ( because the way names are written don't match up exactly between Applications and Staff List, and an equivalent of "Employee ID" exists in the 'Applications' table). However, when I filter and display data, I want to be able to filter it on the basis of "Officer Name" from the "Staff List".

I thought that this would be straightforward given that there is a 1-to-1 correspondence between the Employee IDs and the names in the Staff List. But when I use "Full Name" rather than "Employee ID", the rankings all come back as "1". 

Whereas what I'm after is this (I got the below by making a measure: Full Name = SELECTEDVALUE('Staff List'[Full Name]), but obviously I can't filter using that):

Do you know what could be causing this? There's no duplicates in Staff List and every Officer Name/Full Name has a unique Employee ID.

My data model, for clarity:

Thanks again for all your help!

[parry2k]

@Anonymous there is great blog post on RANK function, try that or share pbix file with expected result and I will look into it.ra

[Ashish_Mathur]

Hi,

Share the link from where i can download the PBI file.