Count Number of Matches in all rows

[OSin]

Hi All,

I have searched for a solution to this question but have had no luck so far.

I would like to calculate the number of string occurences in all rows of a certain column, based on a string in the current row of a seperate column. For example:

BOM

COMPONENT2,COMPONENT3,COMPONENT3

COMPONENT1,COMPONENT5,COMPONENT3

COMPONENT2,COMPONENT1

COMPONENT1,COMPONENT3

COMPONENT1,COMPOMENT2,COMPONENET3,COMPONENT4

SKU	SKU in BOM
COMPONENT 1	4
COMPONENT 2	3
COMPONENT 3	5
COMPONENT 4	1
COMPONENT 5	1

Currently, I can count the rows where the string occurs using DAX:

SKU in BOM = countrows(filter('Data',find(earlier('Data'[SKU]),'Data'[BOM],,0)>0))

But this gives just a count of the rows, wheras I want to get the total number of occurances.

I have seen a suggestion of using Text.Split to get the number of occurences, but I'm not sure how to reference the "SKU" Column as the delimiter and how to iterate over every row and sum, rather than just the current row.

Edit: The main issue I have is that each component can be present more than once in any given row. Editted the tables to clarify.

Any help would be appreciated.

[CR]

Hi @OSin

Could you please tell us which values you need in your column instead of them you display in the column SKU in BOM ? It will ease the reply.

Regards,

CR

[OSin]

@CRI would like to get the column "SKU in BOM", I.e. the number of times that the sting "SKU" occurs in the String "BOM", summed for all rows.

[Zubair_Muhammad]

@OSin

Try this calculated column

Column =
COUNTROWS ( FILTER ( Table1, SEARCH ( EARLIER ( [SKU] ), [BOM], 1, 0 ) > 0 ) )

[OSin]

@Zubair_Muhammad  Unfortunately that just gives the number of rows with matches as I mentioned in the opening post. I also need to count the number of occurences within those rows.

[CR · Accepted solution]

I personally duplicate the table and I open the query Editor to split the data into rows

Then I can apply the DAX formula proposed by @Zubair_Muhammad

Then you can use this table in a visual rapidly.

I know it is only to save you because a DAX formula should solve it but I don't know it !

Regards,

CR

[OSin]

@CRI Ended up using your solution as I could combine it with an additional column "Quantity" multiple the component by the quantity purchased for a total column.

[OSin]

@CR   Yep, I've tried this way but unfortunately the real data has 1000+ SKU values and up to 30 components per BOM, so it isnt feasable to do this. There are also values in other coulmns that I don't want duplicated.

Thanks

[Zubair_Muhammad]

@OSin@CR

This formula works with sample data

Column =
VAR mySKU = [SKU]
VAR AllBOM =
    CONCATENATEX ( Table1, [BOM], "," )
VAR myBOM =
    SUBSTITUTE ( AllBOM, ",", "|" )
VAR temp =
    ADDCOLUMNS (
        GENERATESERIES ( 1, PATHLENGTH ( myBOM ) ),
        "BOMB", TRIM ( PATHITEM ( myBOM, [Value] ) )
    )
RETURN
    COUNTROWS ( FILTER ( temp, [BOMB] = mySKU ) )

[OSin]

This seems to work at first glance, but with my data for some values of SKU I am getting a lower result than when using

Column =
COUNTROWS ( FILTER ( Table1, SEARCH ( EARLIER ( [SKU] ), [BOM], 1, 0 ) > 0 ) ) :

Which means some results are being excluded from this formula?  Is there an upper limit to CONCATENATEX that could be limiting the returned count?

[Zubair_Muhammad]

@OSin@CR

File attached as well

[CR]

@Zubair_Muhammad you are a SUPER USER !!!!

I keep it in my long list of solutions !!!!

CR

[CR]

Strange because I proposed you this solution because you have a lot of data.

I proceed like this by duplicating the table first to do what I want after. It doesn't affect PBI capacities.

Just for information, I could propose you to Group By the data after the split into Rows.

It gives you directly what you need without using DAX formula at all (all is done in Query Editor).

Anyway, I hope somebody will provide you DAX formula;

Regards,

CR