Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sbenzaquen
Helper I
Helper I

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

sbenzaquen_0-1657039571065.png

sbenzaquen_1-1657039597470.png

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

sbenzaquen_2-1657039709810.png

 

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

 

Thank you,

Salvador

 

 

16 REPLIES 16
sbenzaquen
Helper I
Helper I

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

 

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

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.

@sbenzaquen 

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

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

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

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

@sbenzaquen 
Any luck?

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...

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

@sbenzaquen 

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

@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

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

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.

@sbenzaquen 

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

tamerj1
Super User
Super User

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.