Advanced Filter Option on Slice

[Rexatron]

Hi all, 

This is a fairly complex problem that I am dealing with currently. 

My objective is to do a deep filtering on the slice based on the two filters that I have set. Example, if user select year 2020 and selects option A, only option A items that are found in 2020 will be displayed.

You can refer to the picture below for reference. 

And this is the row filter condition that I use.

OR(SWITCH(
LOOKUP("dajs212","Filter","ID", "Status"),
"All Active", TRUE,
"All Non-Active", [Status] = "All Non-Active",
"9. Issue Final Report", [Status] = "9. Issue Final Report",
"8. Management Response", [Status] = "8. Management Response",
""
),
SWITCH(
LOOKUP("dajs212","Filter","ID","Year"),
2022, [Year] = 2022,
2021, [Year] = 2021,
2020, [Year] = 2020,
2019, [Year] = 2019,
2018, [Year] = 2018,
2017, [Year] = 2017,
2016, [Year] = 2016,
2015, [Year] = 2015,
2014, [Year] = 2014,
""
)
)

However, I am running into an issue where if I select Year 2020, I am given ALL of the list. My objective is to have a deep filtering function whereby if I select Year 2020 and Option A, only Option A items that are under 2020 will display. 

I hope someone can give a clue as to the expressions that I should use.  

[Suvrutt_Gurjar]

Maybe you wish to use AND() instead of OR()

Also if Year columns are of text type, better practice will be to use "2020" instead of 2020.

If those are number types, double quotes are not necessary.

[1minManager · Accepted solution]

Not sure if I've understood correctly.  But it sounds like you've got these two options and you want a further filtering of an existing slice. 

From what I can assume, its not working because if it,s an equation for a slice it should be a True/False equation that can be checked against each row e.g.

AND(
   [Year]=[_ThisRow].[Year],
   [Status]=[_ThisRow].[Status]
)

If instead your trying to create a LIST type virtual column, then you need a Select() type function to pull in a list of Key columns e.g.

Select(Filter[ID],AND(
   [Year]=[_ThisRow].[Year],
   [Status]=[_ThisRow].[Status]
   )
)

 Let me know if this helps

Simon@1minManager.com

[MultiTech]

You might find this helpful:

• Enhanced Dashboard Walkthrough