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 experts,
I am building a report to validate report to validate the Participant ID stored in the Participant Table. I wish to add a custom column "Participant ID Validation Result" by checking wheather the values contain any non-numerical chracters. If it is all number, the result will be valid. If it contains any non-numerical characters, it will show invalid.
Participant Table:
Is it possible to write any expressions to achieve this?
Many thanks!
Solved! Go to Solution.
Column = IF(ISERROR(VALUE([Participant ID])),"Invalid","Valid")
DAX
Column = IF(ISERROR(VALUE([Participant ID])),"Invalid","Valid")
DAX
Hi Greg,
Just a follow up quesion if we use following condition:
If it is all number, the result will be valid. If it contains any non-numerical characters OR blank, it will show invalid.
Name | Participant ID | Particpant ID Validation Result |
Smith | 4222T | Invalid |
David | Invalid | |
Jim | 5123同 | Invalid |
Celia | 5922¥ | Invalid |
Jessica | 52221 | Valid |
In that case, how should we modify the DAX to achieve that?
Many thanks!
I believe:
Column = IF(ISBLANK([Participant ID]) || ISERROR(VALUE([Participant ID])),"Invalid","Valid")
in query editor add a new column with this syntax
if Text.Length([Participant ID])=Text.Length(Text.Select([Participant ID],List.Transform({0..9},each Number.ToText(_)))) then "Valid" else "Invalid"
this is M solution, not DAX
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |