Forum Discussion
Help Needed: DAX Formula to Validate Alphanumeric Values
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 |
| [email protected] |
| 16546854 |
| 1641654 |
| 564646 |
| 646464 |
| 546498 |
| 64646464 |
| 646486 |
| 64646546 |
| ASW4646464 |
| AW54646 |
| AQ464654 |
| AD446464 |
| AS4646DD |
| ASDSWSD |
| OJOJ |
| POJPOJP |
You can add a special case for blanks but they didn't cause an issue in my testing.
3 Replies
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 _ResultHi 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
You can add a special case for blanks but they didn't cause an issue in my testing.
