Creating a column with PowerBI Dax for top 25 offices by count of office name

Question

I am attempting to create a column called IsTop25Office in PowerBI and DAX that adds an indicator column per row if the Office in that row is in the top 25 count by its name and adds a "Yes" next to it in that row if it is, and a "No" if it is not.

What I have is :

IsTop25Office = IF(
    RANKX(
        ALL('Table'[officeName]),  // Ensure ranking is done based on unique office names.
        CALCULATE(COUNTROWS('Table'), ALL('Table'[officeName])), // Count rows for each officeName across the entire 'Table' table.
        ,DESC, Dense
    ) <= 25, "Yes", "No"
)

Unfortunately when I do that, I get "Yes" for all rows regardless if the Office is the top 25 or not, and no "No"s. How can I fix this?

The motivation for this is to use this for dynamic filtering of the top 25 (or arbitrary n) offices.

The data with the column of interest called officeName is :

officeName
Charlie LawGroup
Ruth's Dining
Charlie LawGroup
Trolly Dentist
McGiver Warehouse

The expected output with the added to the data model with isTop25Office :

officeName         isTop25Office
Charlie LawGroup   Yes
Ruth's Dining      No
Charlie LawGroup   Yes
Trolly Dentist     Yes
McGiver Warehouse  No
...                ...               

In this example Charlie LawGroup and Trolly Dentist are each ranked in the top 25 or more of counts in the officeName column.

Answer 1

When you use the ALL('Table'[officeName]) in the RANKX you are telling DAX to rank across the distinct list of office names, it is OK in this part but the way you are pairing it with the count is the issue.How ?

Because of the filter context and how it works with CALCULATE and ALL.

The DAX ensures that each officeName is evaluated within the context of its occurrence across the entire dataset.

However, the combination of your ranking logic and count calculation is failing to make difference between office names accurately and that's why you are having YES for all rows as a result.

You can update your measure like below :

IsTop25Office = IF(
    RANKX(
        ALL('Table'),  // Use ALL('Table') to consider all rows in the context of the entire table
        CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[officeName])),  // Now here you count rows for each unique officeName, maintaining in the same time the officeName context
        ,DESC, Dense
    ) <= 25, "Yes", "No"
)

I tried it with a small dataset and I limited the Top25 to Top2 : Intial data :

Result :