Forum Discussion
filter for (multiple select with AND)
Anonymous the quick solution without changing existing measures is to improve the model using following approach:
"Text cause" table used for cause slicer will have record for both high-risk and low-risk
"Text Effect" table will have a record for each risk category, see below:
There will be a new table for risk category selection and I called it "Risk Category"
And this is how these tables will be connected:
and as you will make the risk selection, cause and effect slicers will be changed accordingly. Let's start with this and see if it works for you. The only issue I see with this is that if low-risk or mid-risk is selected, the user still has to select the Effect in the slicer even if it has one choice. If we want the measure to work without the user having to select the effect then we need to update the measure.
So, I gather today 5pm Mid European time and, if I reckon correcttly, 8am your time is fine for you? You let me know how to connect please!
Only as a note: I'm not allowed to show original data, in case you planned to talk on that basis, but ... let's talk.
Of course, if it's still fine for you! By all means, really many thanks!
I'm getting the impression the issue is even more tricky.
If you select two Risks with not (-entirely) overlapping Cause and/or Effect choices, of course the filters show the union of all these possibilities to choose from. If two options outside the intersection set of possibilities are chosen, it breaks, as it looks for those options simultaneously.
In the sample data: Say you select both mid- and low-risk.
The Effect filter shows both recall (valid for mid-risk) and allergy (for low-risk). If both are selected (and no Cause), the result is:
| Text | Date | Value | Risk Category |
| Needless recall of peanut energy bars amidst allergy fears | 11/23/2013 | 3,107 | mid-risk |
Fine for mid-risk, but not for low-risk, where it should actually look for allergy only and the result should then be:
| Text | Date | Value | Risk Category |
| Apples' China risk | 9/8/2023 | 778,985 | low-risk |
| Pick Peanuts, Pick Apples and Get Out of Town | 10/1/2023 | 13,270,269 | low-risk |
| A Low-Cost Grocery Delivery Service With Much More Than Ugly Apples | 11/9/2023 | 5,543,076 | low-risk |
| I have a severe allergy to strawberries | 9/23/2023 | 4,013,349 | low-risk |
| Nestle divests peanut allergy business Palforzia | 9/3/2023 | 23,837 | low-risk |
| Woman who used Apple looks on as witness recalls ‘crunching’ sound | 8/16/2023 | 92,142 | low-risk |
Needless to say what happens if peanut is chosen (no Effect). For low risk, it shouldn't even look for a Reason, still you get results:
| Text | Date | Value | Risk Category |
| Pick Peanuts, Pick Apples and Get Out of Town | 10/1/2023 | 13,270,269 | low-risk |
| Nestle divests peanut allergy business Palforzia | 9/3/2023 | 23,837 | low-risk |
And everything sums up in the end.
To my mind, the calculation should happen separately for each of the chosen filters and then union the results or something alike. This I'm definitely not able to sort out with a formula (or even more).
See you later then (8Am your time, if google calculated it correctly, sorry for the initial typo)!