Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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"
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em Português@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
Hi @gauravnarchal ,
Try the following measure:
Measure =
SWITCH (
TRUE ();
ISERROR ( MOD ( SELECTEDVALUE ( 'Table'[Indicator] ); 2 ) ) = FALSE; "ERROR";
LEN ( SELECTEDVALUE ( 'Table'[Indicator] ) ) > 1; "ERROR";
"OK"
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
96 | |
78 | |
68 | |
55 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |