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, 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
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

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, 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
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,072)