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.
The default behaviour of the filters in a dashboard is AND. We need to find a way to turn this into an OR.
We will use the filtered measure plugin in order to display the OR result.
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.
In order to get the desired result we will define the following formula:
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.