DAX Lookup to another list

[hdemedici100]

Hi community,

I have a query with a long list of emails [List A], some of these belong to service accounts and we want to be able to identify these (so they can be filtered out). At the moment, there are six different prefixes (which range in different character lengths), these are in another list which is a query [List B] (I've done it this way as we're likely to add to a list).

When creating a new column, how can I bring back a true or false response is the email column in List A begins with any value in List B? I assume it would be some sort of lookup, but I've not been able to come up with anything.

Thanks

[hdemedici100]

P.S this is my first time on the forum. I couldn't attach the Excel file so pasted it in. Hope that's ok?

[hdemedici100]

@Greg_Deckler, thanks for getting back to me so quick. I've attached some sample data as requested. I'll also have a look at the SEARCH and FIND functions in the menatime. 

LIST A		LIST B
finance.bot@example.com		-SVC
name1.name2@example.com		finance.bot
name1.name3@example.com		ukfm
name1.name4@example.com		app.bot
name1.name5@example.com
name1.name6@example.com
-svcname1.name7@example.com
name1.name8@example.com
name1.name9@example.com
name1.name10@example.com
name1.name11@example.com
name1.name12@example.com
name1.name13@example.com
name1.name14@example.com
name1.name15@example.com
name1.name16@example.com
name1.name17@example.com
ukfmname1.name18@example.com
name1.name19@example.com
name1.name20@example.com
name1.name21@example.com
name1.name22@example.com
name1.name23@example.com
name1.name24@example.com
app.bot25@example.com
name1.name26@example.com
name1.name27@example.com
name1.name28@example.com
name1.name29@example.com
name1.name30@example.com

[Greg_Deckler · Accepted solution]

@hdemedici100 This is perfect. Although, I had actually mocked this up because it was an interesting problem. See the attached PBIX under the signature and see if that solves it for you. Here is the column DAX code:

Column = 
    VAR __Keywords = DISTINCT('ListB'[Column1])
    VAR __MaxLen = MAXX( ADDCOLUMNS( __Keywords, "__Len", LEN([Column1])),[__Len])
    VAR __Table = GENERATEALL( { [Column1] }, __Keywords )
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "__HasKeyword", 
                VAR __Len = LEN([Column1])
                VAR __find = FIND([Column1], LEFT([Value],__Len),,-1)
                VAR __result = 
                    SWITCH(TRUE(),
                        __find = -1, FALSE,
                        __find = 1, TRUE,
                        FALSE
                    )
            RETURN
                __result
        )
    VAR __Result = IF(COUNTROWS(FILTER(__Table1, [__HasKeyword] = TRUE)) >= 1, TRUE, FALSE)
RETURN
    __Result

[hdemedici100]

Firstly, I have no idea what you've done here as it looks very complicated, but it's worked! I've been able to use it in mine, adjust the columns, and it works perfectly! Thank you so much!

[Greg_Deckler]

@hdemedici100 Awesome, here are some annotations:

Column = 
    //get a list of the keywords to find
    VAR __Keywords = DISTINCT('ListB'[Column1]) 
    //get the maximum length of the keywords, this isn't actually needed as I went another route
    VAR __MaxLen = MAXX( ADDCOLUMNS( __Keywords, "__Len", LEN([Column1])),[__Len])
    /* 
       create a table that is the cartesian product of the current row value { } makes it a
       table and the list of keywords
    */
    VAR __Table = GENERATEALL( { [Column1] }, __Keywords )
    VAR __Table1 = 
        // add a column for each row that returns true/false if the keyword is found
        ADDCOLUMNS(
            __Table,
            "__HasKeyword", 
                // get the length of the keyword
                VAR __Len = LEN([Column1])
                // FIND the keyword in just the LEFT of the row column (Value)
                // FIND returns the START posisition if it finds the keyword
                // Using the optional parameter to return -1 if not found
                VAR __find = FIND([Column1], LEFT([Value],__Len),,-1)
                VAR __result = 
                    SWITCH(TRUE(),
                        // if -1 not found so false
                        __find = -1, FALSE,
                        // if the START position of the found keyword is 1 then it was found
                        // at the start of the row column (Value)
                        __find = 1, TRUE,
                        // otherwise, the keyword was found not at the beginning
                        FALSE
                    )
            RETURN
                __result
        )
    // if at least one of the keywords was found at the beginning, then return TRUE, else FALSE
    VAR __Result = IF(COUNTROWS(FILTER(__Table1, [__HasKeyword] = TRUE)) >= 1, TRUE, FALSE)
RETURN
    __Result

[Greg_Deckler]

@hdemedici100 You would generally use SEARCH or FIND to do this. Can you post some sample data so that I can mock it up and not have to guess?