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 )
v-eachen-msft
Community Support
6 years agoHi 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
)