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?

  • JohnSalt's avatar
    JohnSalt
    Helper I
    2 years ago

    Thanks Owen,

    Nice! Thats neater and much easier to read.

    Wasnt sure if text conversion created much of an overhead compared to mathematical extraction of digits but theres no lag when using both so tempted to use your code.

    Just needs the addition of conversion of a calculated check digit from 11 to 0, 11 is treated as 0 and 10 is invalid so no conversion needed as it will never equal the check digit.

    Re pushing up to Power Query M, Yes am doing that too! NHS No gets used in so many reports from so many sources that im looking to collate a set of the slickest possible methods in DAX, M, R and further upstream still in SQL.

    In one of my previous jobs I saw things like NHS Number of all zeros, sequential numbers, a concatonation of NHS number and date etc etc bleeding through into the NHS Number field, so it cant be checked enough!

    • 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.