Find text in a column from another table

[sbenzaquen]

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 like an additional column in the "Time Spent" table with such Issue key.

Ultimately, the my Time Spent table should look like this:

I'll be grateful if you can help me out. 

Thank you,

Salvador

[sbenzaquen]

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]

@sbenzaquen 
Is there any way to split the key table? i.e. per clients or per project or per task or even a combination of them. 
For example is each task has certian keys then we can split the key table by each task (not actualy split it but to add a new column [Task] to the keys table). Then if we have 90 tasks that would make 100 keys per each task. Is that something applicable in your case?

[sbenzaquen]

Yes that is something we could do. We can organize Issue Keys by project i.e. PBRID-8 and PBRID-67 are part of Project Bridge. PALMA-2 and PALMA-51 are part of Project Alma.

[tamerj1]

@sbenzaquen 

Great. Let me know when done so I can lead you through the rest if the steps

[sbenzaquen]

Done - I have now 48 distinct "Project Keys" (and 9k Issue Keys)

[tamerj1]

@sbenzaquen 
Great. Now connect bothe tables via Project Columns. Then use 

Issue Key =
MAXX (
    FILTER (
        RELATEDTABLE ( 'Issue Key' ),
        CONTAINSSTRING ( 'Time Spen'[Notes], 'Issue Key'[Issue Key] )
    ),
    'Issue Key'[Issue Key]
)

[sbenzaquen]

@tamerj1 - see my reply above (Message 12). What is next? thanks

[tamerj1]

@sbenzaquen 
Any luck?

[sbenzaquen]

It is loading "work in on it" for a while....I think the data is still too big. My time spent table is 313k...

To make sure I understood it correctly, how am I supposed to link the two tables? I still don't have any link with the Time Spent table as there is no issue/project key to use...Do I need to develop one column on the time spent table? I only did the project key in the Issue Key table...

[tamerj1]

@sbenzaquen 
Yes. Either the project name or the project key should be avialable in both tables. The relation would be many to many.

[tamerj1]

@sbenzaquen 

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

[sbenzaquen]

@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]

@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]

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.

[tamerj1]

@sbenzaquen 

Sorry there is no pbix file. How big are you tables? Both of them

[tamerj1]

Hi @sbenzaquen 

please use

Issue Key =
MAXX (
    FILTER (
        VALUES ( 'Issue Key'[Issue Key] ),
        CONTAINSSTRING ( 'Time Spen'[Notes], 'Issue Key'[Issue Key] )
    ),
    'Issue Key'[Issue Key]
)