Forum Discussion
Split time per project
Hi guys,
I would need your help with verification of my function below. I have 2 tables (one imported and second calculated). The calculated table is showing worklog split per project. Now, the function below should split Logged time between the projects. Unfortunately, the function is working only if there are multiple projects for one worklog. I spent time to find mistake in my formula but didn't find the error. I believe it must be something with EARLIER.
LOGGED_TIME =
VAR _ProjectCount =
COUNTROWS(FILTER('pbi_FACT_Worklogs per Project', 'pbi_FACT_Worklogs per Project'[Worklog ID] = EARLIER('pbi_FACT_Worklogs per Project'[Worklog ID])))
VAR _LoggedTime =
CALCULATE(MAX(jira_FACT_Worklogs[LOGGED_TIME]), jira_FACT_Worklogs[WORKLOG_ID] = EARLIER('pbi_FACT_Worklogs per Project'[Worklog ID]))
RETURN
DIVIDE(_LoggedTime,_ProjectCount)
jira_FACT_Worklogs
| Worklog ID | Employee Name | Projects | Logged Time |
| 1 | Employee 1 | Project 1;Project2;Project3 | 90 |
| 2 | Employee 1 | Project 1 | 50 |
| 3 | Employee 2 | Project 4;Project 5 | 120 |
Calculated table: pbi_FACT_Worklogs per Project
| Worklog ID | Employee Name | Projects | Logged Time /* desired field */ |
| 1 | Employee 1 | Project 1 | 30 |
| 1 | Employee 1 | Project 2 | 30 |
| 1 | Employee 1 | Project 3 | 30 |
| 2 | Employee 1 | Project 1 | 50 |
| 3 | Employee 2 | Project 4 | 60 |
| 3 | Employee 2 | Project 5 | 60 |
Thank you for any help.
IvanS
IvanS , In power query first add this column
Dist Time = [Logged Time]/ List.Count(Text.Split([Projects], ",") )
Power Query- Text.Split: https://youtu.be/Fn5mNEuRNVg
List.Count and List.Distinct: https://youtu.be/zNREVnoAHwM
Now you can use split by Delimiter into rows : https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag
1 Reply
IvanS , In power query first add this column
Dist Time = [Logged Time]/ List.Count(Text.Split([Projects], ",") )
Power Query- Text.Split: https://youtu.be/Fn5mNEuRNVg
List.Count and List.Distinct: https://youtu.be/zNREVnoAHwM
Now you can use split by Delimiter into rows : https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag