DAX Formula

[hosea_chumba]

From my customers table, I would like to check the Phone No. column and return output where any of the below is true:

1. Blanks
2. Phone No. digits are not 9
3. Phone No. with special characters

However, my formula below is not working, kindly assist with a better approach.

Invalid Phone No. Numbers =

VAR InvalidCharacters = “abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*()”_+={}[]|\:;\'<>,.?/~`”

RETURN FILTER('Customers',

   OR(

       ISBLANK('Customers'[Phone No.]),

       LEN('Customers'[Phone No.]) <> 9,

       NOT(ISNUMBER(VALUE('Customers'[Phone No.]))),

       COUNTROWS(FILTER(UNICHAR(SEQUENCE(LEN('Customers'[Phone No.]),1,0)), FIND(UNICHAR(SEQUENCE(LEN(InvalidCharacters),1,1)), UNICHAR(SEQUENCE(LEN('Customers'[Phone No.]),1,0)))) > 0),

       COUNTROWS(FILTER(UNICHAR(SEQUENCE(LEN('Customers'[Phone No.]),1,0)), FIND(" ", UNICHAR(SEQUENCE(LEN('Customers'[Phone No.]),1,0)))) > 0),

       COUNTROWS(FILTER(InvalidLengths, LEN('Customers'[Phone No.]) = VALUE)) > 0

   )

)

[v-binbinyu-msft · Accepted solution]

Hi @hosea_chumba ,

Please try to create a calculate column with below dax formula:

Column =
VAR _a = [Phone No.digits]
VAR _b =
    IFERROR ( INT ( _a ), _a )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _a ), _a,
        IFERROR ( INT ( _a ), FALSE () ), _a,
        LEN ( _a ) <> 9, _a
    )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rupak_bi]

Make a calculated column with those three classifications and then count them. It will be simple.