How do we calculate ranks of table rows dynamically with ALLSELECTED?

[haimh]

Hello,

I have a model named "DAS Performance," which presents KPIs for each period (year-month) and each employee.

Per each period and employee role, I created a calculated column that ranks their performance compared to all employees in that period.

Look at the yellow marked columns they are important:

The code for the calculated rank is :

RANK_MONTHLY_CALLS = 
IF(
    'DAS Performance'[TOTAL_F2F_VIDEO_MONTHLY_CALLS] > 0 && 
    'DAS Performance'[GLOBAL_ROLE_CODE] = "Sales Representative",
    RANKX(
        FILTER(
            ALL('DAS Performance'),  -- This will consider only the filtered employees
            'DAS Performance'[MONTH_CALANDER_ID] = EARLIER('DAS Performance'[MONTH_CALANDER_ID]) &&  -- Filter by the same month
            'DAS Performance'[TOTAL_F2F_VIDEO_MONTHLY_CALLS] > 0 &&  -- Only include employees with total calls > 0
            'DAS Performance'[GLOBAL_ROLE_CODE] = "Sales Representative"  -- Only for Sales Representatives
        ),
        'DAS Performance'[TOTAL_F2F_VIDEO_MONTHLY_CALLS],  -- Rank based on the total calls
        ,
        DESC,  -- Sort in descending order (highest calls get rank 1)
        DENSE  -- Use dense ranking (no gaps in ranks)
    ),
    BLANK()  -- Return blank if conditions are not met
)

The column works great and I depend on this column in other measures.

The problem begins when I start using Team / DAS slicers (DAS = Employee)

If I filter the visual by slicer with a specific team, for example, the rank column is still calculated by all teams.

I tried to think about how to resolve this with ALLSELECTED but I faced 2 issues:

1) I work with row level, therefore there is no CALCULATE function in my rank.

2) I know I need an ALLSELECTED function but it requires CALCULATE in a measure, not a calculated column.

This code of course did not work:

RANK_MONTHLY_CALLS_MEASURE = 
IF(
    'DAS Performance'[TOTAL_F2F_VIDEO_MONTHLY_CALLS] > 0 && 
    'DAS Performance'[GLOBAL_ROLE_CODE] = "Sales Representative",
    RANKX(
        FILTER(
            ALLSELECTED('DAS Performance'),  -- This ensures we only rank based on the current filter context (like selected employees)
            'DAS Performance'[MONTH_CALANDER_ID] = MAX('DAS Performance'[MONTH_CALANDER_ID]) &&  -- Ensure ranking is done by month
            'DAS Performance'[TOTAL_F2F_VIDEO_MONTHLY_CALLS] > 0 &&  -- Only employees with positive calls
            'DAS Performance'[GLOBAL_ROLE_CODE] = "Sales Representative"  -- Ensure ranking is only for Sales Representatives
        ),
        'DAS Performance'[TOTAL_F2F_VIDEO_MONTHLY_CALLS],  -- Ranking based on total calls
        ,
        DESC,  -- Highest calls get rank 1
        DENSE  -- Use dense ranking (no gaps in ranks)
    ),
    BLANK()  -- Return blank for non-representatives or zero calls
)

How to resolve this issue then if I am based on a static table (model)

[haimh]

Many thanks! Worked!

[Dangar332]

Hi,  @haimh 

If it works, then accept answer as solution it helps others to finds solution.

[Dangar332 · Accepted solution]

Hi, @haimh 

You cann't compute Dynamic calculation in Column, In column once Logic/calculatio happen it can't modify as slicer selection so you have to go with Measure.

RANK_MONTHLY_CALLS_measure =
VAR curr_MONTH_CALANDER_ID =
    SELECTEDVALUE ( 'DAS Performance'[MONTH_CALANDER_ID] )
RETURN
    IF (
        'DAS Performance'[TOTAL_F2F_VIDEO_MONTHLY_CALLS] > 0
            && 'DAS Performance'[GLOBAL_ROLE_CODE] = "Sales Representative",
        RANKX (
            FILTER (
                ALLSELECTED ( 'DAS Performance' ),
                -- This will consider only the filtered employees
                'DAS Performance'[MONTH_CALANDER_ID] = curr_MONTH_CALANDER_ID
                    && -- Filter by the same month
                'DAS Performance'[TOTAL_F2F_VIDEO_MONTHLY_CALLS] > 0
                    && -- Only include employees with total calls > 0
                'DAS Performance'[GLOBAL_ROLE_CODE] = "Sales Representative" -- Only for Sales Representatives
            ),
            CALCULATE ( SUM ( 'DAS Performance'[TOTAL_F2F_VIDEO_MONTHLY_CALLS] ) ),
            ,
            DESC,
            -- Sort in descending order (highest calls get rank 1)
            DENSE -- Use dense ranking (no gaps in ranks)
        ),
        BLANK () -- Return blank if conditions are not met
    )

Best Regards,
Dangar

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