Performing an OR between dashboard filters

[intapiuser]

Analytical Need 

A common requirement in data analysis is to be able select values from several filters and to see the results that contains all of those values and not only the results where only selected values appear.

In fact, the requirement is to have an OR and not an AND between the filters.

For example, I have 2 filters of country & product.

I choose country "UK" and product "A":

AND will get me results just for product "A" sold in the UK

OR will get me results for all the products sold in the UK or all the countries that product "A" has been sold in.

Challenge

The default behaviour of the filters in a dashboard is AND. We need to find a way to turn this into an OR.

Solution

 We will use the filtered measure plugin to display the OR result. This add-on is pre-installed on Sisense in Linux environments and its version could be different. The download link is for Sisense on Windows.

Our data:

When we select product A and country UK we don't want to see the second record (B, Greece), but we do want to see all the rest.

Dashboard filters:

In order to get the desired result we will define the following formula:

(sum([value]),[@Country])+(sum([value]),[@Product])

The @ sign means that this part of the formula refers only to the mentioned dashboard filter. The + sign is actually a sum between the values.

However, this is only getting us half way, since for the records that contain both UK and product A - we will get the amount doubled like so:

We need to divide these result by the amount of filter values the record is associated to.

In this case, we need to divide the first row by 2 (because we have UK & A) and the rest by 1 (we have either UK or A). In order to achieve this calculation we will need to use this formula:

(([# of unique Country],[@Country])+ ([# of unique Product],[@Product]))

So the final formula will be the division between the 2:

((sum([value]), [@Country]) + (sum([value]), [@Product])) /
(([# of unique Country], [@Country])+ ([# of unique Product], [@Product]))

Now the results are correct:

In case you have more than 2 filters, you'll need to add more calculations to the nominator & the denominator.

Note: the rest of the dashboard filters that don't participate in the formulas will behave with the normal AND functionality. 

Cube

Detailed calculations

The default behavior (no filter measure)