topic Re: DAX Lookup to another list in DAX Commands and Tips

DAX Lookup to another list

hdemedici100 — Tue, 19 Mar 2024 18:57:05 GMT

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

Re: DAX Lookup to another list

Greg_Deckler — Tue, 19 Mar 2024 19:01:11 GMT

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?

Re: DAX Lookup to another list

hdemedici100 — Tue, 19 Mar 2024 19:26:51 GMT

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

Re: DAX Lookup to another list

hdemedici100 — Tue, 19 Mar 2024 19:27:21 GMT

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?

Re: DAX Lookup to another list

Greg_Deckler — Tue, 19 Mar 2024 19:28:58 GMT

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

Re: DAX Lookup to another list

hdemedici100 — Tue, 19 Mar 2024 19:40:03 GMT

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!

Re: DAX Lookup to another list

Greg_Deckler — Wed, 20 Mar 2024 14:19:07 GMT

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