topic Re: Create new column to filter format license plate in DAX Commands and Tips

Create new column to filter format license plate

DeBIe — Tue, 28 Apr 2020 14:36:02 GMT

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!

Re: Create new column to filter format license plate

Anonymous — Tue, 28 Apr 2020 15:30:27 GMT

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

Re: Create new column to filter format license plate

Anonymous — Tue, 28 Apr 2020 17:01:38 GMT

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

Re: Create new column to filter format license plate

Anonymous — Tue, 28 Apr 2020 19:58:32 GMT

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

Re: Create new column to filter format license plate

Anonymous — Tue, 28 Apr 2020 20:10:59 GMT

Anonymous,

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

Re: Create new column to filter format license plate

Anonymous — Tue, 28 Apr 2020 20:24:58 GMT

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

Re: Create new column to filter format license plate

Anonymous — Wed, 29 Apr 2020 09:27:34 GMT

From the original post:

(example: "1-AAA-000")

Best
D

Re: Create new column to filter format license plate

Anonymous — Wed, 29 Apr 2020 09:37:08 GMT

Hi Anonymous

Thanks, I get what you want to convey.

Regards,

Harsh Nathani

Re: Create new column to filter format license plate

DeBIe — Wed, 29 Apr 2020 14:18:26 GMT

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