Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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
@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
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.
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |