Forum Discussion
Single Slicer for shared dimensions across multiple Power BI datasets?
Hello!
Question: How can I achieve unified filtering with shared dimensions across multiple Power BI datasets when all business datasets consume from the same central dimensions dataset?
Issue Description:
I am building a composite Balanced Scorecard report using "DirectQuery for Power BI datasets". My report connects to multiple business datasets (e.g., Finance Dataset, Marketing Dataset).
All these source datasets are built using DirectQuery to the same shared dataset (Shared Dimensions Dataset), which contains imported tables (Date, Customer, Product, Department,...) from our Azure SQL database.
However, in my final composite report, I see duplicate instances of each shared dimension. For example, I see Customer (from Finance) and Customer_1 (from Marketing). The same happens for all other shared dimensions (Date, Product, etc.). This prevents unified filtering across business datasets.
Desired Behavior:
I want Power BI to recognize that when multiple business datasets consume the same shared dimensions from a central dataset, they should be treated as the same logical tables in my composite report. This would allow a single Customer slicer to filter all visuals from different business datasets.
Current Behavior:
My composite report connects only to the business datasets (Finance, Marketing).
Both business datasets use DirectQuery to the same Shared Dimensions Dataset.
The Model view shows duplicated dimension tables (Customer & Customer_1, Date & Date_1).
Core Problem:
Even though Finance Dataset and Marketing Dataset both consume dimensions from the exact same shared semantic model, Power BI treats them as separate entities in the composite report, making unified cross-dataset filtering impossible.
Thank you for your help!!
Mz
Hi MahmoudZou ,
Thanks for the clarification.
Just to add to what parry2k mentioned, this is expected with the current composite model behavior. Even if the business semantic models originate from the same shared dimensions dataset, Power BI still treats each semantic model as a separate source group. Because of that, the dimension tables do not automatically merge and the relationships defined inside the source models are not inherited when multiple semantic models are combined in one report.
As you noted, loading only the fact tables would require rebuilding relationships in the Balanced Scorecard, which defeats the purpose of reusing the existing logic. At the moment there is no supported feature that recognises two dimension tables from different semantic models as the same table and keeps a single slicer without extra modeling.
For more details, please visit:
Use composite models in Power BI Desktop - Power BI | Microsoft Learn
Composite model guidance in Power BI Desktop - Power BI | Microsoft LearnThere are similar community threads discussing this scenario:
Solved: Composite model relationships / shared dimension t... - Microsoft Fabric Community
Shared Dataset and Composite Dataset - Microsoft Fabric CommunityOne practical approach is to create a single semantic model that contains the shared dimensions once and then connects to the business facts. The Balanced Scorecard would then rely on that single model instead of combining multiple semantic models at the report layer, avoiding repeated relationship work.
You can monitor the Power BI release plans and Fabric blog for any future changes in this area.
Hope this helps. Please reach out for further assistance.
Thank you.
7 Replies
- parry2k
Super User
MahmoudZou, when you are connecting to the semantic model, you have the choice to pick the tables you want. Maybe you don't want to pick these dimension tables and use it from the common dimension table dataset, and then create the relationship in your new composite model.
- MahmoudZouFrequent Visitor
Thank you for the suggestion. I understand the approach you're recommending, but if I only select the fact tables from the business datasets and omit their dimension tables, I lose all the pre-defined relationships and context from the source semantic models. Manually recreating the relationships for all the fact tables in the composite model contradicts the main objective. The goal is to reuse the existing business logic and relationships from the source datasets without any manual rework or duplication of effort and ensure any changes in the source models are automatcally reflected in the Balanced Scorecared.
Regards
Mz
- v-veshwara-msft
Community Support
Hi MahmoudZou ,
Thanks for the clarification.
Just to add to what parry2k mentioned, this is expected with the current composite model behavior. Even if the business semantic models originate from the same shared dimensions dataset, Power BI still treats each semantic model as a separate source group. Because of that, the dimension tables do not automatically merge and the relationships defined inside the source models are not inherited when multiple semantic models are combined in one report.
As you noted, loading only the fact tables would require rebuilding relationships in the Balanced Scorecard, which defeats the purpose of reusing the existing logic. At the moment there is no supported feature that recognises two dimension tables from different semantic models as the same table and keeps a single slicer without extra modeling.
For more details, please visit:
Use composite models in Power BI Desktop - Power BI | Microsoft Learn
Composite model guidance in Power BI Desktop - Power BI | Microsoft LearnThere are similar community threads discussing this scenario:
Solved: Composite model relationships / shared dimension t... - Microsoft Fabric Community
Shared Dataset and Composite Dataset - Microsoft Fabric CommunityOne practical approach is to create a single semantic model that contains the shared dimensions once and then connects to the business facts. The Balanced Scorecard would then rely on that single model instead of combining multiple semantic models at the report layer, avoiding repeated relationship work.
You can monitor the Power BI release plans and Fabric blog for any future changes in this area.
Hope this helps. Please reach out for further assistance.
Thank you.