Forum Discussion

MitchTrott's avatar
MitchTrott
Frequent Visitor
2 years ago
Solved

Column format check

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

Need Help
Tips and Tricks
  • MitchTrott's avatar
    MitchTrott
    2 years ago

    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!!

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      MitchTrott
      Frequent Visitor
      2 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.

         

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    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's avatar
      MitchTrott
      Frequent Visitor
      2 years ago

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

  • Vallirajap's avatar
    Vallirajap
    Resolver III
    2 years ago

    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's avatar
      MitchTrott
      Frequent Visitor
      2 years ago

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