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

     

    • MitchTrott's avatar
      MitchTrott
      Frequent Visitor
      2 years ago

    • MitchTrott's avatar
      MitchTrott
      Frequent Visitor
      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!!