Top N Filter when drilling through hierarchy

DAX DAX Calculations
1

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)

2

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 )
    )
)

image
image537×552 16.6 KB

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

2 Likes
3

Wow that’s brilliant! Thank you!

image
image1602×782 134 KB

@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?

4

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

3 Likes
5

Hi Melissa, appreciate for your expert level response though I want to get dynamic top N filter for all three categories. Hierarchy level of mine is different but you may share the logic using the same country > city > customer name scenario.

6

Hi @Susanta1991,

Welcome to the forum, glad to have you here!

Please avoid asking questions in a Solved thread, instead create a new topic and refer to this one. Thank you.

2 Likes
7

Hello @Melissa,

Thanks for the response, will surely create a new thread asking the same query with share background data. cheers.

1 Like