Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
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.
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]
)
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.
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:
Let me know if this helps.
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]
)
User | Count |
---|---|
93 | |
90 | |
90 | |
81 | |
49 |
User | Count |
---|---|
156 | |
145 | |
104 | |
72 | |
55 |