Stumped: return all within a category

[RyanAck]

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

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. 

[Greg_Deckler]

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

[RyanAck]

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. 

Article	UPC	Quantity
10447401	190275903264	1
10447401	190275903134	1
10447401	190275902984	3
10447401	190275903172	3
10447401	190275903066	0
10447401	190275902717	5
10447401	190275903035	8
10447401	190275903080	1
10447401	190275903394	4
10447401	190275903059	3
10447403	190275903219	0
10447403	190275902564	1
10447403	190275902588	3
10447403	190275902786	3
10447403	190275903011	0
10447403	190275903318	1
10447403	190275903004	2
10447403	190275903271	0
10447403	190275902656	3
10447403	190275903370	2
10447403	190275902601	2
10447404	190275903202	0
10447404	190275903295	0
10447404	190275903356	1
10447404	190275903141	2
10447404	190275903158	0
10447404	190275903240	2
10447404	190275903332	1
10447404	190275902977	4
10447404	190275903042	6
10447404	190275903288	2
10447404	190275902908	2
10447404	190275902854	0

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

[Greg_Deckler]

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.

[RyanAck]

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. 

Thank you for your reply.

[Greg_Deckler]

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?

[RyanAck]

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

[Greg_Deckler · 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.

[RyanAck]

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!

[Greg_Deckler]

Awesome! Glad it's of use!

[RyanAck]

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.