How to filter a table based on a value returned from another filter selection

[jennifer-ddp-ar]

I am a relatively new user. 

I have a dataset that contains organization names, vendors, products, and asset sizes of the organizations.  I have created a table of the vendors and products of one organization, based on a filter for the organization - choose Org A and see list of all vendors and products.  Org A is in the $1M-$5M asset range.  I would have another table showing the vendors and products of peer organizations in the same asset range. I have made this work by creating a separate filter of asset ranges that is narrowed down based on the organization chosen in the first filter - but the user has to click on the asset range that shows up after Org A is chosen in the first filter for the peer organization table to populate.  Can I somehow automatically filter the peer organization table based on the asset range of Org A without the user needing to click on an asset range?

Organization	Apple Vendor	Apple Product	Orange Vendor	Orange Product	Grape Vendor	Grape Product	Asset Size
Org A	X Farm	Gala	Y Farm	Navel	Z Farm	Red	$1M-$5M
Org B	V Farm	Red Delicious	G Farm	Valencia	M Farm	Green	$10M-$20M
Org C	R Farm	Granny	S Farm	Mandarin	T Farm	Black	$1M-$5M

Choose Org A in first filter, table shows Org A's Vendors and Products:

Type	Vendor	Product
Apple	X Farm	Gala
Orange	Y Farm	Naval
Grapes	Z Farm	Red

Since Org A is in the $1M-$5M asset group, I would like for the Peer Group table to show:

Type	Vendor	Product
Apple	R Farm	Granny
Orange	S Farm	Mandarin
Grapes	T Farm	Black

As well as other vendors and products from that asset group.

Can I do this without the user having to click on a separate filter showing the asset group associate with Org A?

[v-rzhou-msft]

Hi @jennifer-ddp-ar 

Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[amitchandak]

@jennifer-ddp-ar , It needed some power Query tranformation. Unpivot - > Split -> Pivot .

Then use in visual. Please find the attached file after signature 

Refer unpivot: https://www.youtube.com/watch?v=2HjkBtxSM0g

split : https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Pivot : https://www.youtube.com/watch?v=oKByyI09Bno

[jennifer-ddp-ar]

[v-rzhou-msft · Accepted solution]

Hi @jennifer-ddp-ar 

I transform your data model by pivot, unpivot and split column.

New data model:

You can achieve your goal by building an unrelated organization table and measures.

If you want to build a table visual, you may need to calcualte the size of organization you selected and then filter the columns (vendor and product) by size.

Build an unrelated organization table:

Org = VALUES('Table (2)'[Organization])

Measures:

Size1 = 
VAR _SELECTORG =
    SELECTEDVALUE ( Org[Organization] )
VAR _Size =
    CALCULATE (
        MAX ( 'Table'[Size] ),
        FILTER ( ALL('Table'), 'Table'[Organization] = _SELECTORG )
    )
RETURN
    _Size

Filter = IF(MAX('Table'[Size])=[Size1],1,0) 

Then build a new table by type ,vendor and product columns. Add filter measure into the filter field in this table visual and set it to show items when values =1.

Result is as below.

Or you can try matrix visual as well, you may refer to my pbix file to build a matrix visual to achieve your goal only by adding measures into value field.

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[jennifer-ddp-ar]

This is perfect and worked well.  Now I need to look at my real dataset and see which one of your solutions works best.  I had to manually unpivot some of the information my first time around and need to figure out how to do all of the pivots in Power BI.  Thank you for your help!

[jennifer-ddp-ar]

Thank you.  I have it all unpivoted and am able to create the tables you show.  What I can't figure out how to do is have the table for Org A, and without having to use another filter, automatically have a table for Org C because it is in the same asset range as Org A.  I've made changes to your PBIX to show you, but not sure how to attach...