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
jennifer-ddp-ar
Frequent Visitor

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

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?

 

OrganizationApple VendorApple ProductOrange VendorOrange ProductGrape VendorGrape ProductAsset Size

 

Org A

X FarmGalaY FarmNavelZ FarmRed$1M-$5M

 

Org B

V FarmRed DeliciousG FarmValenciaM FarmGreen$10M-$20M

 

Org C

 R FarmGrannyS FarmMandarinT FarmBlack$1M-$5M

 

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

TypeVendorProduct
AppleX Farm

Gala

OrangeY FarmNaval
GrapesZ FarmRed


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

TypeVendorProduct
AppleR Farm

Granny

OrangeS FarmMandarin
GrapesT FarmBlack

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?

1 ACCEPTED SOLUTION

Hi @jennifer-ddp-ar 

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

New data model:

1.png

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.

2.png

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. 

View solution in original post

6 REPLIES 6
v-rzhou-msft
Community Support
Community Support

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
Super User
Super User

@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

 

2020-11-19 (5).png

 

Hi @jennifer-ddp-ar 

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

New data model:

1.png

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.

2.png

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. 

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!

 

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

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.

Top Solution Authors