Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
  • Anonymous's avatar
    Anonymous
    6 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

     

     

  • v-eachen-msft's avatar
    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
        )