Forum Discussion

hosea_chumba's avatar
hosea_chumba
Helper I
2 years ago
Solved

DAX Formula

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

   )

)

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.

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