Create a calculated measure to check if the data has only letters

[SS_1122]

Hi All,

I am trying to create a measure to calculate the validity of the data. The data is as below :

Name 1	Name 2
Mark	Sa
Lea	Mousk
M9rg89	Page
K**a	Jules
Sasha's	Ovam
M	M
Dee	Dee

 
The calculated field I want to create basically will check the following:
The Name1 should contain only letters and no symbols like (@,£,$,9,10,11 etc) but can contain ( ' ), Second it should not be same as Name2 , if these conditions are met then it is valid otherwise invalid.

Name 1	Name 2	Validity
Mark	Sa	Valid
Lea	Mousk	Valid
M9rg89	Page	Invalid
K**a	Jules	Invalid
Sasha's	Ovam	Valid
M	M	Invalid
Dee	Dee	Invalid

Thanks

[Anonymous]

Hi @SS_1122 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a calculated column.

Validity = var _aaa = UNION(GENERATESERIES(UNICODE("A"),UNICODE("Z")),GENERATESERIES(UNICODE("a"),UNICODE("z")),{UNICODE("'")})
var _a = LEN([Name 1 ])
var _b = GENERATESERIES(1,_a,1)
var _c = ADDCOLUMNS(_b,"Character",MID([Name 1 ],[Value],1))
var _d = ADDCOLUMNS(_c,"Flag",IF(UNICODE([Character]) in SELECTCOLUMNS(_aaa,"Unicode",[Value]),0,1))
return  IF(SUMX(_d,[Flag])=0 && 'Table'[Name 1 ] <> 'Table'[Name 2 ] ,"valid","invalid")

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[SS_1122]

Hey Neeko - Thanks for your reply, I w=am positive that this should work, but I have one query what if I have blank data in between , in that case it shows the error like "The arguments in GenerateSeries function cannot be blank"

So for example if I have something like this 

Name 1	Name 2
Mark	Sa
Lea	Mousk
M9rg89	Page
K**a	Jules
Sasha's	Ovam
M	M
	Poppins
Dee	Dee

The results should  be like 

Name 1	Name 2	Validity
Mark	Sa	Valid
Lea	Mousk	Valid
M9rg89	Page	Invalid
K**a	Jules	Invalid
Sasha's	Ovam	Valid
M	M	Invalid
	Poppins	Blank
Dee	Dee	Invalid

[Anonymous · Accepted solution]

Hi @SS_1122 ,

You can create another calculated column.

Column = IF('Table'[Name 1 ] = BLANK()  ,"BLANK",'Table'[Validity])

Best Regards,

Neeko Tang

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

[SS_1122]

"The arguments in GenerateSeries function cannot be blank" I keep getting this error 

[Thejeswar]

Hi @SS_1122 ,

You will have to handle the NULL as BLANK in the Name1 Column before creating the Calculated Column to known if it is Valid or Invalid record. Also you should be using this New Column where the NULL is handled in the Calculated Column.

Column1 = IF('Table'[Name 1 ] = BLANK()  ,"BLANK",'Table'[Name 1 ])

[Anonymous]

Hi @SS_1122 ,

Please check your data type, refer to my PBIX, is your data different? If the error is still reported, please provide a simplified PBIX file (take care to protect your private data)

You can refer to the following post that may be helpful to you:

Challenge : how to generate a table spliting each ... - Microsoft Power BI Community

Best Regards,

Neeko Tang

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