Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DeBIe
Post Partisan
Post Partisan

Create new column to filter format license plate

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:

 
Car Yes / No = IF(SEARCH("1-*-*",Tablename[Description],1,0),"Car", "No Car")
 
I am getting correct results, but I am also getting a lot of other results that are correct according to the DAX formula, but somehow I would like to improve my column so that I will only get results when the format is exactly as shown above. 
 
Example correct result:
-1-URD-666 
Example results which I do not need
-01-01-2020 t/m 01-09-01 .....
- NVBTC-DSD-DD-110-DDFS-112-SS
 
Can anyone help me or advise on how to improve my added column? Thanks!
 
8 REPLIES 8
DeBIe
Post Partisan
Post Partisan

@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.

harshnathani
Community Champion
Community Champion

Hi @DeBIe ,

 

 

Assuming that the you are looking for format 1-XXX-XXX.

 

You can try using ? instead of * in your search. 

 

Car Yes / No = IF(SEARCH("1-???-???",Tablename[Description],1,0),"Car", "No Car")

 

 

SEARCH ( <FindText>, <WithinText> [, <StartPosition>] [, <NotFoundValue>] )

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.

StartPositionOptional

The character position in WithinText at which you want to start searching. If omitted, the default value is 1.

NotFoundValueOptional

The numeric value to be returned if the text is not found; if omitted, an error is returned.

 

 

2.JPG

 

Regards,

Harsh Nathani

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

Anonymous
Not applicable

@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

Anonymous
Not applicable

From the original post:

(example: "1-AAA-000")

Best
D

Hi @Anonymous 

 

Thanks, I get what you want to convey.

 

Regards,

Harsh Nathani

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors