cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tumeski Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX CONTAINS() Help with weird data I have

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 Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: DAX CONTAINS() Help with weird data I have

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 Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

Highlighted
Tumeski Frequent Visitor
Frequent Visitor

Re: DAX CONTAINS() Help with weird data I have

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.

Community Support Team
Community Support Team

Re: DAX CONTAINS() Help with weird data I have

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 Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors