Hi @Steve, thanks for your reply. I am glad you bring up the data structure issue. When I first got this table, I was thinking about break it down to several subset. However, this is the first project I used Power Bi, and did not figure out how to /what’s the benefits. Could you please explain a bit more on why you structure the original table to the current shape? Really appreciate it!
Hi @nainiu440,
So, Power BI is a tabular database management system and every measure in the model works in a process of defining a table, refining a table and iterating over the resultant refined table. At the moment the data table has 36 columns. Every measure and each function that uses Calculate() in the syntax is creating 36 filters in order to begin the refinement process. By creating lookup tables around Tasks and Projects you can reduce the size of the data table to 3 columns (potentially) of Project Name ID, Task Name ID and Duration. At the moment the model is only 36K rows and so performance is not a major concern but, when it starts to get into millions of rows, the DAX calculations that have been created will suffer from performance issues.
Also, since the last time, I’ve had the chance to look further at the data structure. It appears to me each project has many tasks and that each task has a duration (maybe?). If that’s the case, then there is an easier DAX pattern you can use to calculate the mean and Std Dev of the durations, and that would be;
Mean Duration = AVERAGE(Master_Table[Actual_Duration])
Task Mean = CALCULATE([Mean Duration],
ALLEXCEPT(Master_Table, ‘Task Name Table’[TaskName]))
Using the Allexept function removes the filters on projects but maintains those applied to tasks, which are selected in the tasks slicer. Give it a try and let me know what you think.