Top N Filter when drilling through hierarchy

Is there any way to maintain a “Top N” filter so that when drilling through the hierarchy, you only see the top N number of rows? In the attached example, I have a matrix with County and the user can drill down to city. I set a filter to top 5 counties, but when a user drills down to city, I would like them to see the top 5 cities. Applying a top 5 filter to city doesn’t work because then only the 5 cities are included when viewing by county.

The reason why I want this functionality is to limit the amount of rows in a table so that a very long list of rows doesn’t take a long time to load.

Free training Power BI Demo.pbix (506.1 KB)

Hi @Eric,

Thanks for providing a sample file :+1:
Can you see if this works for you.

Top5 By Country/City = 
VAR TopFiltered =
    CALCULATETABLE(
        GENERATE(
            VALUES( Location[County] ),
            TOPN(
                5,
                CALCULATETABLE( VALUES ( Location[City] ) ),
                [Total Profits]
            )
        ),
        ALLSELECTED()
    )
RETURN

IF( NOT( ISINSCOPE( Location[City] )), [Total Profits],
    CALCULATE( [Total Profits],
        KEEPFILTERS ( TopFiltered )
    )
) 

Here’s your sample file. eDNA - Top5 Country by City.pbix (502.8 KB)
I hope this is helpful.

1 Like

Wow that’s brilliant! Thank you!

@Melissa If I could ask a follow-up question, what if I wanted to add another level to the hierarchy, such as customer name, and also limit that to the top 5 customers? In the attached workbook, I added customer name under city. Is that also possible?

I’m assuming that again you don’t want filtering on the City aggregate level for only Top 5 Customers

VAR TopFilteredCity =
    CALCULATETABLE(
        GENERATE(
            VALUES( Location[County] ),
            TOPN(
                5,
                CALCULATETABLE( VALUES ( Location[City] ) ),
                [Total Profits]
            )
        ),
        ALLSELECTED()
    )
VAR TopFilteredCustomer =
    CALCULATETABLE(
        GENERATE(
            VALUES( Location[City] ),
            TOPN(
                5,
                CALCULATETABLE( VALUES ( Customer[Customer ID] ) ),
                [Total Profits]
            )
        ),
        ALLSELECTED()
    )
RETURN

IF( NOT( ISINSCOPE( Location[City] )), [Total Profits],
    IF( NOT( ISINSCOPE( Customer[Customer Name] )), 
    CALCULATE( [Total Profits],
        KEEPFILTERS ( TopFilteredCity )
    ),
    CALCULATE( [Total Profits],
        KEEPFILTERS ( TopFilteredCustomer )
    )
))

.
image

2 Likes