Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
EmaT
Helper II
Helper II

multiple select with AND

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
appleallergy
peanutrecalls
raisin 

 

 

Text Date Value
Apples’ China risk9/8/2023778,985
Pick Peanuts, Pick Apples and Get Out of Town10/1/202313,270,269
A Low-Cost Grocery Delivery Service With Much More Than Ugly Apples11/9/20235,543,076
Krispy Kreme urgently recalls four-pack of doughnuts over peanut allergy fears8/7/20234,013,349
Recalls over allergy fears: Chocolate raisin snacks may contain peanuts10/9/20236,518,508
I have a severe allergy to strawberries9/23/20234,013,349
Aldi urgently recalls deli meats over allergy fears8/23/20234,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 peanuts10/9/20236,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 fears8/7/20234,013,349
Recalls over allergy fears: Chocolate raisin snacks may contain peanuts10/9/20236,518,508
Aldi urgently recalls deli meats over allergy fears8/23/20234,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?

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@EmaT here is the part 2 video. Thanks!

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

23 REPLIES 23
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

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

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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
Super User
Super User

@EmaT here is the part 2 video. Thanks!

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

pls Share sample pbix file

Thanks, but I already got a solution.

Sorry!

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

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

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!

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
Super User
Super User

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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

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
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors