topic Re: Specific values from one column inserted to new one in DAX Commands and Tips

Specific values from one column inserted to new one

Anonymous — Wed, 10 May 2023 12:53:59 GMT

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"

" "

Re: Specific values from one column inserted to new one

tamerj1 — Wed, 10 May 2023 13:31:59 GMT

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

Re: Specific values from one column inserted to new one

Anonymous — Wed, 10 May 2023 14:07:02 GMT

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.

Re: Specific values from one column inserted to new one

tamerj1 — Wed, 10 May 2023 14:37:32 GMT

Anonymous

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