Question on sub filters

[Anonymous]

Hi I need some hep with the following:

Here's my sample data 

NameCountryLevel

Carol	Australia	Exe 1
Jeremy	Ireland	Exe 1
Rachel	Italy	Exe 1
Colin	Japan	Exe 1
Kelvin	Australia	Manager 1
Tom	Ireland	Manager 1
Emma	Italy	Manager 1
Terence	Japan	Manager 1
Tim	Australia	Manager 2
Christopher	Ireland	Manager 2
Aileen	Italy	Manager 2
Lucas	Japan	Manager 2
Justin	Australia	Manager 3
Eric	Ireland	Manager 3
Randall	Italy	Manager 3
Stuart	Japan	Manager 3
Pearl	Australia	Senior Leader 1
Sarah	Ireland	Senior Leader 1
Howard	Italy	Senior Leader 1
Dorothy	Japan	Senior Leader 1
Jan	Australia	Exe 2
Tammy	Ireland	Exe 2
Jon	Italy	Exe 2
Lando	Japan	Exe 2

Question 1

1. I would like to filter and exclude across 2 fields. Specifically, I would like to remove people based in Japan who are not Leaders. How would I do that?

2. I only need Leaders + Mangers in my data set, so what I've done is to filter by level and remove the Exes. Though I do need to include a small number of Exes whom I am able to identify by name. So in the data above, how would I remove all exes, but keep only Jeremy and Lando?

[V-lianl-msft · Accepted solution]

You can use similar formulas in power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZJNTsMwFISvUnndDfQEValEQyoh0l3UxSh5wpb8U704QG7DWTgZLg4QW+7Oet9YM2/sthU7sNNiLbbj4BlaIZz3H7S6E+d1KypiMlMYHZg0bJ/AF3SSrncPHnpK0M5pZb8+w6zCBTZhT6TflM0sj7B4JZ4lJ2cSz5TujcHCNbsaEtuOFs4ZV+aG9X1MLlkN3l0kcTFCVG2VJrKFEBHXY4chWT/lVbC/WcEmLsmqKwbYzN3bHloXEkTe+BHsCwEifiZw/uoNWeV4VRP6v7YaMGQSo6R6dO/g/mff3zQl2YNj5+WU9FLSVciruX6e2NwJpvAf51bd8kX+53UQuuwrBnL+Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Country = _t, Level = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Country", type text}, {"Level", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Country] ="Japan" and [Level] <> "Senior Leader 1"
then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Level] <> "Exe 1" and [Level] <> "Exe 2" and [Custom] <> 1 or [Name] = "Lando" or [Name] = "Jeremy"),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

[amitchandak]

@Anonymous , Try measures like these measures

calculate(countrows(Table), filter(table, not(Table[Country] ="Japan" && search("Leader",[Level],,0) =0)))

calculate(countrows(Table), filter(table, search("Exe",[Level],,0) >0 || search("Manager",[Level],,0) >0 || [Name] in {"Jeremy","Lando"} ))

[Anonymous]

Thanks Amit,

I'm trying to work through the first solution. Can you explain the last part (in bold) in detail?

calculate(countrows(Table), filter(table, not(Table[Country] ="Japan" && search("Leader",[Level],,0) =0)))

My interpretation of this part is to exlude all in Japan, except the title leader. Is that correct?  My data has leader 1, leader 2, leader 3 and so on. Does that mean in the formula above I need to include all the leaders that I want to keep, or specific all that I want to exclude? Could you please share an updated Measure formula so I can slowly analyse it? 

Thanks!