Convert the Product column value to a specific code with the corresponding quantity

[nghilttran]

Values in the Product column and corresponding code:
Products with value 1 will have result code: CKD-CN- (quantity: 1)
Products with value 2 will have result code: CKD-CNHT- (quantity: 2)
Products with value 3 will have result code: CSN-CN- (quantity: 3)
Products with value 4 will have result code: CSN-CNHT- (quantity: 4)
Products with value 5 will have the result code: ĐT-IT-CN- (quantity: 5)
Products with value 6 will have the result code: ĐT-IT-CNHT- (quantity: 6)
Products with value 7 will have result code: LTTP- (quantity: 7)
Products with value 8 will have result code: DM- (quantity:8)
Products with value 9 will have result code: DG- (quantity: 9)
Code column after conversion
Each value in the Product column will be converted to a specific code, along with the corresponding quantity, as listed above.

[Joseph_Seddik]

Have a new virtual column with app formula:

SWITCH([Products], 
  1, "CKD-CN- (quantity: 1)",
  2, "CKD-CNHT- (quantity: 2)",
  .
  .
  .
  9, "DG- (quantity: 9)",
  "Unknown"
)

[nghilttran]

That's what I mean

My formula is:
IFS(
[Products] = 1, CONCATENATE("CKĐ-CN-", TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 1))+1)),
[Products] = 2, CONCATENATE("CKĐ-CNHT-", TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 2))+1)),
[Products] = 3, CONCATENATE("CSN-CN-", TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 3))+1)),
[Products] = 4, CONCATENATE("CSN-CNHT-", TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 4))+1)),
[Products] = 5, CONCATENATE("ĐTCNTT-CN-", TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 5))+1)),
[Products] = 6, CONCATENATE("ĐTCNTT-CNHT-", TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 6))+1)),
[Products] = 7, CONCATENATE("LTTP-", TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 7))+1)),
[Products] = 8, CONCATENATE("DM-", TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 8))+1)),
[Products] = 9, CONCATENATE("DG-", TEXT(COUNT(SELECT(Test_Record[Products], [Products] = 9))+1)),
TRUE,""
)

This is Result:

[Joseph_Seddik · Accepted solution]

SWITCH([Products], 
  1, "CKD-CN-" &
       COUNT(FILTER("Test_Record", [Products] = 1) - LIST([_ThisRow])) + 1,
  2, "CKD-CNHT-" &
       COUNT(FILTER("Test_Record", [Products] = 2) - LIST([_ThisRow])) + 1,
  .
  .
  .
  9, "DG-" &
       COUNT(FILTER("Test_Record", [Products] = 9) - LIST([_ThisRow])) + 1,
  "Unknown"
)

[Joseph_Seddik · Accepted solution]

SWITCH([Products], 
  1, "CKD-CN-" &
       COUNT(FILTER("Test_Record", [Products] = 1) - LIST([_ThisRow])) + 1,
  2, "CKD-CNHT-" &
       COUNT(FILTER("Test_Record", [Products] = 2) - LIST([_ThisRow])) + 1,
  .
  .
  .
  9, "DG-" &
       COUNT(FILTER("Test_Record", [Products] = 9) - LIST([_ThisRow])) + 1,
  "Unknown"
)

[nghilttran]

This formula is correct but I have used security filter according to display data of each user and it is affected by security filter. The formula will count according to each user record (Add By column). How to not be affected by security filter