multiple select with AND

[EmaT]

I have two lists of keywords: Cause and Effect. I have filters allowing for multiple selection out of both lists (incl. select all).

I need to find all selected keywords at once from (both) these lists in a Table column Text. (Further, I have to sum up the amounts Value corresponding to this selection.)

Cause Effect

apple	allergy
peanut	recalls
raisin

Text Date Value

Apples’ China risk	9/8/2023	778,985
Pick Peanuts, Pick Apples and Get Out of Town	10/1/2023	13,270,269
A Low-Cost Grocery Delivery Service With Much More Than Ugly Apples	11/9/2023	5,543,076
Krispy Kreme urgently recalls four-pack of doughnuts over peanut allergy fears	8/7/2023	4,013,349
Recalls over allergy fears: Chocolate raisin snacks may contain peanuts	10/9/2023	6,518,508
I have a severe allergy to strawberries	9/23/2023	4,013,349
Aldi urgently recalls deli meats over allergy fears	8/23/2023	4,013,349

E.g. if raisin, allergy and recall are simultaneously selected, the result should be:

Text Date Value

Recalls over allergy fears: Chocolate raisin snacks may contain peanuts	10/9/2023	6,518,508
Totals		6,518,508

If allergy and recalls are both selected (no Cause), then:

Text Date Value

Krispy Kreme urgently recalls four-pack of doughnuts over peanut allergy fears	8/7/2023	4,013,349
Recalls over allergy fears: Chocolate raisin snacks may contain peanuts	10/9/2023	6,518,508
Aldi urgently recalls deli meats over allergy fears	8/23/2023	4,013,349
Totals		14,545,206

In this latter situation, a chart by Year, Month, Day, should show for Aug 2023 8,026,698. 

Could anyone please help?

[parry2k · Accepted solution]

@EmaT here is the part 2 video. Thanks!

[parry2k · Accepted solution]

Hi @EmaT  Somebody commented on my part 2 video without adding an extra row in the slicer tables. (Cause and Effect). I have revisited the measure and added the revised DAX measure in the comments of my part 2 video. Please use that measure in your solution. Cheers!!

[EmaT]

@parry2k 

I've got an extra layer to this already pretty difficult topic. I made a new post and tagged you, maybe you can give me at least a hint. By all means many thanks!

@filter for (multiple select with AND)

[EmaT]

Thank you and your commentator! It's indeed more elegant.

[parry2k]

@EmaT Yes it was hard - I tried my best and also I was trying these videos out of the gate since you were waiting on it. Didn't want to spend too too much time waiting and thinking about it. Anyhow, the important part is if this solution works for you.

Indeed, feel free to reach out if you have more questions. When you create a post, just tag me in it and I will get notified. Good luck!

[EmaT]

I really appreciate it very much! The solution, your explanations and the whole effort!

And great, many thanks in advance for other questions, I'll definitely first get back to you!

Many many thanks!

[parry2k · Accepted solution]

@EmaT here is the part 2 video. Thanks!

[Ahmedx]

pls Share sample pbix file

[EmaT]

Thanks, but I already got a solution.

[EmaT]

Sorry!

But... You can create the file asap, just copy-paste my excel lists an import them in Power BI.

[Ahmedx]

Sorry, I didn't ask for your file, but I need Mr. Parry's 2k file

[EmaT]

I've watched it! It's perfect. I must explain a bit how to chose or not chose the "None", but it works for me, as far as I could test it a bit in a rush at the moment. Many many thanks!

Could I get back to you in case I'll still have questions or for other topics?

THANK YOU very much!

[EmaT]

Oh, huge thanks! I'll have a look tomorrow and let you know, but I'm very positive it's also great! Thank you a lot!

[parry2k]

Hi @EmaT , Thanks for your patience, 1st part of the video is here and 2nd is coming in a day or so:

[EmaT]

Many thanks! My only progresses were indeed in your direction, but the count matching condition eluded me.

But yes, I need the 2nd part, the no selection gave me from the start (another) headache. I hope you can post the video till Friday.

I'm looking forward to it, but already huge thanks, I like the solution and how you explain it.

[parry2k]

@EmaT Thanks for the feedback. Glad you found the explanation clear. I'm learning and trying to improve, and always looking for feedback. Best!

[EmaT]

Oh, many thanks! I'll have a look tomorrow. I only hope I'll be able to integrate it in my whole setting. 

Anyways, I can't wait to watch it & also the 2nd part! Big thanks!

[parry2k]

It will be I next 1-2 days. Is that ok with you?

[EmaT]

Do you have any idea how long it will still take you to post a solution? Thanks a lot!

[EmaT]

If it's too much to write me the solution already, I'll wait. It would be at the very last moment and I can only hope I am able to make it work in my setting. Either way,  many thanks!

[parry2k]

@EmaT sounds good - I have a solution but you need to make some changes to your effect and cause option. You have to have one more row which you can call "No Selection", and there are multiple reasons for that.

As a matter of fact, I think I'm going to do a video on this topic and explain everything in it, the solution and the reason for this additional row.