Forum Discussion
Anonymous
6 years agoNot applicable
Data validity and syntax
Hello All, I am building a data quality dashboard. One column is of PAN number. I to check its validity, I need to apply 3 rules and finally calculate validity % based on that. The rules are: 1) Fir...
- Anonymous6 years ago
I tried to follow the specifications of the following link:
https://en.wikipedia.org/wiki/Permanent_account_number
Test if this function is right for you.
let checkPAN=(PAN as text) as logical => let charsPAN=Text.ToList(PAN), fourthChars=Text.ToList("ABCEFGHLJPT"), len=Text.Length(PAN)=10, upper=Text.Upper(PAN)=PAN, btwAAAZZZ="AAA"<=Text.Start(PAN,3) and Text.Start(PAN,3)<="ZZZ", fourthChar=List.Contains(fourthChars,charsPAN{3}), fifthChar="A" < charsPAN{4} and charsPAN{4} <"Z", digitsPAN="0000"<Text.Range(PAN,5,4) and Text.Range(PAN,5,4)<"9999", lastCharPAN="A" < charsPAN{9} and charsPAN{9} <"Z" in len and upper and btwAAAZZZ and fourthChar and digitsPAN and lastCharPAN in checkPAN - 6 years ago
Hi Anonymous ,
You could refer to the following DAX:
Column = VAR a = LEFT ( 'Table'[Column1], 5 ) VAR b1 = MID ( 'Table'[Column1], 1, 1 ) VAR b2 = MID ( 'Table'[Column1], 2, 1 ) VAR b3 = MID ( 'Table'[Column1], 3, 1 ) VAR b4 = MID ( 'Table'[Column1], 4, 1 ) VAR b5 = MID ( 'Table'[Column1], 5, 1 ) VAR b = ISERROR ( VALUE ( b1 ) ) && ISERROR ( VALUE ( b2 ) ) && ISERROR ( VALUE ( b3 ) ) && ISERROR ( VALUE ( b4 ) ) && ISERROR ( VALUE ( b5 ) ) VAR c = NOT ( ISERROR ( VALUE ( MID ( 'Table'[Column1], 6, 4 ) ) ) ) VAR d = EXACT ( UPPER ( RIGHT ( 'Table'[Column1], 1 ) ), RIGHT ( 'Table'[Column1], 1 ) ) VAR e = ISERROR ( VALUE ( RIGHT ( 'Table'[Column1], 1 ) ) ) VAR f = CALCULATE ( COUNTA ( 'Table'[Column1] ), ALLEXCEPT ( 'Table', 'Table'[Column1] ) ) = 1 VAR g = b4 IN { "P", "C", "H", "F", "A", "T", "B", "L", "J", "G" } RETURN IF ( EXACT ( UPPER ( a ), a ) && ISTEXT ( a ) && b && c && d && e && f && g, 1, 0 )
Anonymous
6 years agoNot applicable
I tried to follow the specifications of the following link:
https://en.wikipedia.org/wiki/Permanent_account_number
Test if this function is right for you.
let
checkPAN=(PAN as text) as logical =>
let
charsPAN=Text.ToList(PAN),
fourthChars=Text.ToList("ABCEFGHLJPT"),
len=Text.Length(PAN)=10,
upper=Text.Upper(PAN)=PAN,
btwAAAZZZ="AAA"<=Text.Start(PAN,3) and Text.Start(PAN,3)<="ZZZ",
fourthChar=List.Contains(fourthChars,charsPAN{3}),
fifthChar="A" < charsPAN{4} and charsPAN{4} <"Z",
digitsPAN="0000"<Text.Range(PAN,5,4) and Text.Range(PAN,5,4)<"9999",
lastCharPAN="A" < charsPAN{9} and charsPAN{9} <"Z"
in len and upper and btwAAAZZZ and fourthChar and digitsPAN and lastCharPAN
in checkPAN