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.
Oh, I'm sorry, I'm in Germany so it's way too late for me (almost 8pm), I've already closed my day and can't use the systems out of office.
What about tomorrow 5pm Mid European time, would it be too early for you? Else please name a time more convenient. And tell me how to connect please!
This would be really great!! Huge thanks!
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)!