Report: Filter by a field with at least a certain amount of entries

[fabiomanniti]

Hello, I have a table like this:

User ID	Name	Country
1	Mark	GB
2	John	US
3	Robert	ES

I would like to make a page level filter based on countries.

As long as I want the Top N countries I know how to do but what if I want to show only countries with at least 100 users?

So that my page will authomatically update as soon as a country has new people within

Thank you

[OwenAuger]

Hi there,

The usual TopN filters or any measure-based filters are not available at page or report level (unfortunately).

A nice alternative could be to create a calculation group with a calculation item that evaluates the selected measure with an appropriate Country filter applied.

You would just need to ensure that you are using explicit measures everywhere, and perhaps tweak any visuals that don't include measures (such as slicers) to ensure that they are filtered by appropriate measures.

The calculation item's expression would look something like this (assuming your table is called Users )

VAR Threshold = 100
VAR CountriesToInclude = 
    CALCULATETABLE (
        FILTER (
            VALUES ( Users[Country] ),
            CALCULATE ( COUNTROWS ( Users ) ) >= Threshold
        ),
        REMOVEFILTERS () -- Ensure we're counting unfiltered users per country
    )
RETURN
    CALCULATE ( 
        SELECTEDMEASURE (),
        KEEPFILTERS ( CountriesToInclude )
    )
    

 Then you would just apply this calculation item as a filter on the relevant pages (or report level).

Please post back if needed.

Regards,

Owen

[freginier]

Just create a page level filter on country field choos TOP N and drag/drop number of user field and select 100. 

This filter will automatically filter / update your page.