cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tumeski
New Member

DAX CONTAINS() Help with weird data I have

Hello,

 

I thought I would post my own post about this so I can explain the problem well enough. I have tried to look around to find a solution but no luck so far.

 

First I explain what I discovered as a problem and where I need help.

 

I made a measure with following

 

 

Billed = IF(CONTAINS('Bills'; 'Bills'[OrderNum]; 'Orders'[ID]); TRUE(); FALSE())

The idea is, if Bills don't have in any row my order's order number I know there haven't been genereated a bill for that order.

 

 

This works fine and all, but then I realized the data is a bit... "**bleep**y" as in for the field 'Bills'[OrderNum] most of the rows have just 1 value in it (Format: Text) "0001234" for example BUT the field may have MULTIPLE OrderNum's in them like "0001234, 0001235, 0001236".

 

I supposed CONTAINS() might be able to work as a regular expression but that is not the case, I suppose it tries to look at the whole thing so now in Order[ID] I have "0001234" and if this ID is in one of these fields with multiple ID's in it I get FALSE for the ID to exist.

 

Sorry if explanation is a bit messy but I tried to make it clear where the problem is. I have tried doing with FIND(LOOKUPVALUE()) kind of things, but just this one is picking my brain now a bit too much.

 

Thanks a lot in advance DAX gods 🙂

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @Tumeski,

 

If you want do some contains related check, I'd like to suggest you use IN keyword with ALLSELECTED function.

Sample:

Billed =
IF (
    SELECTEDVALUE ( 'Orders'[ID] ) IN ALLSELECTED ( 'Bills'[OrderNum] );
    TRUE ();
    FALSE ()
)

BTW, current dax  functions not support regular expression.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @Tumeski,

 

If you want do some contains related check, I'd like to suggest you use IN keyword with ALLSELECTED function.

Sample:

Billed =
IF (
    SELECTEDVALUE ( 'Orders'[ID] ) IN ALLSELECTED ( 'Bills'[OrderNum] );
    TRUE ();
    FALSE ()
)

BTW, current dax  functions not support regular expression.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Hey @v-shex-msft,

 

Thank you for your reply. This DAX seems to do the trick. In C# Contains() understands to find it inside the string, but yeah I quess only IN is available to do this in DAX world.

HI @Tumeski,

 

>>This DAX seems to do the trick. In C# Contains() understands to find it inside the string, but yeah I quess only IN is available to do this in DAX world.

For search text itself, I'd like to suggest you use search and find function.

DAX Fridays! #30: SEARCH vs FIND

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors