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
Highlighted
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, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

3 REPLIES 3
Highlighted
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, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

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, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 253 members 2,702 guests
Please welcome our newest community members: