Forum Discussion

sbenzaquen's avatar
sbenzaquen
Helper I
4 years ago

Find text in a column from another table

Hi experts, I need help with something I'm working on. I want to search in the "Notes" column from the "Time Spent" table if it contains any of the "Issue Keys" of the "Issue Key" table. If so, I'd...
Need Help
sbenzaquen's avatar
sbenzaquen
Helper I
4 years ago

Thank you tamerj1 .

It is not working as PBI gets blocked when trying to process this formula. I think something is off with the formula. Could you please check? maybe sharing a PBI file will help to clarify.

Thank you in advance

 

tamerj1's avatar
tamerj1
Community Champion
4 years ago

sbenzaquen 

If possible please please share your file to check what is the problem. 

  • sbenzaquen's avatar
    sbenzaquen
    Helper I
    4 years ago

    tamerj1  It is not possible to share the file as it contains confidential info. The Time spent table contains 313k rows and the Issue key 9k.

    Is there any way to work around it?

    thank you

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      sbenzaquen 
      That would make 2.8 bilion iterations. In addition to CONTAINSSTRING which is a slow function. Let me think if there is another way to do that.

      • sbenzaquen's avatar
        sbenzaquen
        Helper I
        4 years ago

        Thank you tamerj1 .

        I was maybe thinking of this as an option:

        • issue keys are comprised by: 5 letter characters; a "-" sign and 1/2 digits (e.g. PBRID-72, PALMA-3).
        • we may get a rule that searches for these criteria in the "Notes" field and then remove those that are not in the Issue Key table (you may get some silly date fitting the criteria)

         Let me know if this helps.