cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RyanAck Regular Visitor
Regular Visitor

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. 

 

1.pngDesired Outcome

 

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

2.pngCurrent 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

Accepted Solutions
Super User
Super User

Re: Stumped: return all within a category

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

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


10 REPLIES 10
Super User
Super User

Re: Stumped: return all within a category

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


RyanAck Regular Visitor
Regular Visitor

Re: Stumped: return all within a category

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.

RyanAck Regular Visitor
Regular Visitor

Re: Stumped: return all within a category

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.

Super User
Super User

Re: Stumped: return all within a category

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


RyanAck Regular Visitor
Regular Visitor

Re: Stumped: return all within a category

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.

Super User
Super User

Re: Stumped: return all within a category

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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
RyanAck Regular Visitor
Regular Visitor

Re: Stumped: return all within a category

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

Super User
Super User

Re: Stumped: return all within a category

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

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


RyanAck Regular Visitor
Regular Visitor

Re: Stumped: return all within a category

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!