Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi there, hoping someone can help please.
My data is presented as follows:
Products
Unique Product Reference | Product Category | Materials |
1234 | Hat | Cotton, Polyester, Wool |
1526 | Trousers | Cotton |
1122 | Hat | Wool |
1948 | Scarf | Linen, Cotton |
1458 | Hat | Polyester |
1874 | Scarf | Linen |
A separate table has the combination of products and materials that we want are focusing on analysing
e.g
Product_Material
Product Category | Material |
Hat | Linen |
Hat | Wool |
Hat | Cotton |
Scarf | Linen |
Trousers | Wool |
I have looked at the following example https://community.powerbi.com/t5/Desktop/Dynamic-filtering-slicing-if-text-contains/m-p/425504 which gets close to what I need but I can't quite get that to work for my needs.
What I really need is an output along the following after having selected a product:
Filter on Hats in the Product_Material table and display the following
Material | Count of products containing material | Total Products in category | % of products with material in |
Linen | 0 | 3 | 0% |
Wool | 2 | 3 | 67% |
Cotton | 1 | 3 | 33% |
I hope that all makes sense.
Thank you in advance for your help
Solved! Go to Solution.
Hi @HoDASRG ,
Please try:
Count of products containing material =
VAR _a =
ADDCOLUMNS (
FILTER (
'Products',
[Product Category] = SELECTEDVALUE ( Product_Material[Product Category] )
),
"FlAG", CONTAINSSTRING ( 'Products'[Materials], MAX ( 'Product_Material'[Material] ) )
)
VAR _b =
COUNTX ( FILTER ( _a, [FlAG] = TRUE () ), [Unique Product Reference] )
RETURN
IF ( ISBLANK ( _b ), 0, _b )
Total Products in categor = CALCULATE(COUNT(Products[Unique Product Reference]),FILTER('Products',[Product Category]=SELECTEDVALUE('Product_Material'[Product Category])))
% of products with material in = DIVIDE([Count of products containing material],[Total Products in categor])
Then use 'Product_Material'[Product] create a slicer
Create a Table:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Many thanks Jianbo, you are a God amongst men!!
Hi @HoDASRG ,
Please try:
Count of products containing material =
VAR _a =
ADDCOLUMNS (
FILTER (
'Products',
[Product Category] = SELECTEDVALUE ( Product_Material[Product Category] )
),
"FlAG", CONTAINSSTRING ( 'Products'[Materials], MAX ( 'Product_Material'[Material] ) )
)
VAR _b =
COUNTX ( FILTER ( _a, [FlAG] = TRUE () ), [Unique Product Reference] )
RETURN
IF ( ISBLANK ( _b ), 0, _b )
Total Products in categor = CALCULATE(COUNT(Products[Unique Product Reference]),FILTER('Products',[Product Category]=SELECTEDVALUE('Product_Material'[Product Category])))
% of products with material in = DIVIDE([Count of products containing material],[Total Products in categor])
Then use 'Product_Material'[Product] create a slicer
Create a Table:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
70 | |
70 | |
38 | |
28 | |
26 |
User | Count |
---|---|
97 | |
88 | |
59 | |
43 | |
40 |