Filter dataset for SELECTEDVALUE() using SEARCH()

[lundande]

I have a data set of 15 million rows that needs to be filtered based on a value that can appear in a any one of 6 seperate columns ( not a great dataset). I concatenated these columns so I can use SEARCH to see if the value is in the row. I need to somehow filter down the results so that only the rows that include the selected value remain. All I know to do is use CALCULATE, which doesn't seem to work...  If anyone can help it would be greatly appreciated!   - I will use DAX or PowerQuery, anything that gets it done!

TEST =
VAR company =
               SELECTEDVALUE ( 'All Columns'[COMPANYNAME] )
RETURN
               CALCULATE (
                         VALUES ( 'All Columns'[Search Column] ),
                         SEARCH ( company, 'All Columns'[Search Column], 1, -1 ) > 0
                 )

Example:

Chung Hung Steel Co.	Protin Imp. Ltd.Chung Hung Steel Co., Ltd.PROTIN IMPORT LTD. 3298 164TH STREET, ATTN: MR. ROB BERNER, SURREY BC CA V3Z 0G5, 604-736-2568Protin Import Ltd.
Hobby Lobby Stores Inc.	Hobby Lobby Stores Inc.Arte Y Confeccion Sociedad AnonimaLIVINGSTON INTERNATIONAL 612 E DALLAS ROAD   SUITE 200, GRAPEVINE TX76051 USHobby Lobby Stores, Inc.

[dvl_ctaul]

You can try something like this:

CALCULATE ( 
SUM ( table[column] ),
CONTAINSSTRING ( 'All Columns'[Search Column], SELECTEDVALUE( Companyname[selected] ) )

Then you can create another table or just use the column that has the company name in it and use it as a slicer. 

[smpa01 · Accepted solution]

@lundande  does this work?

Measure =
VAR company =
    SELECTEDVALUE ( 'All Columns'[COMPANYNAME] )
RETURN
    CALCULATE (
        MAX ( 'All Columns'[COMPANYNAME] ),
        SEARCH ( company, 'All Columns'[Search Column], 1, -1 ) > 0
    )

[lundande]

Looks like MAX is what I was missing! Thank you for the help.

[lundande]

Sadly this is going to be used by many people who work with different companies. They are each going to need the ability to select the company they need. 

[dvl_ctaul]

Are you ever going to want the rows that don't have the specified value? If so I would recommend filtering those out in your query editor before even loading to your dataset. Otherwise all of your calculations that will rely on that filter will need to contain that filter statement. 

You can just use that concatenated column you created and then do a filter by contains in powerquery.