Forum Discussion
filter for (multiple select with AND)
parry2k multiple select with AND
I have one more layer to the problem presented in the post "multiple select with AND". I recap the essential issues.
I have the same two lists of keywords: Cause and Effect. I have filters allowing for multiple selection out of both lists (incl. select all).
1. First task is to find all selected keywords at once from (both) these lists in a Table column Text and then to sum up the amounts Value corresponding to this selection. (Already solved in "multiple select with AND".)
| Cause | Effect |
| apple | allergy |
| peanut | recall |
| raisin |
2. In addition, the Table has an extra column for the Risk Category, which restricts the possible causes and effects. (It appears as an extra filter visual.)
E.g. low-risk should allow to search for allergy only, whatever the cause, mid-risk for all causes but only effect recall, and high-risk for all possible combinations of causes and effects.
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 |
Krispy Kreme urgently recalls four-pack of doughnuts over peanut allergy fears | 8/7/2023 | 4,013,349 | high-risk |
Recall over allergy fears: Chocolate raisin snacks may contain peanuts | 10/9/2023 | 6,518,508 | high-risk |
I have a severe allergy to strawberries | 9/23/2023 | 4,013,349 | low-risk |
Aldi urgently recalls deli meats over allergy fears | 8/23/2023 | 4,013,349 | high-risk |
Raisin allergy sends Patrick and Brittany Mahomes' baby to ER | 8/19/2023 | 199,000 | high-risk |
Nestle divests peanut allergy business Palforzia | 9/3/2023 | 23,837 | low-risk |
Peanut allergy study promising | 6/19/2023 | 92,000 | mid-risk |
Woman who used Apple looks on as witness recalls ‘crunching’ sound | 8/16/2023 | 92,142 | low-risk |
Needless recall of peanut energy bars amidst allergy fears | 11/23/2013 | 3,107 | mid-risk |
| Risk Category | allowed selection |
| high-risk | all Causes and Effects |
| mid-risk | all Causes but only Effect=recall |
| low-risk | no Cause and only Effect=allergy |
E.g. when Risk Category mid-risk is selected, the filter Cause shows all causes and the filter Effect only recall. With apple selected (+ recall), there is no text matching the selection, the final Value should thus be 0. If only peanut (+recall) is selected, then the final Value should be 3,107.
| Text | Date | Value | Risk Category |
| Needless recall of peanut energy bars amidst allergy fears | 11/23/2013 | 3,107 | mid-risk |
For Risk Category low-risk, where no Cause selection is possible and only Effect allergy, then the Value should be 4,013,349 + 23,837 = 4,037,186.
| Text | Date | Value | Risk Category |
| 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 |
Second task is then to show the valid filters Cause and Effect allowed under the selected filter Risk Category and correctly calculate the total Value according to the (allowed) selection. Ideally, even Risk Category has multiple selections, in which case of course the union of all allowed causes and effect for the multiple risks selected can be chosen from, but the Value calculation shall of course follow the rules.
Could you please help me some more? Many thanks in advance!
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.
16 Replies
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!
Hi, sorry on another call for another hour. I will read your messages later and get back to you. You can always communicate via email, it is ins my signature.
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!
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 It should be easy to do in PQ.
I've checkd it for the filters already.
But I realized I need to rewrite / add the old measures for calculations on each risk category (I was using grouped data before this new topic with the filter of the filters arrived). It will probably take me a bit longer to do this and especially check the numbers, though this report has high priority, and tomorrow I have a pretty busy day.
But I can definitely accept it already as a (great!) solution, many great thanks!
I'll let you know if I still encounter problems.
Do you have time to connect? I am in pacific time zone.
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!