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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tomaszpatrzyk
Frequent Visitor

Specific values from one column inserted to new one

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"

" "

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

1.png

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],", " )

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

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

1.png

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_0-1683727345106.png

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.

@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 )
)
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors