Each visual has its own table and I can't filter

[naninamu]

Hello - bit of a weird one but any advice much appreciated! 

I built a report utilising a relatively simple 3 table model. It has 5 visualisations and 3 slicers which filter across all the visualisations.

The data engineers at work decided they could speed it up by agregating the calcuations for each visual into an sql view for each visualisation: so instead of my simple model, each visualisation has its own dedicated sql view which if required agregates numbers and in theory helps speed things up as we are often dealing with large volumes of data.

The problem is with each table unique to each viz, I can't for the life of me work out how to get filtering working. My slicers filter fields A, B and C, and they have added these fields into each of the 5 views. So, I can make a master table of unique values for A, B and C and link this to each table to have global filtering work - that's fine. However, the cross viz filtering doesn't work as each visual has it's own unique aggregations and is not working off the same underlying data. I have tried linking up the views into a data model in Power BI, but for instance trying to cross filter on Viz A to Viz C doesn't work because they don't share any fields from a common view.

Not sure if this makes sense to anyone, but if anyone has every done anything like this, advice is much appreciated! (I'm trying to convince them to just go back to my nice simple model which filters everything...:p )

[naninamu]

Thank you both for your replies. This is exactly what I was trying to explain to them: "By breaking the data into separate views for each visual, you may lose that shared foundation that enables slicers and visuals to interact smoothly." - you worded it much better! Luckily sanity has prevailed and my 3 tables have been restored. TREATAS sounds interesting tbough, I'd forgotten all about that and never used it, so I'll have to learn how to incorporate that should this come up again. 

[v-pagayam-msft · Accepted solution]

Hi @naninamu ,
Thank you @andrewsommer for the prompt response!

By breaking the data into separate views for each visual, you may lose that shared foundation that enables slicers and visuals to interact smoothly. Power BI works best with a star schema and shared dimensions, which is why your original 3 table model worked well.

I completely agree with the suggestion to return to your simpler model, as it is more aligned with how Power BI is designed to operate. You may still optimize performance using techniques like incremental refresh and native aggregations inside Power BI.

If you are constrained to the current setup, one possible workaround is to use a shared dimension table for fields A, B, and C and link it to each of your views in the data model. This will at least restore global filtering with slicers. For cross-visual filtering, you could also use the DAX function TREATAS() to simulate passing filters between the separate views.

If you want more context or to help explain this to your team, these resources might be helpful:

Star Schema Best Practices for Power BI 
Composite Model Relationships & Shared Dimension Tables – This forum thread talks about using shared dimensions in composite models, which might help with your current setup.

Maybe you could test both approaches  to compare performance and filtering behavior.Feel free to let us know.

Hope this resolve your query.If so,consider accepting it as solution.

Thank you.

Regards,
Pallavi.

[andrewsommer]

It’s hard to answer this without being able to see the model/data but I would say that in general Power BI works much better with granular data. 

Sounds like the best course of action is to go back to the solution that worked with your previous model.  There are many other ways to improve performance than aggregated data in views

Please mark this post as solution if it helps you. Appreciate Kudos.