Column format check

[MitchTrott]

Hi all, 

I need a custom column to check the values of another column are of the correct format. the format being ABCD1234. If the format is anything different i'd like it to be flagged. I cannot seem to work out the DAX needed to do this. 

For example if the entry in the column reads ABCDE1234 or ABCD1234A etc then they need to be flagged

Thanks in advance

[v-xiandat-msft]

Hi @MitchTrott ,

Below is my table:

The following DAX might work for you:

Check Format = 
VAR text1 = [Column1]
VAR check1 = LEN(text1) = 8
VAR check2 = EXACT(LEFT(text1, 4), UPPER(LEFT(text1, 4)))
VAR check3 = ISNUMBER(VALUE(RIGHT(text1, 4)))
RETURN IF(check1 && check2 && check3, "Correct Format", "Incorrect Format")

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MitchTrott]

That looks great, thank you although im getting an error: Cannot convert value '' of type Text to type Number.

Also, in another table, cannot convert value '852A' of type text to type number

[v-xiandat-msft]

Hi @MitchTrott ,

My column is the text type.Can you share a screenshot of your error, please?

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MitchTrott · Accepted solution]

Hi, I have fixed the error with an added IFERROR clause.

Check 4x4 Format =

VAR text1 = [4x4]

VAR check1 = LEN(text1) = 8

VAR check2 = EXACT(LEFT(text1, 4), UPPER(LEFT(text1, 4)))

VAR check3 = ISNUMBER(IFERROR(VALUE(RIGHT(text1, 4)), BLANK()))

RETURN IF(check1 && check2 && check3, "Correct Format", "Incorrect Format")

 

Thank you for your help!!

[MitchTrott]

[Vallirajap]

Hii @MitchTrott,

Try this for the above scenario.

Column2 = If(LEN(SELECTEDVALUE('Table'[Column1])) = 9, "TRUE", "FALSE")  // Measure

Column = If(LEN('Table'[Column1]) = 9, "TRUE", "FALSE") // Column

Both return a true or false output. Instead of True or False, you can use any value to create a flag.

Did I answer your question?

Mark my post as a solution, this will help others...!

Hit the kudo also,

Thank you.

[MitchTrott]

Thanks for your solution. This is great to check the Length but it wont flag if the input is 12345678 or A1234567 etc. Thanks

[Idrissshatila]

@MitchTrott ,

so the correct format is having an id with the first 4 characters as ABCD and the last 4 characters as 1234 ?

they should all be like that ? 

or the correct format is the first 4 characters as text and the last 4 characters as numbers ?

[MitchTrott]

yes format should be 4 capital letters followed by 4 numbers. Thanks