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.
Anonymous no worries, can you confirm following:
| Risk Category | allowed selection | |
| high-risk | all Causes and Effects | Do you mean whatever selection the user has made (basically the current behavior of the solution) |
| mid-risk | all Causes but only Effect=recall | what does all causes mean - user selected causes or all the causes in the slicer table - override user selection just use all the values |
| low-risk | no Cause and only Effect=allergy | I guess if the user selected any cause in the slicer it will be not taken into consideration. |
First of all: many thanks!
- High-risk: Yes, basically the current solution... (Actually, the same if all or no Risks are selected.)
- Mid-risk: The user can select any/all of the existent causes (all in the slicer), there is no restriction. Restricted are only the effects (namely to recall only). Ideally again, the Effect filter would show only recall as an option.
- Low-risk: Ideally again, the filter Cause would show to be empty or wouldn't allow to click upon or something. (No clue now to manage this without a relationship of Causes and Effects to the Table.)
In the end, the most important is that the calculation of the total Value is correct, I mean according to these on-top restrictive rules.
(As I see it: If there is a way to filter the filters, the calculation would remain the current solution. Else the rules should be integrated in the calculation itself.)
Thank you very much!