cancel
Showing results 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

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)
(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
Super User

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
Blog
4 REPLIES 4
Super User

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
Blog
Helper I

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!

Super User

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
Blog
Helper I

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.

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors