filter for (multiple select with AND)

[EmaT]

@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!

[parry2k]

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.

[EmaT]

I sent you an email. No worry. Many great thanks!

If needed, we can still arrange call another day. Just let me know.

Many thanks!

[parry2k]

Do you have time to connect? I am in pacific time zone. 

[EmaT]

Hi! Are you there? Still want to connect or we better keep on messaging here?

I'll be around for ca. 50 min - 1h, you can always write me. Afterwards I might read it only tomorrow (in ca. 19-20h from now on). Here it's 5:11 pm. Thanks a lot!

[EmaT]

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!

[EmaT]

@parry2k 

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! 

[EmaT]

@parry2k 

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)!

[parry2k]

@EmaT No worries, take your time. it is exciting to have this solution in place. Good luck!

[EmaT]

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!

[EmaT]

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!

[parry2k · Accepted solution]

@EmaT 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.  

[EmaT]

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.

[EmaT]

Many thanks! It looks great, as usual!

I'll probably need to unpivot-pivot in Power Query or something, because in reality I have much more Causes and Effects. But I'll try & see if it works tomorrow afternoon and let you know.

Big thanks!

[parry2k]

@EmaT It should be easy to do in PQ.

[parry2k]

@EmaT 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.

[EmaT]

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!