Filter propagation when using composite model - Direct Query with Local tables.

[AkshayGothe]

• Existing Environment - 
  Multiple production reports related to different departments – HR, Compliance Report, Finance Reports etc. in different workspaces
• Requirement - 
  • Main Requirement - 
    Capturing measures and their dependent tables into one single report using above environment as a source with Direct Query connection to the above sources.
  • Sub-Requirement - 
    The ability to slice visuals from different semantic models by a department slicer (requiring the need for central department slicer for which the model setup is as in the example
• Model Setup Example - 
  
• Challenge - (in a particular report island) - 
  Trying to create the below relationship and the ability to slice via department slicer. ( Department slicer is a local slicer trying to slicer direct query tables
  
  
• Findings-
  Tried the above relationship but results in ambiguity error between any two tables when all relationships are established as an example explained below -
  
  whereas there was no bi-directional filtering enabled , resulting in the inability to slice by dimensions across visuals related to this particular report (report island). The below rel. results in the following error.
  
  Even if I inlcude the other tables connected to the department table, no relationships are set bi directional.
• Our Understanding-
  Source relationships are restricting or ignoring to propagate relationships established locally
  but a central department dimension table is needed to slice value across visuals coming in from different semantic model.
  
  These visuals from different semantic models are placed side by side in report pages which makes it necessary to have a central slicer as shown in the model setup.
  

[v-jtian-msft]

Hi,MFelix ,thanks for your concern about this issue.

Your answer is excellent!

Hello,@AkshayGothe .

Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions.
Thank you very much for your understanding and support of Power BI.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[AkshayGothe]

Hi @MFelix , 

Thanks for the response, while testing out solutions, I tried using TREATAS to propagate selections, it gave me the results I wanted, atleast for the use case I currently have.
Given that, above solution may also be limited in other similar situations due to Composite Model complexities and limitations. 
Do you have some more resources / references to understand better about such models OR their limitations?

[MFelix]

Hi @AkshayGothe ,

The usage of TREATAS is also an option but you need to have that in your measure or a measure that will be added to your visualization to make it work, but believe that the limitations are still very similar for the ones with the "physical" relationship.

Here are some links:

https://data-marc.com/2022/06/08/successfully-implementing-power-bi-composite-models/

https://www.youtube.com/watch?v=3EWmkEdaA2U

[MFelix]

Hi @AkshayGothe ,

The composite models have limitations namely when refering to tables from different models being related that is why you are getting this type of errors, the models can work with this limited relationships but you can have a lot of issues.

Check this blog post with some addtional insights.

One alternative that I can see for working with this is to use the Advance Sync option on slicers, it can take a little bit more to setup in terms of reporting but will allow you to not have this type of relationship.

https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers?tabs=powerbi-deskt...

Using this feature you can setup two slicers based on two different columns to work in the same way, only thing that you need to have is the same type of data for both columns.

So in this case if you have the Department in all the models if you pick that dimension on all your models and have one Non Related table for the Department then you can sync your reports based on slicers.

Has I refer it's a reporting complex setting for the first time but overcomes some of the relationship issues.