Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
5 years ago
Solved

Cell Validation

Dear All

 

I want to create a measure to find if any cell value is having more than 1 character (A-Z), then it should return that as an error.

 

Digits are also not accepted as a value and should return that as an error. Only valid value in the cell should be a single character.

 

Indicator
A
B
C
D
DD
EE
2
3
E

 

Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    5 years ago

    Hi gauravnarchal ,

     

    Try the following measure:

     

    Measure = 
SWITCH (
    TRUE ();
    ISERROR ( MOD ( SELECTEDVALUE ( 'Table'[Indicator] ); 2 ) ) = FALSE; "ERROR";
    LEN ( SELECTEDVALUE ( 'Table'[Indicator] ) ) > 1; "ERROR";
    "OK"
)

     

     

  • MFelix's avatar
    MFelix
    5 years ago

    Hi gauravnarchal ,

     

    Try the following code:

    Measure = 
SWITCH (
    TRUE ();
    ISERROR ( MOD ( SELECTEDVALUE ( 'Table'[Indicator] ); 2 ) ) = FALSE; "ERROR";
    SELECTEDVALUE ( 'Table'[Indicator] ) = BLANK(); "ERROR";
    LEN ( SELECTEDVALUE ( 'Table'[Indicator] ) ) > 1; "ERROR";
    "OK"
)

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi gauravnarchal ,

     

    Try the following measure:

     

    Measure = 
SWITCH (
    TRUE ();
    ISERROR ( MOD ( SELECTEDVALUE ( 'Table'[Indicator] ); 2 ) ) = FALSE; "ERROR";
    LEN ( SELECTEDVALUE ( 'Table'[Indicator] ) ) > 1; "ERROR";
    "OK"
)

     

     

    • gauravnarchal's avatar
      gauravnarchal
      Post Prodigy
      5 years ago

      Hi MFelix - Thank you for your help. It did work.

       

      One last questions - How can i add a rule to return error for blank cells?

       

      So now - Cell value with more than 1 character or Digits or blank cells are not accepted as a value and should return that as an error. 

       

      Once again, thank you for your help.

       

      Regards

      Gaurav

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi gauravnarchal ,

         

        Try the following code:

        Measure = 
SWITCH (
    TRUE ();
    ISERROR ( MOD ( SELECTEDVALUE ( 'Table'[Indicator] ); 2 ) ) = FALSE; "ERROR";
    SELECTEDVALUE ( 'Table'[Indicator] ) = BLANK(); "ERROR";
    LEN ( SELECTEDVALUE ( 'Table'[Indicator] ) ) > 1; "ERROR";
    "OK"
)
  • dobregon's avatar
    dobregon
    Impactful Individual
    5 years ago

    Hi,

     

    Something that you can do is
    1. Create a column that show you how many characteres the other column has for each row. Using LEN(Columnname). you can create if(len(column)>1,"error","ok")

    2. Create a measure taking into account the new column