Pivot Tables - drill down

[MarkC1971]

Good Day All,

Apologies if this has been addressed before.

I have two tables in table 1 I have port and number of deficiencies and in the second table I have a list of each deficiency

Table 1 example

Port / Date / Number of inspections / Number of Deficiencies
Rotterdam / 01/07/2018 /1 / 4

Table two

Port / Date / Question ID / Question Title / Description of Deficiency
Rotterdam /01/07/2018 - 11001 / safety / Fire hose leaking
Rotterdam /01/07/2018 - 23001 / safety / Fire extinguisher damaged
Rotterdam /01/07/2018 - 41001 / safety / Door not closing automatically
Rotterdam /01/07/2018 - 51001 / safety / Equipment out of service

I have made a pivot table that counts the number of inspections and sums the number of deficiencies, however when i drill down on the number 4 i would only get the results from table 1

What I want to achieve is to be able to drill down and get the list of deficiencies from table 2.

I have created power queries / power pivot models and established relationships, but so far nothing has worked.

I should add that table 1 also contains rows where there is an inspection but zero deficiencies, where as table 2 only has those cases where there has been a deficiency.

I tried to merge tables 1 and tables 2, but the drill drown in the subsequent pivot table was not able to group the 4 deficiencies by date.

Any ideas or suggestions would be greatly appreciated.

Thanks

Mark