DAX CONTAINS() Help with weird data I have

[Tumeski]

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 🙂

[Anonymous · Accepted solution]

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

[Tumeski]

Hey @Anonymous,

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.

[Anonymous]

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