Calculated column comparing lists of strings too heavy, need help with a better method

[murrayb8]

Hi,

I'm building out a dashboard that takes three inputs lists of strings to search for in a master list of strings.

I essentially want to check if one list is an exact match of an entry in the master list,

if one list is contained within the text in an entry in the master list but not if they contain specific terms.

This will result in one column showing essentailly Found or Not Found.

The master list can be quite large but the two input strings are generally quite small.

What i have so far works but takes ages to complete and is slowing down the whole dashboard's refresh time.

Is there possible a better way of doing this or a way to make this lighter?

Here is what I have so far:

Does list match =IF(ISBLANK(LOOKUPVALUE('inputList'[Exact Term],'inputList'[Exact Term],'masterList'[MasterString]))=FALSE(),"Found",IF(AND(SUMX('inputList',FIND(UPPER('inputList'[Contains Term]),UPPER('masterList'[MasterString]),1,0))>0,SUMX('inputList',FIND(UPPER('inputList'[Negative Contains Term]),UPPER('masterList'[MasterString]),1,0))=0),"Found","Not Found")

Thanks in advance for any help!

[v-lili6-msft]

HI,@murrayb8

There should be no mistake in the formula ,Maybe your formula need to optimize ,

please share pbix or some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[murrayb8]

Hi Lin,

Thanks for your response. Yes you are right, my Dax formula does work fine but it takes a long time to refresh. Essentially I need to make this as fast and efficient as possible but I'm not sure how to go about doing this. I've put together an example file that is similar to the setup I have in my file. The formula is the same however the actual input may be sever hundreds of thousands of rows so any efficiency I can add will make a noticeable difference. Here is the file in onedrive: Example File

Thanks in advance for your help!

[v-lili6-msft]

Hi @ murrayb8

The formula of [Does list match 2],  “=FALSE()” isn’t necessary. 

and I do some optimization on your formula , try it please:

Does list match 2 = 
IF (
    ISBLANK (
        LOOKUPVALUE (
            Lists[Exact Term],
            Lists[Exact Term], 'Master Data'[Master String]
        )
    ),
    IF (
        AND (
            SUMX (
                VALUES ( Lists ),
                FIND (
                    UPPER ( Lists[Contains Term] ),
                    UPPER ( 'Master Data'[Master String] ),
                    1,
                    0
                )
            )
                > 0,
            SUMX (
                VALUES ( Lists ),
                FIND (
                    UPPER ( Lists[Negative Contains Term] ),
                    UPPER ( 'Master Data'[Master String] ),
                    1,
                    0
                )
            )
                = 0
        ),
        "Yes",
        "No"
    ),"Yes"
)

another way is divide your formula into several columns then do a simple formula based on these column like :

column 3= ISBLANK (
        LOOKUPVALUE (
            Lists[Exact Term],
            Lists[Exact Term], 'Master Data'[Master String]
        )
column 4=  SUMX (
                VALUES ( Lists ),
                FIND (
                    UPPER ( Lists[Contains Term] ),
                    UPPER ( 'Master Data'[Master String] ),
                    1,
                    0
                )
            )
column 5= SUMX (
                VALUES ( Lists ),
                FIND (
                    UPPER ( Lists[Negative Contains Term] ),
                    UPPER ( 'Master Data'[Master String] ),
                    1,
                    0
                )
            )
then 
Does list match 3 = 
IF (
   'Master Data'[Column 3],
    IF (
        AND (
            'Master Data'[Column 4] > 0,
            'Master Data'[Column 5] = 0
        ),
        "Yes",
        "No"
    ),"Yes"
)

Best Regards,

Lin

[murrayb8]

Hi Lin,

Thanks for your reply, that does improve the speed slightly although that one columns still takes around 15 minutes+ to refresh which is still fairly heavy. I think its purely down to having to compare and match strings. Is there any way maybe to improve a model or process in M that could improve efficiency here do you think?