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, take your time. it is exciting to have this solution in place. Good luck!
I've started to check the numbers. Unfortunately, the calculation breaks with the current measures when multiple Risks (but not all) and multiple Causes (but again not all) are selected.
The sample I gave is of course an example and I don't now if you can find a case to show that it breaks.-- One should probably extend the Table with one line or change one of the Risk-filtering rules, but I'm a bit under pressure at the moment.
So far, I've worked directly in my bigger setting (which is just much more causes, effects and rows in total). In my setting, it definitely shows 0.
I guess it makes sense because the current measures do not account for the number of the new filter selections, i.e. for the selected Risks.
(That's why I guess also in the example, which you can see asap, it won't work. Maybe you can check it for me and tell me if/in which regard to extend the Table or change something else for you to be able to see if it breaks.)
Anyways, any idea from you is more than welcomed! Many thanks!
I extended your measure to something like this:
_Totalvalue_selected – 3 =
VAR _SelectedEffects = FILTER(VALUES(TextEffect[Effect]), ISFILTERED(TextEffect[Effect]))
VAR _SelectedCauses = FILTER(VALUES(TextCause[Cause]), ISFILTERED(TextCause[Cause]))
VAR _SelectedCausesAndEffects = UNION ( _SelectedCauses, _SelectedEffects )
VAR _MatchingSlicerValues = ADDCOLUMNS(TextData, "@IsMatched",
VAR _TextValue = TextData[Text]
RETURN SUMX(FILTER( SelectedCausesAndEffects,CONTAINSSTRING(TextValue,[Cause])), 1))
VAR _CountOfSelectedValues = COUNTROWS(_SelectedCausesAndEffects)
VAR _SelectedRisks = FILTER(VALUES(TextRisk[RiskCategory]), ISFILTERED(TextRisk[RiskCategory]))
VAR _CountOfSelectedRisks = COUNTROWS(_SelectedRisks)
VAR _MatchingSlicerValues_withRisk = ADDCOLUMNS(FILTER(_MatchingSlicerValues, [@IsMatched]= _CountOfSelectedValues), "@IsMatched_Risk", SUMX(_SelectedRisks, 1))
VAR _MatchingExactSlicerValues_withRisk= FILTER(_MatchingSlicerValues_withRisk, [@IsMatched_withRisk]= _CountOfSelectedRisks)
RETURN IF(NOT(ISFILTERED(TextCause[Cause])) && NOT(ISFILTERED(TextEffect[Effect])) && NOT(ISFILTERED(TextRisk[RiskCategory])), BLANK(), SUMX(_MatchingExactSlicerValues_withRisk, [Value]))
It seems to work, but maybe you can have a look, because you'll definitely spot the mistakes right from the start. (I hope I haven't misspelled somthing, as I'm still working in my big setting with my slightly diferent variable names.) Maybe you can even chek it directly against the sample data.
Even if correct, it can definitely be written much more parsimonous.
I'm ages apart from you masterful knowledge level... so a bit of further help would be totally great. By all means, I've learn a whole lot from you already. Very many thanks!