Forum Discussion

pbi1908's avatar
pbi1908
Helper III
3 years ago
Solved

Drill down table with dates

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 ?

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    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.

3 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    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.

  • pbi1908's avatar
    pbi1908
    Helper III
    3 years ago

    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's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    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.