cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tom
Frequent Visitor

Slicer Error after publishing (works locally)

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"

Error.png

 

Without slicing, everything is showing.

 

 

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

 

Kind regards,

Tom

 

1 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!

View solution in original post

3 REPLIES 3
NimaiAhluwalia
Resolver II
Resolver II

Hello @Tom 

 

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!

 

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!

View solution in original post

v-easonf-msft
Community Support
Community Support

Hi, @Tom 

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors