Validate numeric values and 8 digits length

[gauravnarchal]

Hello - I need help to create a measure to check if the values in the below table are all numeric and 8 digits then return ok else error. 

Table 1

Serial No
00809898
00810098
00810298
00810498
00810698
00810898
'00811098
00811298
CGH889
BGD
ISR
"909K

[v-yangliu-msft · Accepted solution]

Hi  @gauravnarchal ,

If you want to use meausre, please refer to what @mahoneypat  said and rewrite the formula to look like this:

Measure = 
IF(
    LEN(MAX('Table1'[Serial No])) = 8 
    &&  
    IFERROR(VALUE(MAX('Table1'[Serial No])), 0)>0,"Valid", "Error")

You can also use the m language of Power Query

Here are the steps you can follow：

1. Enter the Power query through Transform data and select Add Column – Custom Column

2. Enter the code

if
List.AllTrue({Text.Length([Serial No]) = 8 ,Value.Is(Value.FromText([Serial No]), type number)})
then
"corrert"
else
"error"

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCwMLC0sLRQitUBcwwNDJA5RsgcE2SOGTIHZkBMqYGBkTlIyBDZGEOYMc7uHhYWlmCmk7sLmPYMDgLTlgaW3kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial No" = _t]),
    Flag = Table.AddColumn(Source, "8 digits", each Text.Length([Serial No])=8 and not (try Number.From([Serial No]))[HasError])
in
    Flag

[mahoneypat]

You can use a DAX column expression like this.  Replace Data with your actual table name.

IsValid = IF(LEN(Data[Serial No]) = 8 && IFERROR(VALUE(Data[Serial No]), 0)>0, "Valid", "Error")

Pat