SQL Like Filter

[vividarinda]

Hi Experts,

 I am novice to power bi and struggling with query in sql to convert in dax query.

For example : 

IH06.TPLNR_FUNCTIONALLOC NOT LIKE '16__9___'

How to convert query in dax query?

[Anonymous]

Hi @vividarinda ,

I created  a sample pbix file(see the attachment), please check if that is what you want.

Table 2 = FILTER('Table',IFERROR(SEARCH("16__9___",'Table'[TPLNR_FUNCTIONALLOC],1,0),0)=0)

Best Regards

[vividarinda]

Hi @Anonymous ,

Thanks your feedback, i mean, can i filter with escape underscore in dax query?

IH06.TPLNR_FUNCTIONALLOC NOT LIKE '16__9___'

[Fowmy]

@vividarinda 

DAX has a function called CONTAINSSTRING, it should work for you. 
Example:Countries ending with "land"

Evaluate
FILTER ( ALL ( Customer[CountryRegion] ), CONTAINSSTRING ( Customer[CountryRegion], "*land" ) )

[vividarinda]

Hi @Fowmy  ,

Thanks your feedback, i mean, can i filter with escape underscore in dax query?

IH06.TPLNR_FUNCTIONALLOC NOT LIKE '16__9___'

[Anonymous]

Hi @vividarinda ,

Could you please provide some raw data and samples to explain your requirement?

---

 filter with escape underscore in dax query?

IH06.TPLNR_FUNCTIONALLOC NOT LIKE '16__9___'

---

Best Regards

[vividarinda]

I have a raw data like this,

UnitNo	Category
77DZ0279	KBL
77DZ0249	KBL
77GD9006	KBL
77GD0062	KBL
16DZ0277	PETROSEA
16EX0154	PETROSEA
16EX9011	Rental
16DZ9178	Rental

then i want filter based on condition like this :

- If UnitNo start with 77, then "KBL",
- If UnitNo start with 16__9___, then "Rental",
- If else "Petrosea"

[Anonymous · Accepted solution]

Hi @vividarinda ,

You can create a calculated column as below to get it:

Category = 
    SWITCH(
        TRUE(),
        LEFT([UnitNo], 2) = "77", "KBL",
        LEFT([UnitNo], 2) = "16" && MID([UnitNo], 5, 1) = "9", "Rental",
        "PETROSEA"
    )

Best Regards

[vividarinda]

Thank you very much for your help very helpful