Display the minimum employeeID based on the top ranking selected

[X19203659]

Hi Community member

I would need to get your help to create a measure to get the EmployeeID with the smaller “_Tiebreaker rank” in a card visual.

As you can see in the below slicer called “Top”, “Top 10” is selected.
Based on this selection, the employee id Q00960 should appear in the card.

Below are the different columns and measures that you can see on the above screenshot

Column:

EmployeeID column comes from the employee table

Measure

Measure 1

_MinEmployeeID is the below measure (Minx):

_MinemployeeIndex = MINX(Employee,Employee[EmployeeIndex])

Measure 2

8 _Select_Crt OnTime is the measure:

              8 _Select_Crt OnTime =

var MaxDate=  [MaxDate]

var MinDate=[MinDate]

var OnTimeTrip = COUNTX(filter(Duplicate_removed,

Duplicate_removed[is_point_tracked]=True

&& Duplicate_removed[mdc_driving_event_type]="Ontime"

&& Duplicate_removed[is_service_cancelled]<> true

)

, Duplicate_removed[UniqueTrip])

return

divide(OnTimeTrip,[0 _Select_Crt TrackedPoint])

Measure 3

EmployeeRank is the below measure:

EmployeeRank =

VAR BaseRank =

RANKX (

FILTER ( ALL ( Employee ), NOT ISBLANK ([8 _select_Crt OnTime] )),

 [8 _select_Crt OnTime],

, DESC, SKIP)

VAR TieBreakerRank =

                             RANKX (

FILTER ( ALL ( Employee ), NOT ISBLANK ([8 _select_Crt OnTime] )),

[_MinemployeeIndex],  -- Use EmployeeIndex directly, not SELECTEDVALUE

 ,  ASC,  SKIP)

VAR FinalRank =

IF (

ISBLANK([8 _select_Crt OnTime]),

BaseRank + 100000,  -- Push blank values to the bottom

BaseRank + (TieBreakerRank / 10000))

RETURN

FinalRank

Measure 4:

_TieBreakerRank =

 RANKX (

all(Employee),[EmployeeRank],

 , ASC, SKIP )

Many thanks in advance for your help!
Kind regards

[Anonymous]

Hi @X19203659 ,

You want the top 1 Employee ID displaying in the card visual, right?

If so, please try

Top 1 EmployeeID = 
CALCULATE(
    MAX(Employee[EmployeeID]),
    FILTER(ALL(Employee), [_TieBreakerRank] = 1)
)

Best Regards,
Stephen Tao

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