Text search count of rows based on filtered values

[HoDASRG]

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

[HoDASRG]

Many thanks Jianbo, you are a God amongst men!!

[v-jianboli-msft · Accepted 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.