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

You're welcome 🙂

Regarding the calculated check digit, I had intended the "modulo 11" calc to convert 11 to zero (and leave 10 unchanged) in this line:

 

VAR CalculatedCheckDigit =
    MOD ( 11 - MOD ( Checksum, 11 ), 11 )

 

but please verify if it works as intended.

 

All the best with exploration on other methods! 🙂

 

I hadn't encountered NHS Nos before so very interesting to learn a bit about them!

JohnSalt's avatar
JohnSalt
Helper I
2 years ago

In the spirit of less is more I brought in the NHS no as string and reduced the final code down to just a couple of lines

 

NHS No Check =
VAR Checksum =
    SUMX ( GENERATESERIES ( 1, 9 ), MID ( [NHS No], [Value], 1 ) * ( 11 - [Value] ) )
RETURN
    IF ( INT ( MID ( [NHS No], 10, 1 ) ) = MOD ( 11 - MOD ( Checksum, 11 ), 11 ), "Valid", "Invalid" )

 

But then found that it didnt seem to matter if it was string or not, was expecting an error but seems to work either way.