Hi Brian,

Thanks for working this out so thoroughly. We might have to speak a little more about the data model as you confused me a bit, although there might be also a misinterpretation of some data. I recognise and agree fully with your view on Fact tables and Dimension tables. But I developed this stylised data model as the original data model contains a few more tables not relevant to the question and it is completely in Dutch. Therefore I took out the relevant tables and translated it all in English. In reality the data model deals with schools and supplying companies instead of students. In fact the data model is like a Yellow Pages containing rather static information. Therefore there are no actions or transactions in the model.

For the sake of our discussion we can just forget about the Level and we can consider Value as the seize of the students head or something else which is as static as there home address (as long as it is a numerical value that can be summarised). In the original data model Value represents the capacity of the supplying company which is exactly as static as their address. I had hoped to simplify the question by having it represented by schools and students instead of having to explain much about the meaning and relationship between the schools and the supplying companies.

The original data model consists of 8.000 schools and almost 30.000 supplying companies. That’s why I combined all addresses deliberately into a single table in Power Query as I don’t want to burden the performance of my report with making a union in DAX between such large tables. But as far as this drill through question goes, I can live with you suggestion to bring back the addresses to the original tables.

My problem sits with the two distances tables. If we forget about the column Level and rethink the column Value as a static aspect of each student and bring it back to the Student table, I am left with two almost identical tables.

Perhaps the meaning of the distances is a bit more relevant. In my original data model I calculated the distance between each possible combination of both schools and supplying companies up to a distance of max. 5000m. So I took all 38.000 addresses and cross joined them with each other, calculated the geographical distance and filtered the rows with a distance less than 5000m. This results in a table with some 700.000 rows. This means that I calculated the distance between each possible combination of a school or supplier and any other school or supplier. For the simplicity of the question I brought this back to just the distances between a few schools and some students. (And I just copied some random distances, therefore the funky outcome in your map )

This means that in my original distance table in the column School, each School-id will occur multiple times and in the column Students, each Student-id will occur multiple times. Therefore I recognised a 1 to many relationship between the School table and the Distance table and also a 1 to many relationship between the Students table and the Distance table.

Regarding this additional background information, and the wish to neglect the column Level and bring back the column Value to the students table, my question is if we still need two identical tables holding the distance information? In my original data model this would imply duplication a table with some 700.000 rows!

Can you adjust the report so that you adjust the table Values for the columns Level and Value and integrate it wil the Table Distances?

Regards,

Sebastiaan