Count how many times a string from one column appears in a the column of a different table

[westillusinguse]

I need a PowerBi solution. I need to count how many times the string in column "Modes" appears in the table column of "Companies Modes of Transportation". The "Companies Modes of Transpartation" column is a multi select column so I cannot easily do a countrow funtion because it will not be a 1 to 1 match. 

A lot of solutions that I have found have been looking for a specific word but I need to count how many all of the words in the "Modes" column appears.

I could do this in Excel by using the * but I cannot figure out how to replicate something like this in PowerBi for all of the words in the "Modes" column.

The example below should return:

Airplane: 4

Car: 2

Truck: 4

Rail: 6

Boat: 4

[parry2k]

@westillusinguse no worries, click transform data -> select the column that need splitting -> click split by delimited (screen shot below) -> enter delimiter (semicolon) -> click advanced options -> select rows -> click ok

and this will do it.

[parry2k]

@westillusinguse it can be done, but as a best practice, I would recommend splitting the column to the rows in the PQ and from there everything should be super simple. 

[westillusinguse]

If I split it up then it would disrupt other data points that were calculated because it would add additional rows. Is there anyway to incorporate the containstring funtion and pile onto the formula?

Something like: =Countrows(Filter(Table,(ContainsString(Table,Column,"Train"))), Countrows(Filter(Table,(ContainsString(Table,Column,"Train"))), etc.

[gmsamborn]

Hi @westillusinguse 

Would this work as a calculated column in your Modes table?

Count 2 = 
    COUNTROWS(
        FILTER(
            'Companies',
            CONTAINSSTRING( 'Companies'[Company Modes of Transportation], [Mode] )
        )
    )

or 

Count 3 = 
VAR _Mode = [Mode]
RETURN
    CALCULATE(
        COUNTROWS( 'Companies' ),
        CONTAINSSTRING( 'Companies'[Company Modes of Transportation], _Mode )
    )

[westillusinguse]

I can't get either of them to work because PowerBI cannot recognize Mode at the end of the formula.

[gmsamborn]

If you are creating a calculated column in the Modes table, you should have no problem.

Search column for mode of transportation.pbix