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
ashikts
Helper II
Helper II

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

I have a data like  

 

Employeedepartment
ADEVOPS,SYSTEM ADMIN
BSYSTEM ADMIN
CDEVOPS

 

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

Employeedepartment
A1,2
B2
C1

 

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

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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.

12.PNG

Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

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.

12.PNG

Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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

amitchandak
Super User
Super User

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

 

hello sir,

i couldnt achieve the expected result ,.

result came like this 

employeedepartmentcolumnresult
aDEVOPS,SYSTEM ADMIN123
bDEVOPS2
cSystem admin3

 

but i want the answer is like 

 

employeedepartmentcolumnresult
aDEVOPS,SYSTEM ADMIN1,2
bDEVOPS1
cSystem admin2

 

Please help me 

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.