Forum Discussion

Tumeski's avatar
Tumeski
New Member
8 years ago
Solved

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 :)

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Tumeski
      New Member
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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