To design a Power BI data model that can effectively allow reporting at both summary and detail levels without unnecessary creation of tables, the following guidelines can be adhered to:
Implement Relationship Between Aggregated Tables and the Detail Tables: Apart from creating a detailed fact model that would lead to duplication of data, one can build the detail layer of a model and then aggregate tables that stretch over the details. Thanks to Power BI composite model functionality, you are able to connect those Summary and Detail tables. Therefore, in the drill-down reporting, there will be no need for a break in the flow of data from the summation to detailed data by the use of extra tables.
Make Adjustments to DAX Calculations in Order to Support Summarization in a More Versatile Manner: A well-structured DAX measure will allow for both the detail and the summary positioning. Some appropriate DAX functions to use to enable the measurements to be variable with the user’s view are SUMX, CALCULATE, or GROUP BY, among others. For instance, a measure may aggregate totals to the region in a top-level summary and allow for a drill down of the area by cities without creating any other tables.
Utilize Aggregations Feature in Power BI: Power BI incorporates an element referred to as aggregations, which enables one to create a summary table that is stored at a high level of aggregation and connect such a table with a detailed table or tables. Subsequently, the composite model will use the aggregate tables in the summary view mode and only invoke the detail table to be accessed at the drill-down hierarchy level. This has the advantage of minimizing the size of your model while at the same time providing an improvement in speed performance for the presentation of reports of a higher level.
You may refer to more detailed information about using composite models and aggregations for optimal multi-level reporting in the Microsoft documentation found in the link you provided. This will guarantee that your Power BI model will be efficient and fast.
