Managed Aggregations and filtering in the filtering pane issue

[_power_bi]

I have two fact tables. One is an aggregated version of the other(imported) and the other is full (direct query). These two tables are set up to use managed aggregations.  All fields are related either groupby or are aggregated.  I have the aggregated table hidden.  

Now, when I need to apply a filter, I want to use the filter pane and do filtering there.  The problem is that when I use the field from the direct query table, it is clearly using direct query to gather the results.  It takes a long time when there are only a few options. It should be pulling from the imported table. 

If I unhide the agg table and pull in that field, it renders the options quickly, but it doesn't filter the non aggregated table. 

This seems like a major drawback to using managed aggregations. Not being able to filter using the filter pane. Wondering if others have experienced this and if so, is there a workaround? 

Follow up: I did some testing. When I pull in a field from the direct query table into a filter visual, all values showed up immediately.  But when I pull in the same field to the filter pane, it spins for quite (over a minute) to display values.  The filter pane is definitely using direct query while the visual on the page is using the managed aggregations to gather values. 

[DataInsights · Accepted solution]

@_power_bi,

Are you using a star schema? If not, you can create dimension tables in Power Query (select distinct values from a column such as Department to create a Department dimension). Once dimension tables have been created, create a relationship between each dimension table and fact table. Use fields from dimension tables in filters and slicers (this is a best practice). Since your dimension tables will use Dual storage mode, the list of values should load quickly in filters. The article below may be helpful.

https://dax.tips/2021/09/06/intro-to-power-bi-aggregations/ 

[_power_bi]

This does work as a workaround.  It is a bit strange to separate these out, there are 8 columns. But, if it works in the filter pane, then I think we are good.  Our users also build their own reports from the model, so it will require some explanation why there are these other tables now when they were all in one table before. 

[DataInsights]

@_power_bi,

Separating dimensions into their own tables is an extra step but well worth it. The star schema is actually quite intuitive once you start using it, and Power BI works best with a star schema.

https://www.sqlbi.com/articles/power-bi-star-schema-or-single-table/