Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JohnSalt
Helper I
Helper I

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 = 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?
 
 
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

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?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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!

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!


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.