Help Needed: DAX Formula to Validate Alphanumeric Values

[InsightSeeker]

Hello - I need help with validating values using DAX to determine if they contain only alphanumeric characters.

If the value contains only alphanumeric characters, it should return TRUE; otherwise, it should return FALSE.

Value
gtest@gmail.com
16546854
1641654
564646
646464
546498
64646464
646486
64646546
ASW4646464
AW54646
AQ464654
AD446464
AS4646DD
ASDSWSD
OJOJ
POJPOJP

[AlexisOlson]

DAX is not a very good tool for this. I'd recommend doing it in Power Query or further upstream (e.g. SQL).

It is possible though. You can iterate through all the characters and see if each one a letter or number.

Something like this as a calculated column:

IsAlphanumeric =
VAR _AlphaNum = "abcdefghijklmnopqrstuvwxyz0123456789"
VAR _L = LEN ( Table1[Value] )
VAR _Series =
    SELECTCOLUMNS ( GENERATESERIES ( 1, _L, 1 ), "@N", [Value] )
VAR _AllChars =
    ADDCOLUMNS ( _Series, "@Char", MID ( Table1[Value], [@N], 1 ) )
VAR _AlphaNumChars =
    FILTER ( _AllChars, CONTAINSSTRING ( _AlphaNum, [@Char] ) )
VAR _Result =
    ( L = COUNTROWS ( _AlphaNumChars ) )
RETURN
    _Result

[InsightSeeker]

Hi @AlexisOlson - I am getting the following error. Could it be because some of the cells are blank? How can this be handled?

The arguments in GenerateSeries function cannot be blank.

Thanks

[AlexisOlson · Accepted solution]

You can add a special case for blanks but they didn't cause an issue in my testing.