Specific values from one column inserted to new one

[tomaszpatrzyk]

I have a "SummarySkills" column with different skills in one cell, here is the sample of three cells in one column:

"C#, DAX, French, Italian, JavaScript"

"Italian, ABAP Programming, MS Azure"

"MS Azure, Java, SQL"

I want to create new column "LanguageSkills" which will take from the "SummarySkills" column only values with "French" and "Italian". How should the formula of the column look like?

The output I want to achieve is like that:

"French, Italian"

"Italian"

" "

[tamerj1 · Accepted solution]

Hi @tomaszpatrzyk 
Please refer to attached sample file with the solution

Language Skills = 
VAR String = 'Table'[SummarySkills]
VAR Items = SUBSTITUTE ( String, ", ", "|" )
VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 = INTERSECT ( T2, { "French", "Italian" } )
RETURN
    CONCATENATEX ( T3, [@Item],", " )

[tomaszpatrzyk]

Thank you, that was really helpful! Could you help me with one more thing? I have a filtering dropdown with couple of those languages and I want to display them one by one.
For instance, I want to get rid off "French, German" and keep in dropdown only
"French",
"German".
So when a person X has skills both in French and German I want him to be displayed in table when I select any of those languages.

[tamerj1]

@tomaszpatrzyk

I cannot update the sample file right now as already left the office. So I will try to explain in words. 

In this case you don't need the calculated column. Just manually insert a single column disconnect table that contains the names of the languages (each single language in a row) 'Languages'[Language]
Now you can place the flowing FilterMeasure in the filter pane of the table, select "is not blank" then apply the filter 

Language Skills =
VAR SelectedLanguages =
VALUES ( Languages[Language] )
RETURN
COUNTROWS (
FILTER (
'Table',
VAR String = 'Table'[SummarySkills]
VAR Items =
SUBSTITUTE ( String, ", ", "|" )
VAR Length =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
GENERATESERIES ( 1, Length, 1 )
VAR T2 =
SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 =
INTERSECT ( T2, SelectedLanguages )
RETURN
NOT ISEMPTY ( T3 )
)
)