Slicer Error after publishing (works locally)

[Anonymous]

Hi everyone,

I created a report that uses aggregation tables for caching data. I am using DirectQuery for data access.

This is the simplified data model:

CC_Mapping

Company_Code

OrgName

Company_Code_OrgName

Table 2

Document_Number

Company_Code

Revenue

I want to be able to show the Revenue of "Table 2" based on the Document numbers with a slicer that slices by "Company_Code_OrgName".

I created a 1:* relationship between the "CC_Mapping" table and "Table 2". As key, the "Company_Code" was used.

As expected, I now can slice by "Company_Code_OrgName"

Here comes the problem:

For performance reasons, I am caching the values in an aggregation table:

AGG_Table_2

Document_Number (Group By)

Company Code (Group By)

Revenue (Sum)

Company_Code_OrgName (merged into this table by using "Merge Queries")

This significantly speeds up the performance, as I am handling lots of Document Numbers. When grouping them together and summing up the revenue, data is small enough to fit into local memory.

On my local PC, everything is working as expected with this setup.

I tested all queries in DaxStudio and everyting is working - the aggregation table is used instead of DirectQuery when slicing.

However, as soon is I publish this report to web, slicing results in an error.

This is the error I'm getting:

"Couldn't load data for this visual"

Without slicing, everything is showing.

Is there an issue with aggregation tables in the PowerBI Web application? 

Kind regards,

Tom

[NimaiAhluwalia]

Hello @Anonymous 

If the visual is showing data without the slicer then something is wrong with the agg table or the relationship between them please check once.

Aggregations that combine DirectQuery, Import, and/or Dual storage modes may return different data unless the in-memory cache is kept in sync with the source data. For example, query execution won't attempt to mask data issues by filtering DirectQuery results to match cached values. There are established techniques to handle such issues at the source, if necessary. Performance optimizations should be used only in ways that don't compromise your ability to meet business requirements. It's your responsibility to know your data flows and design accordingly.

IF MY SOLUTION HELPED YOU PLEASE DO HIT A LIKE AND ACCEPT IT AS A SOLUTION IF IT SOLVES YOUR ERROR!

[Anonymous · Accepted solution]

Hi there, the problem was solved by changing the storage mode of the aggregation table from dual to import. There seems to have been some kind of caching problem. Thanks for your help!

[v-easonf-msft]

Hi, @Anonymous 

Sorry, I haven't encountered a similar problem before.

For directquery, have you checked the gateway?Make sure it is configured correctly and all your data source credentials  are correct.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.