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
RyanAck
Advocate I
Advocate I

Stumped: return all within a category

Hi everyone, I am completely stumped on this. Within our data items are categorized by article and UPC. Article is the style, and UPC's are the sizes within that style. 

 

Below is a screenshot of where I want to end up. I want to be able to filter by a UPC, and have all UPC's returned for that article. I changed the slicer interactions to fake it for this demonstration screenshot. 

 

Desired OutcomeDesired Outcome

 

I've tried different combinations of ALL(), ALLEXCEPT(), and SELECTEDVALUE() but have only been able to get to this point below. 

Current Outcome(s)Current Outcome(s)

 

I feel like this has to be possible, but I am not gaining momentum. If it's not possible, can someone let me know so I can pivot and stop spinning my wheels? 

 

Thank you for taking the time to check this out. 

1 ACCEPTED SOLUTION

OK, the solution to this would take a bit to explain so just attaching the file. Most important point, separate, unrelated UPC table.

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

10 REPLIES 10
Greg_Deckler
Super User
Super User

Can you post some sample/example raw data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Formulas

The only formula I have in my file is the following, which I used as a test but not successfully

All Qty = CALCULATE(Sum([Quantity]), ALL(pqRetailSalesTransactionDetail[UPC]))

 

Data Subset

I'm using a single table right now, though am open to any possibilities that will make this work which may involve multiple tables and relationships between them. 

 

ArticleUPCQuantity
104474011902759032641
104474011902759031341
104474011902759029843
104474011902759031723
104474011902759030660
104474011902759027175
104474011902759030358
104474011902759030801
104474011902759033944
104474011902759030593
104474031902759032190
104474031902759025641
104474031902759025883
104474031902759027863
104474031902759030110
104474031902759033181
104474031902759030042
104474031902759032710
104474031902759026563
104474031902759033702
104474031902759026012
104474041902759032020
104474041902759032950
104474041902759033561
104474041902759031412
104474041902759031580
104474041902759032402
104474041902759033321
104474041902759029774
104474041902759030426
104474041902759032882
104474041902759029082
104474041902759028540

 

I'm learning the ropes of the community here, thanks for the link to the posting guidelines Greg.

No worries, that's why I created that post!

 

I believe in your case in playing with your data is that in your table visualization you need to click the little drop down arrow next to your columns in your Values area and set "Don't summarize". Let me know if that works.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg, that's good to know for other projects, but still limits me to only the UPC selected. 

 

I'm kind of trying to go backwards. If I could make it say something, it would say...Find the article of the selected UPC, then filter the visual to show all UPC's that live within that article, not only the one selected. 

 

It's counterintuitive to everything I know about filters, but DAX has opened a whole new world of what's possible. 

3.png

Thank you for your reply.

Oh, I think I get what you are trying to do. So, let me state this back to you to make sure I understand. You want to pick a UPC. That UPC is tied to a an article. You want to return all other rows that have that same Article ID, correct?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Yes exactly! Thanks for saying that so much more poetically than my attempt.

OK, the solution to this would take a bit to explain so just attaching the file. Most important point, separate, unrelated UPC table.

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Greg, this is amazing. I almost gave up on this. Thank you so much for your help, I'm going to dissect what you did and recreate it. I appreciate your time and effort here! This has a huge impact on our organization and I can think of many ways I will use it in other applications as well. 

 

Thank you again!

Awesome! Glad it's of use!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you Greg - sorry, new to the forum -- I'll scrub some data and go through the steps outlined in that link to document everything. Thank you very much for sharing.

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.