Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I have 3 tables,
Projects
Tasks
and SubTasks
I would like to create a drill down table (matrix) in Power BI in which i will have 3 layers (Projects, Tasks, SubTasks name ) and as columns i will have the % Complete the Start Date and End Date.
The table i want to looks like the below. When i will expand the Farton --> i will see the Bear --> and then Alfa, Beta.
The relationships that i have in my model is one to many between Project and Task table and One to Mane between Task and SubTask table. I can modify any of those column if it's needed.
Can you help me how can i achieve this ?
Solved! Go to Solution.
Hi, @pbi1908 ;
Try to create a measure.
Measure =
IF(ISINSCOPE('SubTasks'[SubTask Name]),MAX('SubTasks'[start date]),
IF(ISINSCOPE('Tasks'[Task Name]),MAX('Tasks'[start date]),MAX('Projects'[start date])))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @pbi1908 ;
Try to create a measure.
Measure =
IF(ISINSCOPE('SubTasks'[SubTask Name]),MAX('SubTasks'[start date]),
IF(ISINSCOPE('Tasks'[Task Name]),MAX('Tasks'[start date]),MAX('Projects'[start date])))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yalanwu-msft, initialy thanks for your answer, i already create the relationship and i also have the metrics that i want.
My problem is the date, i would like to have the start and date in the visual as i showed in the screenshot above.
I guess that i need to create a new table with all the dates and the IDs of my tables as foreign keys and add the date from the this table something like this, but again if i do this it takes the first date (or last) from subtask table (if the new relationship is between the subtask and the new dates table).
Hi, @pbi1908 ;
First you could create a relationship like below:
Then add matrix rather than table.
the final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |