Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
HoDASRG
New Member

Text search count of rows based on filtered values

Hi there, hoping someone can help please.

 

My data is presented as follows:

 

Products

 

Unique Product ReferenceProduct CategoryMaterials
1234HatCotton, Polyester, Wool
1526TrousersCotton
1122HatWool
1948ScarfLinen, Cotton
1458HatPolyester
1874ScarfLinen

 

A separate table has the combination of products and materials that we want are focusing on analysing

 

e.g 

 

Product_Material

 

Product CategoryMaterial
HatLinen
HatWool
HatCotton
ScarfLinen
TrousersWool

 

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

 

MaterialCount of products containing materialTotal Products in category% of products with material in
Linen030%
Wool2367%
Cotton1333%

 

I hope that all makes sense.

 

Thank you in advance for your help

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

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

vjianbolimsft_2-1666320988435.png

 

Create a Table:

vjianbolimsft_1-1666320970045.png

 

Final output:

vjianbolimsft_0-1666320835106.png

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.

 

View solution in original post

2 REPLIES 2
HoDASRG
New Member

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

v-jianboli-msft
Community Support
Community Support

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

vjianbolimsft_2-1666320988435.png

 

Create a Table:

vjianbolimsft_1-1666320970045.png

 

Final output:

vjianbolimsft_0-1666320835106.png

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.

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.