Limiting data on different levels

[SMEisMe]

I have item/purchase order level data in a single query.  I want to be able to select a single item from the top table, and have it show me all of the items that are on that same PO.  I got it to work by making a duplicate of the query, but I was wondering if there was a way of selecting data from a table that would limit the data in another table, but at a different level.

In this screenshot, I have PO 00159156, item AC0051 selected, and the bottom visual showing all of the items on PO 00159156.  Like I said, to make this work I have to load the PO query twice in two seperate table and create a relationship on Order number.  Is there a better way to do this?

[lbendlin]

surely the item table also includes a PO number foreign key column? You should be able to use that as your filter.

Define "Select".  For example if you use a matrix visual you can look at the item without having to make it a filter context. That would allow you to define its parent (the PO number) and then to look down again (its sibling items) 

[SMEisMe]

Thanks for your response, I'll try to better describe what's happening and what I'm trying to do.

I'm using two data queries.  The first is inventory by location and the second is open inbound purchase orders to that location.  The user will look at low inventory and filter down to that item/location combination.  They will then want to look at the inbounds by date and see if those inbound orders need adjusted.  In the picture I provided, the user identified that item AC0051 had low inventory and went to look at the inbound POs.  Seeing that inbound PO 00159156 has item AC0051 on it, they would then want to see what other items are also on that PO and see if they need to adjust quantities.

"Select" means the user will have either clicked on the single line of item AC0051 in the top chart or potentially used a visualization filter to filter down to the single item.  I'm only using a table visualization for both visualizations because that's the level of the data I'm trying to display, I dont need any aggregations.  The data for both visualizations is at the PO/Item/Location/Date for the inbound quantity.

I'm very new to Power BI, so I'm not sure what you mean by filter context or how to define parent/siblings.

[lbendlin · Accepted solution]

As soon as they clicked on that single line you will need two separate copies of your data.  One that is impacted directly by the filter context (user choices) and another that is disconnected, and controlled by measure filters.

[SMEisMe]

That's how I got it to work, I was hoping for a better solution than having to load that data twice since it's in the 200k row range.  Thank you!