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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ciken
Resolver I
Resolver I

Dax that uses contains in the formula

I am trying to create a report that will show all products within an order number where one of the items contains the word coupon. I've borrowed some DAX from another thread and when I use this formula with the full product name equals, I get the correct response. But because we use many different coupon codes, I'm trying to write it with the CONTAINS( feature and when I add that behind the && it doesn't work properly. 

 

What am I doing wrong?

 

WORKS:

Contains Coupon = VAR inExist=CALCULATE(COUNT('Operations: Orders Shopping Cart'[OrderNumber]),FILTER('Operations: Orders Shopping Cart','Operations: Orders Shopping Cart'[OrderNumber]=EARLIER('Operations: Orders Shopping Cart'[OrderNumber]) && 'Operations: Orders Shopping Cart'[OrderedProductName]="Coupon: epssp-gamer"))

 
RETURN
IF(inExist>0,"Coupon Applied","No Coupon")
 
DOESN'T WORK:
Contains Coupon = VAR inExist=CALCULATE(COUNT('Operations: Orders Shopping Cart'[OrderNumber]),FILTER('Operations: Orders Shopping Cart','Operations: Orders Shopping Cart'[OrderNumber]=EARLIER('Operations: Orders Shopping Cart'[OrderNumber]) && CONTAINS('Operations: Orders Shopping Cart','Operations: Orders Shopping Cart'[OrderedProductName],"Coupon")))

IF(inExist>0,"Coupon Applied","No Coupon")
1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

@ciken 

try

 

Contains Coupon = VAR inExist=CALCULATE(COUNT('Operations: Orders Shopping Cart'[OrderNumber]),FILTER('Operations: Orders Shopping Cart','Operations: Orders Shopping Cart'[OrderNumber]=EARLIER('Operations: Orders Shopping Cart'[OrderNumber]) && SEARCH("coupon",'Operations: Orders Shopping Cart'[OrderedProductName],,0)>0))
RETURN
IF(inExist>0,"Coupon Applied","No Coupon")

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

4 REPLIES 4
az38
Community Champion
Community Champion

hi @ciken 

i dont completely understand.

does it work with CONTAINS or with equal?

 

Anyway, I think it shouldnt work with CONTAINS, because CONTAINS is aboout whole table.

for row-by-row comparision you should use SEARCH https://docs.microsoft.com/en-us/dax/search-function-dax or FIND https://docs.microsoft.com/en-us/dax/find-function-dax functions (the difference is in case-sensitivity)

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
az38
Community Champion
Community Champion

@ciken 

try

Contains Coupon = VAR inExist=CALCULATE(COUNT('Operations: Orders Shopping Cart'[OrderNumber]),FILTER('Operations: Orders Shopping Cart','Operations: Orders Shopping Cart'[OrderNumber]=EARLIER('Operations: Orders Shopping Cart'[OrderNumber]) && SEARCH("coupon",'Operations: Orders Shopping Cart'[OrderedProductName])>0))
RETURN
IF(inExist>0,"Coupon Applied","No Coupon")

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Results in Error message: (The search Text producted to function 'SEARCH' could not be found in the given text.)

az38
Community Champion
Community Champion

@ciken 

try

 

Contains Coupon = VAR inExist=CALCULATE(COUNT('Operations: Orders Shopping Cart'[OrderNumber]),FILTER('Operations: Orders Shopping Cart','Operations: Orders Shopping Cart'[OrderNumber]=EARLIER('Operations: Orders Shopping Cart'[OrderNumber]) && SEARCH("coupon",'Operations: Orders Shopping Cart'[OrderedProductName],,0)>0))
RETURN
IF(inExist>0,"Coupon Applied","No Coupon")

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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