Widget Filters - condition across multiple fields

[AlexW]

Working as a dashboard designer without access to the back end data. I am trying to filter with an 'or' condition across a number of fields.

I can only see how to apply a single field filter on the front end, which would equate to an and condition across the field.

e.g. data to identify high priority records are where Field_1, Field_2, Field_3 or Field_4 are equal to "Yes". 

[AssafHanina]

hey @AlexW ,

As a Dashboard Designer, you have the ability to add filters and change the Operand between the Filters.
the Default is AND and it can be changed to OR as well. 

there are prerequisites which require to Validate with Sisense Admin.
Please refer to the official documentation of the Filter Relationships Editor

Best Regards

[DRay]

Hello @AlexW,

I’m following up to see if the solution offered by @AssafHanina worked for you.

If so, please click the 'Accept as Solution' button on the appropriate post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

[AlexW]

Is it possible to do this filtering on a widget rather than a whole dashboard?

[harikm007]

Hi @AlexW ,

Please check if the solution below works for you

https://community.sisense.com/t5/knowledge-base/how-to-use-quot-or-quot-filter-condition-on-a-widget...

-Hari

[Jake_Raz]

Maybe try using Measured Values? You could create a formula, using an IF statement and chaining together your conditions with AND. If all conditions are true, output 1, else 0. Then you filter on that formula for only the items/rows with a "1".

The tricky part is that, within the formula, when you're setting the conditions, you can't just say something like:

[Field_1] = "Yes"

This is because the IF formula only really works when you're comparing numerical values. However, you can construct the formula such that it still results in the same outcome. To do so, when you're in the formula editor, add two parentheses - "(" and ")" - and then inside the parentheses do the following: 

1. Use the data browser to search for the field you need
2. Hover over it, select "More...", then "Count Unique"
3. Type a comma (,) then re-add the same field, but this time just click on it directly
4. After the closing parentheses, add " = 1 "

It should look something like this:

( [# unique Field_1], [Field_1] ) = 1

Then you would click on the second item and select "Filter", and select the Yes value (or whatever value you're looking for. This will ensure that you're filtering down to only the records/items in the widget that contain a Yes value in that field. 

You can use this in an IF formula. If the condition evaluates to true, output a 1, else output a 0 (or NULL). Then you can just chain together multiple statements with AND's, so it only outputs the 1 if all conditions are true.

The final formula would look something like this:

IF(
    ( [# unique Field_1], [Field_1] ) = 1 AND
    ( [# unique Field_2], [Field_2] ) = 1 AND
    ( [# unique Field_3], [Field_3] ) = 1 AND
    ( [# unique Field_4], [Field_4] ) = 1,
    ,1,0
)

(Remember, you also need to click on each of those Fields and select "Filter" so it knows what it's looking for.)

Then you filter your widget for when that formula is equal to 1. The nice thing is that, after you have the filter in place, you can hide the original formula so it doesn't display in the actual widget (since it was only necessary for the filtering).

[DRay]

Hello @AlexW,

I’m following up to see if any of the solutions offered worked for you.

If so, please click the 'Accept as Solution' button on the appropriate post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

[AlexW]

@Jake_Raz I have attempted this with an or but got no results, should I be structuring this differently?

[Jake_Raz]

@AlexW Hmm. Without seeing your underlying data, I couldn't say, sorry. I will say that, whenever I need to create formulas like this, I often run into issues with getting it to work in the way I'm expecting. I usually just keep experimenting (and searching the Sisense forums for useful info) until I find something that works.

Perhaps there's an issue with one or more of the conditions? One thing you could try is to filter down to a particular record in your data that you know should be in the final output. Like, if you know Matter123 should show in the view because it meets all the conditions, then add a widget filter for the matter name and specify that one matter by itself. After that, try creating each condition as its own separate formula. Like, instead of chaining all them together with ORs/ANDs in the same formula, you would have each condition in its own formula that resolves to a 1 or 0. Then you can add them all to the view and validate, separately for each condition, whether it's correctly resolving to a 1. In order for your chained OR formula to work, at least one of the conditions must be resolving to a 1 (or if you're using AND, then all of them must).

Another common issue to run into with this sort of formula are error messages in the formula editor. You didn't mention this but it might be something you encounter. Often this is because the underlying data query is pulling in multiple rows of data per item in the view, so you have to aggregate it somehow by wrapping certain elements of the formula in MIN, MAX, SUM, etc.

Good luck! It can definitely be fiddly, sometimes. If you can't find a way to achieve this through formulas then you may need to look into the other solutions suggested.

[DRay]

Hi @AlexW,

Would you like me to get the ball rolling on connecting you with a technical resource who will work with you on this?

[ramansingh89]

@Jake_Raz Do you think your suggestion can help resolve my use-case as well ? Here is the description

https://community.sisense.com/t5/help-and-how-to/single-filter-against-multiple-columns/td-p/27079

[Jake_Raz]

Perhaps. I'll take a look and post a reply there 🙂