Forum Discussion
Question on sub filters
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?
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"
3 Replies
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"} ))
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!
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"
