Forum Discussion

InsightSeeker's avatar
InsightSeeker
Icon for Helper III rankHelper III
2 years ago

Validation of Alphanumeric Values Using DAX in Power BI

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
[email protected]
16546854
464213
1641654
564646
646464
546498
46464
64646464
646486
64646546
ASW4646464
AW54646
AQ464654
AD446464
AS4646DD
ASDSWSD
KLHH
OJOJ
POJPOJP
Need Help
Show and Tell

3 Replies

  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      InsightSeeker
      Icon for Helper III rankHelper III
      2 years ago

      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's avatar
        marcelsmaglhaes
        Icon for Super User rankSuper User
        2 years ago

        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)