Row Filter Condition Combinations For User Filters

Jordan_Davis1 · 07-14-2020 07:20 PM

I have four different user filters in a dashboard detail view which let the users filter the data in the table view interactively. One filter is for ‘many days since last contact’, one filter for ‘client’, another for ‘stage’, e.t.c. I have a slice for the data through which my filters get applied using row filter conditions like

OR(OR(OR(ANY(Filter[Client])=[Client],ANY(Filter[Stage])=[Stage]),ANY(Filter[Communication Cycle])=[Communication Cycle]),ANY(Filter[Days Since Last Communication])<=[Days Since Last Communication])

All of the filters work independently but is there any way of allowing them all to be used in conjunction? I could go ahead and write out every combination possible but, before I do, does anyone know if there is a smarter/more efficient expression for this?

Suvrutt_Gurjar

If I have understood your requirement, I believe you may be able to achieve a simple combination of AND and OR fileters. Beyond this, every possible combination of ANDand OR may not be possible or may become very complex as per my understanding.

However implementation that I could think of is still not very simple.

I believe you may need 4 additional enum buttons in that detail view, one each for those filter parameters, where enums denote whether the parameter needs to be ORed or Anded in overall expression.

Then I believe there would be at least fairly long slice filter expression consisting of 8 IFS() subexpressions for those 4 paramters (Client, Stage, Communiation Cycle, Days Since Communication etc) to be ANDed or ORed based on Enum selection.

**Edit:**Minor spelling corrections

Steve

This is the expression you gave, reformatted for my clarity:

OR(
  OR(
    OR(
      (ANY(Filter[Client]) = [Client]),
      (ANY(Filter[Stage]) = [Stage])
    ),
    (ANY(Filter[Communication Cycle]) = [Communication Cycle])
  ),
  (ANY(Filter[Days Since Last Communication]) <= [Days Since Last Communication])
)

The expression can be simplified:

OR(
  (ANY(Filter[Client]) = [Client]),
  (ANY(Filter[Stage]) = [Stage])
  (ANY(Filter[Communication Cycle]) = [Communication Cycle])
  (ANY(Filter[Days Since Last Communication]) <= [Days Since Last Communication])
)

What do you mean by this? The expression above will include in the slice any row that matches any one or more of the individual conditions. If you only want included rows that match all four of the conditions, just change OR() to AND().

Jordan_Davis1

Hi Steve and Suvrutt, thanks for your feedback!

Per your suggestions, i’ve tried the additional AND combinations, however, what happens is that the list gets filtered to show only results that match the criteria of one filter and then it adds the results that match the criteria of the second filter. How can I combine these filters so that Filter A produces a list that matches its criteria and then Filter B filters within that list?

Also, you guys are so helpful, every time I search for answers on community pages, I basically just scroll through a post until I see your icon and I know it’ll be the answer i’m looking for. I really really appreciate it all of your explanations.

Steve

The simplified expression I posted above omitted some commas, which would definitely affect the results. Try this instead:

AND(
  (ANY(Filter[Client]) = [Client]),
  (ANY(Filter[Stage]) = [Stage]),
  (ANY(Filter[Communication Cycle]) = [Communication Cycle]),
  (ANY(Filter[Days Since Last Communication]) <= [Days Since Last Communication])
)

Steve

If that didn’t work (I expect it didn’t), try this:

AND(
  IN([Client], Filter[Client]),
  IN([Stage], Filter[Stage]),
  IN([Communication Cycle], Filter[Communication Cycle]),
  OR(
    ISBLANK(Filter[Days Since Last Communication]),
    (ANY(Filter[Days Since Last Communication]) <= [Days Since Last Communication])
  )
)

Jordan_Davis1

This works but the filters aren’t working together. The results I’m getting are the results of Filter A and, separately, the results from Filter B. What i want is to filter my table using Filter A and then further filter those results using Filter B. Is this possible?

Steve

This makes no sense. Please provide a screenshot.

Jordan_Davis1

Yeah, i’m doing something wrong. Using your expression:

AND(
(ANY(Filter[Client]) = [Client]),
(ANY(Filter[Stage]) = [Stage]),
(ANY(Filter[Communication Cycle]) = [Communication Cycle]),
(ANY(Filter[Days Since Last Communication]) <= [Days Since Last Communication])
)

I get the following behavior. It only shows me results once I’ve entered something in all filters. I can’t independently filter by client or date.

Jordan_Davis1

The second expression works much better but still requires me to fill in all fields before seeing any data. It’s a dev app and I’m happy to give you access if it’s easier to see behind the hood.

Austin

Haven’t read the formulas much but I’d suggest using IF(ISBLANK(FIELD), TRUE, CONDITION) instead of the ANY() this gives a true if you’re not filtering on that and matching on your condition otherwise. Might be something to do with how blanks return in statements.

Jordan_Davis1

That’s the second expression that Steve provided which works better than the one in my gif but still requires all fields to be filled in before seeing data.

AND(
IN([Client], Filter[Client]),
IN([Stage], Filter[Stage]),
IN([Communication Cycle], Filter[Communication Cycle]),
OR(
ISBLANK(Filter[Days Since Last Communication]),
(ANY(Filter[Days Since Last Communication]) <= [Days Since Last Communication])
)
)