Cell Validation

[gauravnarchal]

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

[MFelix · Accepted solution]

Hi @gauravnarchal ,

Try the following measure:

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

[amitchandak]

@gauravnarchal , refer if this solution from the past can help

https://community.powerbi.com/t5/Desktop/Testing-if-a-string-ONLY-contains-letters/td-p/460989

[MFelix · Accepted solution]

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]

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 · Accepted solution]

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]

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