How to Grouping Some username (Categorize)

[saranp780]

I have data in the table below.

User Name

ACTSUP003

ACTSUP004

ACTSUP004.1

ACTSUP004.2

JVSKACT001

JVSKACT002

JVSKACT003

JCSKACT004

JVSKCM001

JVSKCM002

JVSKCM003

JVSKCO001

JVSKCOE001

PJACTOF1402B

PJACTOF1503A

PJACTOF1602

PJACTOF1602A

PJACTOF1602B

PURMGR001

PURMGR001.1

PURMGR002

PUROFFLINE

Now, I want to group the username in the next column such as table below.

User Name	Category
ACTSUP003	ACT
ACTSUP004	ACT
ACTSUP004.1	ACT
ACTSUP004.2	ACT
JVSKACT001	ACT
JVSKACT002	ACT
JVSKACT003	ACT
JCSKACT004	ACT
JVSKCM001	CM
JVSKCM002	CM
JVSKCM003	CM
JVSKCO001	CO
JVSKCOE001	OE
PJACTOF1402B	ACT
PJACTOF1503A	ACT
PJACTOF1602	ACT
PJACTOF1602A	ACT
PJACTOF1602B	ACT
PURMGR001	PUR
PURMGR001.1	PUR
PURMGR002	PUR
PUROFFLINE	PUR

Who knows how DAX can I use? Please help me how to make category using the keywords from data.

Best regard.

[BalaVenuGopal]

@saranp780 on which basis are you making these categories for exampe 

1) ACTSUP003  First 3 Letters ACT Which is fine 

2) JVSKCM001 how did you get CM (No Clue)

3) PJACTOF1402B how did you get ACT (No Clue) 

.

.

.

IF you can give your logic so that we can use String functions to get the requierd out put you can add as custom column and we will use power query to get this done .

Please give me exact logic and exact data so that we can resolve this.!!!!!!!!!1

[saranp780]

2) JVSKCM001 how did you get CM (No Clue) ---> CM is the keyword JVSKCM001

3) PJACTOF1402B how did you get ACT (No Clue) ---> ACT is the keyword PJACTOF1402B

[BalaVenuGopal]

Hi @saranp780

From this conversation i undetstood  is that you have mapping for username and keyword. In this case you can do the following.

1) Create table from model (static table) and create two columns (UserName and Key)

2)  Insert your UserName and Key into this table (Unique values)

3) Then join this table with your transactional table .(Join column UserName)

4) Create custom column [use related function to get key from the table you created] in the transactional table.

Then you will get Username and key in same table .

Hope this will get what you need please mark this as solution if you got the expected result.

Regards,

Venu Gopal.

[v-shex-msft]

HI @saranp780,

Based on your sample data, I haven't found any logic about how to choose specific characters as the category.
Can you please share more detail about this?

Regards,

Xiaoxin Sheng

[saranp780]

I've thought that I can put it together by using the keyword such as ACTSUP001 , ACTSUP002 I will put them in ACT group because they have "ACT" in their text. But I don't know DAX that I can use.