- Microsoft Power BI Cookbook
- Brett Powell
- 206字
- 2025-04-04 18:18:13
Summary to detail example
In this example, a summary table (Subcategory Plan) needs to be integrated into a Power BI data model. The business wants to filter plan data via the same Product and Date tables they use regularly and to create actual versus plan reports.
- The grain of the Plan table is Plan Subcategory by Calendar Year and Calendar Month
Plan Summary Table for Virtual Relationships
- Relationships from Plan to the Date and Product tables can't be created directly given the many-to-many relationship
Each physical relationship in Power BI is based on a single column from each table with one of the relationship columns uniquely identifying all the rows of its host table. This is consistent with SSAS Tabular and Power Pivot for Excel models.
- Create the subcategory plan measure
The following DAX measure filters the subcategory plan measure by the Product table and the Date table:
Subcat Plan Amt =
VAR ProductSubCats = VALUES('Product'[Product Subcategory])
VAR DateTbl = SUMMARIZE('Date','Date'[Calendar Year],'Date'[Calendar Month])
RETURN
CALCULATE([Subcategory PlanAmt], TREATAS(ProductSubCats,'Subcategory Plan'[Plan Subcategory]),
TREATAS(DateTbl,'Subcategory Plan'[Calendar Year],'Subcategory Plan'[Calendar Month]))
- Variables are used to store tables representing the filtered values of the Product and Date dimension tables
- The TREATAS() function transfers the variables to the corresponding plan column(s), thus filtering Subcategory Plan