Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
" "
Solved! Go to 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],", " )
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],", " )
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.
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 )
)
)
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |