Chapter 2 of Analyzing data With Microsoft Power BI and Power Pivot for Excel discuss the use of header/details tables in data modeling.
The scenario of header/detail tables appears when you have two fact tables with similar information, but with different granularity. Each of the fact tables has its own dimensions. It looks like two star schemas linked together by a relationship between the facts tables.
The problem with this set-up is that, even though both tables are related to each other, filtering from a dimension belonging to one facts table will not work for metrics in the other facts table.
To solve this, you can either change your filtering or change your model.
Fast forwarding …. you should change your datamodel!
If you set up a bidirectional filtering between the the two facts tables, part of your calculations follow through, but not all.
If you change your model, by adjusting the granularity in one of your fact table so it will match the ganularity of your other table, you are getting somewhere.
You have two fact tables with similar data, but with different level of details:
Both of these fact tables has their own dimensions. Teams are linked to projects, location, vendors and so in. People are linked to position, salery, adresse and so on.
By selecting a metric in your header-data (fex project value), and filtering a dimension linked to your detail-table (fex, level of seniority), you will get incorrect measures because there is no relationship between the two. Your totals will be correct, but each line will display the total value rather than the filtered value because the metric is saved the the header table.
If you try to set up a bidirectional filter on the relationships between your facts tables, your calculations are not as far off as in the previous section, but they may still be incorrect – one person can be part of several teams.
To solve this, you can adjust the metric you want to look into – project value, into the level of details you have in your detail table. This way you link the project value to your people. You denormalize your “project value”.
If you now denormalize your remainder header table into your details table, you will be left a perfect star schema and the issue is resolved.