Cell Validation - Should only have 3 Digit number

[gaurav-narchal]

Hello

How can i do cell validation to check if the value updated in the column (Number) is 3 digit number using a measure. Else it should return as a error.

Thanks

Product	Number
Drum	111
Drum	122
Drum	123
Drum	134
Drum	133C
Drum	133
Glass	133
Glass	145
Glass	134A
Plate	123B
Plate	1234
Glass	12344
Drum	12334

[v-shex-msft]

Hi @gaurav-narchal,

AFAIK, Dax expression and calculate fields can calculate with the raw field but not able to direct change raw field values.
In my opinion, I'd like to suggest you enter to 'query edit' side to do custom with the 'replace value' function to replace these no matched values.
Regards,

Xiaoxin Sheng

[gdarakji]

One thing to note is that this is impossible to achieve through a measure. Your requirement to validate the field row-wise. You should use custom columns for row-wise computations. Measures are meant to aggregate values over multiple rows.

[jonoling]

Hi gaurav,

You have two options:

1. Using Power Query, you can add a conditional column to the table:
   • If Text.Length([Number]) = 3 then True else False
2. Alternatively the LEN() function in DAX will also work as a new Calculated Column:
   • Cell Validation Check = IF(LEN(Table1[Number]) = 3, TRUE(), FALSE())

You can then filter by the new column to find cells with only 3 digits.

Hope this helps.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!