Search textfield with wildcard has no return

[Applicable88]

Hello,

I used following to search for a material with over several characters and as I found it is correct to use asterisk : 3L6*

I need a table with materialnumbers and have the option through a measure to filter the 3L6* Materials out if needed, next in a column.

It looks like this:

6MF(2) =

if(countx(Filter(Table1,Table1[Material]="3L6*"),Table1[Orders]))>0, "Yes", "NO")

I also tried this:

6MF(2) =

if(CALCULATE(Distinctcount(FBGBDASH[Orders]),Table1[Material]="3L6*")>0,"YES","NO")

I tried also with calculate and all() function without success. Going into detail I realize its already the search of "3L6*" which actually

has no returns. Hope someone knows how to search properly. The Material columns is formatted as Text. 

I hope someone can tell me why wildcard doesn't work. 

Thank you. 

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = COUNTROWS(FILTER(VALUES(Table1[Material]),LEFT(Table1[Material],3)="5ML"))

The answer is 37

[Ashish_Mathur]

Hi,

Could you share a dataset and show the expected result.

[Applicable88]

Hello @Ashish_Mathur,

thank you very much. I prepared sample Data and my search still seems to be not right. I also provide a pbix and a excel file as source:

https://drive.google.com/drive/folders/1BUgDmH60tY0kTgAecreS0hkfXsuI5We-?usp=sharing

Best. 

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = COUNTROWS(FILTER(VALUES(Table1[Material]),LEFT(Table1[Material],3)="5ML"))

The answer is 37

[Applicable88]

Thank you, @Ashish_Mathur ,

do you know why the other one didn't work?

[Ashish_Mathur · Accepted solution]

You are welcome.  If my previous reply helped, please mark it as Answer.  * is a wildcard in MS Excel (not in the DAX language)

[Applicable88]

okay, @Ashish_Mathur ,

what happens if "5ML" would be in a middle of a text? Like "43jk5ML24". In that case left or right function cannot find that part.

[Ashish_Mathur]

Use the Search function - SEARCH function (DAX) - DAX | Microsoft Docs