Drill down table with dates

[pbi1908]

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 ?

[v-yalanwu-msft · Accepted 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.

[pbi1908]

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).

[v-yalanwu-msft]

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.