Optimal Data Model - header detail

Hey clay_75 ,

as always, it depends, unfortunately. The people from SQL BI have written an article about data modeling regarding the header/detail challenge: https://www.sqlbi.com/articles/header-detail-vs-star-schema-models-in-tabular-and-power-bi/

Nevertheless, you can create a single view in the data source that allocates the "measures" from the header table to the details, e.g., divididing the header measure by the number of products/per order. You also can consider to change default aggregation function SUM of the header measue to AVERAGE, if this makes sense.

Of course, you have to consider if this makes sense when you filter products and only one product remains in the detail table.

You can also consider both tables as different fact tables with a different dimensionality, one table is only filtered  by a single dimension table - order. The second fact table is filtered by the dimension tables order and product.

Maybe, this helps to make your decisions.

Regards,
Tom