Slice based on multiple inputs

I want to filter my data using slices based on the user input.

I have the slice set up with the row filter conditon: ANY(Filter[Set Name]) = [Set Name]. Filter is a table with the dynamic drop down [Set Name] connected to my data. This column is set to Enumlist in the column type so I can choose more than one value.

According to the expression assistant ANY() will only return one value though, so if I choose 2 selections from my drop down I only get a return for the first value selected.

How can I choose more than 1 value from my drop down to filter accordingly?


1 Like

IN() worked for me thanks!

Follow up question now, can I combine multiple column filters to show data that fits the selected values?

And() makes all options be true
Or() makes any be true and lists each filter as its own list.

I have Set Name, Rarity, and Card Type as drop downs.

I want to be able to choose any options from any columns and have it combine all those values and show the respective data.

I would try:

OR(
if(isnotblank([Set Name]), in([Set Name], Filter[Set Name]), false),
if(isnotblank([Rarity]), in([Rarity], Filter[Rarity]), false),
if(isnotblank([Card Type]), in([Card Type], Filter[Card Type]), false)
)

The idea being… in order for a row to show up, any of the conditions can be true. Each condition contains it’s own IF() statement that checks to see if something has been selected in the filter - if so, then a criteria is enforced, otherwise the answer is false: this prevents any unwanted records from showing up.

So reading down the formula:

  1. If there is a Set Name selected, the set name of any record needs to be included inside the list of selected filter set names - otherwise don’t show any records.
  2. Similarly, only if there’s a Rarity selected will those records be included - otherwise nothing is added to the list.
  3. And same thing here - only those that have the Card Type that’s selected will be included.

In the end, if nothing is selected in the filter expression, then each branch returns a false - and nothing is shown in the slice.

I’m following your logic but the OR() formula still only allows one variable at a time not stacking the filters, just listing them one after another as before.

I think I can achieve my goal by nesting AND() statements inside an OR() statement for each filter combination possibility EDIT: this did not work.

Actually it should be:
OR(
if(isnotblank(Filter[Set Name]), in([Set Name], Filter[Set Name]), false),
if(isnotblank(Filter[Rarity]), in([Rarity], Filter[Rarity]), false),
if(isnotblank(Filter[Card Type]), in([Card Type], Filter[Card Type]), false)
)

This still only adds the lists together not drills them down into a smaller list.

Hmmm… I"m thinking that last one should have worked.

If you add multitechvisions@gmail.com as a collaborator, I’ll take a look if you want.

1 Like

Solution:

AND(
if(isnotblank(Filter[Set Name]), in([Set Name], Filter[Set Name]), True),
if(isnotblank(Filter[Rarity]), in([Rarity], Filter[Rarity]), True),
if(isnotblank(Filter[Card Type]), in([Card Type], Filter[Card Type]), True),
if(isnotblank(Filter[Owned]), in([Owned], Filter[Owned]), True)
)

3 Likes