Forum Discussion
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:
- Blanks
- Phone No. digits are not 9
- 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
)
)
- Anonymous2 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
- AnonymousNot 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. - Rupak_biSuper User
Make a calculated column with those three classifications and then count them. It will be simple.