Validation of Alphanumeric Values Using DAX in Power BI

[InsightSeeker]

Hello,

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

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

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

[marcelsmaglhaes]

@InsightSeeker 

You can create a calculated column in your table as shown below. Feel free to adjust the formula or ask further questions!

AlphanumericCheck =

VAR CurrentValue = 'YourTable'[YourColumn]

VAR Length = LEN(CurrentValue)

VAR CheckChar =

    SUMX(

        GENERATESERIES(1, Length),

        VAR CurrentChar = MID(CurrentValue, [Value], 1)

        RETURN

            IF(

                NOT (

                    (UNICODE(CurrentChar) >= UNICODE("A") && UNICODE(CurrentChar) <= UNICODE("Z")) ||

                    (UNICODE(CurrentChar) >= UNICODE("a") && UNICODE(CurrentChar) <= UNICODE("z")) ||

                    (UNICODE(CurrentChar) >= UNICODE("0") && UNICODE(CurrentChar) <= UNICODE("9"))

                ),

                1,

                0

            )

    )

RETURN

    IF(CheckChar > 0, FALSE, TRUE)

[InsightSeeker]

Hi @marcelsmaglhaes 

I am getting below error when i use the above DAX suggestion.

In some of my cells i dont have any value which might be affecting the result.

Regards

Gaurav

[marcelsmaglhaes]

To handle blank values in your column and prevent GENERATESERIES from crashing, you can add a check for blank values in the VAR Length declaration and handle those cases appropriately

AlphanumericCheck =
VAR CurrentValue = 'YourTable'[YourColumn]
VAR Length = LEN(CurrentValue)
VAR CheckChar =
IF(
ISBLANK(CurrentValue),
0, // No characters to check, so treat as alphanumeric
SUMX(
GENERATESERIES(1, Length),
VAR CurrentChar = MID(CurrentValue, [Value], 1)
RETURN
IF(
NOT (
(UNICODE(CurrentChar) >= UNICODE("A") && UNICODE(CurrentChar) <= UNICODE("Z")) ||
(UNICODE(CurrentChar) >= UNICODE("a") && UNICODE(CurrentChar) <= UNICODE("z")) ||
(UNICODE(CurrentChar) >= UNICODE("0") && UNICODE(CurrentChar) <= UNICODE("9"))
),
1,
0
)
)
)
RETURN
IF(CheckChar > 0, FALSE, TRUE)