Need Help with Counting Tasks in Gantt Chart with Phase, Summary Phase, and Task Types

[Chinmay_8706]

Hello All,

I am facing an issue with displaying the task count in my Gantt chart. I have two tables, Project and ProjectTask, which are connected via a relationship between Project(Name) from the Project table and ProjectTask(Project) from the ProjectTask table. In my Gantt chart, I have the following fields:

• Parent: Name (from the Project table)

• Task: Project task name (from the ProjectTask table)

• Start and End Dates: Both from the ProjectTask table

• % Completion: From the ProjectTask table as well

Now, I need to count the tasks within the Project Task Name column from the ProjectTask table. However, there is a twist: the Project Task Name column contains different types of data: Phase, Summary Phase, and Task.

To distinguish between these three types, I have the following conditions:

1. Phase: When the Parent Task is empty (i.e., msdyn_parenttask is blank) in the ProjectTask table.

2. Summary Task: When the Parent Task is not empty and Summary is set to True (msdyn_issummary = True) in the ProjectTask table.

3. Task: When the Parent Task is not empty and Summary is set to False (msdyn_issummary = False).

The Parent Task and Summary Task are columns within the ProjectTask table, and they help distinguish between Phase, Summary Task, and Task within the Project Task Name column in the ProjectTask table.

Now, here’s the challenge: In my Gantt chart, I am filtering the Parent Task column to be blank because I only want to show the Phases and not the Summary Phases or Tasks. This is to avoid the list becoming too long, which is undesirable. However, the logic to identify Tasks (as described above) contradicts this Parent Task filter within the gantt chart.

To further differentiate the tasks, I have a Project Bucket column that holds the Phase Name for each task. This helps identify which task belongs to which phase.

The Request: I need a calculated column measure that I can use in my Gantt chart, but the chart does not allow measures; it only accepts calculated columns. I would appreciate any suggestions on how to achieve this in Power BI, considering the above constraints.

Thank you in advance for your help!

[v-mdharahman · Accepted solution]

Hi @Chinmay_8706,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are facing issue with your query regarding task counting in the Gantt chart. I understand that you want to display only Phases while still being able to count the Tasks under them. To count the tasks associated with each Phase, you can create a calculated column in the ProjectTask table.
Use this code :
Task_Count_By_Phase =
VAR CurrentPhase = ProjectTask[Project Bucket] -- Identifies the phase for the task
RETURN
CALCULATE(
COUNT(ProjectTask[Project Task Name]),
FILTER(ProjectTask, ProjectTask[Project Bucket] = CurrentPhase && ProjectTask[msdyn_issummary] = FALSE)
)
This will count only tasks and it ensures that tasks are counted based on their Phase (Project Bucket), even if they are not displayed in the chart.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @Chinmay_8706,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @Chinmay_8706,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-mdharahman]

Hi @Chinmay_8706,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.