DAX for NHS Number Check Digit Validity

[JohnSalt]

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 = ROUNDDOWN(('Table'[NHS No]-(ROUNDDOWN('Table'[NHS No],-1))),0)

VAR CalcTable = ADDCOLUMNS(GENERATESERIES(1,9),"xsum",

    (ROUNDDOWN(('Table'[NHS No]-(ROUNDDOWN('Table'[NHS No],-([Value]+1))))/power(10,[Value]),0)) *([Value]+1))

VAR Result = 11-MOD(SUMX(CalcTable,[xsum]),11)

VAR CalculatedCheckDigit = IF(Result=11,0,Result)

RETURN

IF (CheckDigit=CalculatedCheckDigit,"Valid","Invalid")

Can this be improved upon to make it a bit slicker?

[OwenAuger · Accepted solution]

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]

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]

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]

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.