cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gauravnarchal
Post Partisan
Post Partisan

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

 

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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_0-1600171535799.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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"
)

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
MFelix
Super User
Super User

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_0-1600171535799.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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

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"
)

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

dobregon
Post Prodigy
Post Prodigy

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



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!