Data Validation

[gauravnarchal]

Hello

I need help creating 2 separate measures as

1)   If Table 1 Data Check = NUMBER and LEN is <= 9 return TRUE else FALSE.

2)   If Table 1 Data Check = ALPHANUMERIC and LEN is <= 10 return VALID else INVALID.

Table 1

Data Check
0
0
170101
170110
170300
170400
000059C845
000059C846
01-200
2000970
022850W001
063926C001
698
103830
10910
1099079
110
Apple
Banana
Pear

[Anonymous]

Hi @gauravnarchal 

You can refer to the following example.

Create two measures.

IsNum = 
var _createcharacter=SELECTCOLUMNS(GENERATESERIES(UNICODE("A"),UNICODE("Z")),"Character",UNICHAR([Value]))
var _ifcontaincharacter=COUNTROWS(FILTER(_createcharacter,CONTAINSSTRING(MAX('Table'[Data Check]),[Character])))
var _ifnum=COUNTROWS(FILTER('Table',LEN([Data Check])<=9&&[Data Check]<>BLANK()&&_ifcontaincharacter=0&&CONTAINSSTRING([Data Check],"-")=FALSE()))
return _ifnum>0

IsALPHANUMERIC =  
var _createcharacter=SELECTCOLUMNS(GENERATESERIES(UNICODE("A"),UNICODE("Z")),"Character",UNICHAR([Value]))
var _createnum=GENERATESERIES(0,9,1)
var _ifcontainnum=COUNTROWS(FILTER(_createnum,CONTAINSSTRING(MAX([Data Check]),[Value])))
var _ifcontainchara=COUNTROWS(FILTER(_createcharacter,CONTAINSSTRING(MAX([Data Check]),[Character])))
return IF(_ifcontainchara>0&&_ifcontainnum>0&&LEN(MAX([Data Check]))<=10,"VALID ","INVALID")

Best Regards!

Yolo Zhu

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