Forum Discussion

JohnSalt's avatar
JohnSalt
Helper I
2 years ago
Solved

DAX for NHS Number Check Digit Validity

Been struggling to get a DAX formula to check if an NHS number was valid or not using the final check digit. Managed a working solution so thought I would share:   NHSNoCheck = VAR CheckDigit ...
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi JohnSalt 

    In this case, I would be tempted to treat the number as text, to make the code to extract digits a bit simpler.

    Something like this:

     

    NHS Check Owen = 
VAR NumberText =
    FORMAT ( TRUNC ( 'Table'[NHS No] ), REPT ( "0", 10 ) )
VAR CheckDigit =
    INT ( RIGHT ( NumberText, 1 ) )
VAR Checksum =
    SUMX (
        GENERATESERIES ( 1, 9 ),
        MID ( NumberText, [Value], 1 ) * ( 11 - [Value] )
    )
VAR CalculatedCheckDigit =
    MOD ( 11 - MOD ( Checksum, 11 ), 11 )
RETURN
    IF ( CheckDigit = CalculatedCheckDigit, "Valid", "Invalid" )

     

    You could also consider pushing this upstream to Power Query.

     

    Does this work for you?

OwenAuger's avatar
OwenAuger
Super User
2 years ago

Hi JohnSalt 

In this case, I would be tempted to treat the number as text, to make the code to extract digits a bit simpler.

Something like this:

 

NHS Check Owen = 
VAR NumberText =
    FORMAT ( TRUNC ( 'Table'[NHS No] ), REPT ( "0", 10 ) )
VAR CheckDigit =
    INT ( RIGHT ( NumberText, 1 ) )
VAR Checksum =
    SUMX (
        GENERATESERIES ( 1, 9 ),
        MID ( NumberText, [Value], 1 ) * ( 11 - [Value] )
    )
VAR CalculatedCheckDigit =
    MOD ( 11 - MOD ( Checksum, 11 ), 11 )
RETURN
    IF ( CheckDigit = CalculatedCheckDigit, "Valid", "Invalid" )

 

You could also consider pushing this upstream to Power Query.

 

Does this work for you?