Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

creating categories based on first character of text field

Hi,

 

I have a database column which contains workorder numbers (format text - contains alpha numeric characters). I want to create categories based on the first character of the workorder which can be the number 1 to 9. The workorder numbers which start with 2 and 5 are one group, the numbers which start with 3,5, and 7 are the second group. The numbers with 8 and 9 are 2 separate groups. The remaining numbers (1 and 😎 are leftovers and are valid when the not belonging to the first groups.

 

Tried with if-or calculation but I get the message that I only can use 2 nested or's. Tried also SWTICH function but not succesfully.

 

Can somebody help?

 

Thanks in advance.

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Anonymous,

Create the following column in your table.

GROUP = IF(LEFT(Table[workoder],1) IN {"2","5"},"G1",IF(LEFT(Table[workoder],1) IN {"3","4","7"},"G2",IF(LEFT(Table[workoder],1)="6","G3",IF(LEFT(Table[workoder],1)="9","G4","G5"))))

1.PNG


If the above DAX doesn't work, please share sample data of your table here.

Regards,
Lydia

View solution in original post

1 REPLY 1
Anonymous
Not applicable

@Anonymous,

Create the following column in your table.

GROUP = IF(LEFT(Table[workoder],1) IN {"2","5"},"G1",IF(LEFT(Table[workoder],1) IN {"3","4","7"},"G2",IF(LEFT(Table[workoder],1)="6","G3",IF(LEFT(Table[workoder],1)="9","G4","G5"))))

1.PNG


If the above DAX doesn't work, please share sample data of your table here.

Regards,
Lydia

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.