Dax functions for creating a column about the employees and their department

[ashikts]

I have a data like  

Employee	department
A	DEVOPS,SYSTEM ADMIN
B	SYSTEM ADMIN
C	DEVOPS

I want to create table like this  from the source table . 

Employee	department
A	1,2
B	2
C	1

how i can create a column like this with dax ? Please help me ,thanks in advance

[v-jayw-msft · Accepted solution]

Hi @ashikts ,

Check the following steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJxDfMPCNYJjgwOcfVVcHTx9fRTitWJVnICymEIOsM1KMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, department = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"department", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "department", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"department.1", "department.2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if [department.1] = "DEVOPS" then 1 else if [department.1] = "SYSTEM ADMIN" then 2 else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [department.2] = "DEVOPS" then 1 else if [department.2] = "SYSTEM ADMIN" then 2 else null),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column1", "Merged", each Text.Combine({Text.From([Custom], "en-US"), Text.From([Custom.1], "en-US")}, ","), type text)
in
    #"Inserted Merged Column"

Result would be shown as below.

Pbix as attached.

Best Regards,

Jay

[ashikts]

@v-jayw-msft thanks sir .It was a really appreicated help

[amitchandak]

@ashikts , Few steps.

1. Create a table from the department (Table2)

https://www.youtube.com/watch?v=kU2M1LmNvNo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=2

https://www.youtube.com/watch?v=vHuhbvYCiNc&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=3

2. Split column delimiter

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

3. Remove duplicates 

4. Add index column

Then create a new column in dax

concatenatex(filter(Table2, search(table2[deapartment],table1[deapartment],,0) >0),table2[index])

[ashikts]

hello sir,

i couldnt achieve the expected result ,.

result came like this 

employee	department	columnresult
a	DEVOPS,SYSTEM ADMIN	123
b	DEVOPS	2
c	System admin	3

but i want the answer is like 

employee	department	columnresult
a	DEVOPS,SYSTEM ADMIN	1,2
b	DEVOPS	1
c	System admin	2

Please help me