Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, fellow PBI-users :D,
I would like to search through my "Description" fields and return True when the format is like a European license plate
(example: "1-AAA-000"). I am using this function at the moment:
@harshnathani @Anonymous
Thank you very much for your time and help so far. For now I will replace the (*) for (?), because I need to implement this asap and show results. I do understand that there is a more solid solution, as you've shown me @Anonymous. I will sure inspect the steps that you've taken and read the article so I will understand why it is better to implement it that way.
Hi @DeBIe ,
Assuming that the you are looking for format 1-XXX-XXX.
You can try using ? instead of * in your search.
PARAMETER ATTRIBUTES DESCRIPTION
FindText | The text you want to find. You can use the ? and * wildcard characters; use ~? and ~* to find the ? and * characters. | |
WithinText | The text in which you want to search for FindText. | |
StartPosition | Optional | The character position in WithinText at which you want to start searching. If omitted, the default value is 1. |
NotFoundValue | Optional | The numeric value to be returned if the text is not found; if omitted, an error is returned. |
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
@harshnathani,
The formula Car Yes / No = IF(SEARCH("1-???-???",Tablename[Description],1,0),"Car", "No Car") is not fully correct in this case. The second piece ??? must consist of letters and the last ??? piece must consist of digits. As much as I can tell. And this would be achievable in DAX but it's not worth it. Power Query does it effectively and in a simple way.
Best
D
@Anonymous ,
Thanks for the reply.
I agree that it can be done in an effective way in PowerQuery. No questions regarding this.
But not sure what you mean by the second part of your statement.
? represents a character and can be a digit or a alphabet.
Have shown the search using ? in the image attached.
Regards,
Harsh Nathani
Hi @Anonymous
Thanks, I get what you want to convey.
Regards,
Harsh Nathani
Here's the code in Power Query that implements your logic - file attached.
Start table - Original
Getting the indexes of the correct plates - Correct Plate Index
The final outcome (plate with an indicator) - Correct Plates
Of course, you can do a bit of manipulation and merge all the code in the separate queries into one query. I left many queries for you to be able to inspect the steps.
Best
D
You should read this:
https://www.sqlbi.com/articles/from-sql-to-dax-string-comparison/
But what you should really do is use Power Query to split the strings on "-" and create a suitable structure to be able to identify immediately the strings that are correct. If you do this through a table and you'll store the parts in one column, it'll be almost a child's play to create the measure you want and it'll be FAST. Trying to search for patterns through strings in DAX (using DAX functions) is not only sloooooow. It's also prone to error.
Best
D
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |