Aggregated models in Power BI are an efficient way to improve query performance by pre-aggregating large datasets and speeding up report interactions. However, there may be times when you need to break fusion for an aggregated model to gain more control over your data. Learn the process of breaking fusion and its implications in Power BI.
Understanding Fusion in Aggregated Models
Fusion is a mechanism in Power BI that automatically maps queries from a detailed model to its corresponding aggregated model. This process enhances query performance by reducing the amount of data that needs to be processed. However, in some cases, you may need more granular control over your data, necessitating breaking the fusion between the detailed and aggregated models. One specific example of this is calculating distinct counts.
Reasons to Break Fusion
There are several reasons why you might want to break fusion in an aggregated model:
Custom calculations: If you require complex calculations that are not supported by the aggregated model, breaking fusion allows you to perform these calculations on the detailed model.
Unique data scenarios: In some cases, your data may not align with the aggregated model, leading to incorrect or misleading results. Breaking fusion can help you handle such scenarios more effectively.
Debugging: Breaking fusion can be useful when debugging issues related to data modeling or performance in Power BI.
How to Break Fusion in Power BI
To break fusion in an aggregated model, follow these steps:
Identify the aggregation table: Locate the aggregated table in your data model that you want to break fusion for.
Create a calculated column: Add a new calculated column to the aggregated table. This column should contain a simple calculation or concatenation that is unique to the detailed table. For example, you can create a column called 'FusionBreaker' with a formula like = CONCATENATE([Column1], [Column2]).
Update the relationship: Modify the relationship between the aggregated and detailed tables to include the newly created 'FusionBreaker' column. This will prevent Power BI from automatically fusing the two models, as the new column doesn't exist in the detailed table.
Update measures: You may need to update your measures to account for the broken fusion. For example, you might need to replace aggregated measures with their detailed equivalents or use the SUMMARIZE function to group data at the appropriate level of detail.
You can also break fusion in an aggregated model that uses Direct Query by following these steps:
Create a column in your direct query table called FusionKey that is equal to 1 for every row (this will not add a significant amount of size to your model even with millions of rows).
Add a FusionKey column to the aggregated table that also is equal to 1 for every row.
For your distinct count measure, use CALCULATE and then add this as one of the filter arguments: TREATAS( {1}. ‘Direct Query Table Name’[FusionKey] ).
The last step is to go to the manage aggregations menu and add FusionKey as a Group By column.
Once those steps are complete you can check via server timings in DAX Studio if your model is leveraging the aggregated table for distinct count measures.
Implications of Breaking Fusion
Breaking fusion in an aggregated model can provide more control over your data, but it also has some potential drawbacks:
Decreased performance: Breaking fusion may result in slower query performance, as Power BI will no longer automatically use the aggregated model to optimize data processing.
Increased complexity: Managing multiple models and adjusting measures to account for broken fusion can increase the complexity of your data model.
Breaking Fusion in Power BI
Breaking fusion for an aggregated model in Power BI can be a valuable technique when you require more granular control over your data or need to perform complex calculations. By carefully following the steps outlined above, you can successfully break fusion and adapt your data model to suit your needs. However, it's essential to be aware of the potential impact on performance and complexity, so always consider your organization's requirements and weigh the benefits and drawbacks before proceeding.
Ready to get started?
Contact JourneyTEAM with any Power BI questions, today!