Filter TOP N with more filters

[Anonymous]

Hi team,

I need to filter top 25 clients with more revenue. I did it with top N filter, however I need to filter by country and need that my visual also get filtered but only by my first filter of 25 clients. 

For example, I already filtered 25 (out of 100) clients with filter top n (first table) .... and when I need to filter by country, lets say Peru, I need that mi visual shows only the clients in Peru that are contained in the first table and not all the clients top 25 from Peru. 

 I hope its clear. How could i do a filter over anothe filter top n? Thank you in advance

[Anonymous · Accepted solution]

You need 2 tables with countries. Here's the design:

// Fact
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY45DsAgDAT/4hqkQO4SeAaiyH0p/2+zkGab1Wg0lhyjOD2JEmMxTpKK4vWchcH4IoJesqgwoQinV/BIFxt4oIMd3FN/gDvqT3BL/QVuqL/BNfUP2FL/5of+Pn0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Rev = _t, Country = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Rev", Int64.Type}, {"Country", type text}})
in
    #"Changed Type"

// Client
let
    Source = Fact,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Client"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

// Country
let
    Source = Fact,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Country"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

// Country For Filtering TopN
let
    Source = Country
in
    Source

// Measures_
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"})
in
    #"Removed Columns"

And measures:

MEASURE Measures_[Total Rev] = Sum( 'Fact'[Rev] )

MEASURE Measures_[ShouldShowCountry] = 
if(
    ISINSCOPE( Country[Country] ),
    var SelectedCountries = DISTINCT( 'Country For Filtering TopN'[Country] )
    var CurrentCountry = SELECTEDVALUE( Country[Country] )
    var Result = 
        int( CurrentCountry in SelectedCountries )
    return
        Result
)