Aggregation table distinctcount not working (when slicing via relation)

[Anonymous]

Hi everybody,

I have a problem regarding the filtering/slicing of aggregation tables.

I want to use aggregation tables for a dataset that is connected via DirectQuery to achieve performance gains.

This is my Datamodel (edited due to private data):

I have Table 1, which is related to Table 2 via the "Purchasing_Document_Number" column. There is a m:n connection between the tables. Table 1 is filtering Table 2. Both tables contain a Company_Code.

Following THIS GUIDE, I created aggregation tables that group by Purchasing_Document_Number and Company_Code in both tables. The aggregation tables are saved in "dual" mode (not DirectQuery) to optimize performance by caching data:

Using DaxStudio, i verified that the aggregated tables are indeed used for speeding up the performance.

Here is my problem:

I want to show the distinct amount of Purchasing_Document_Number filtered/sliced by the Company_Code of Table 1.

This worked flawlessly before adding the aggregation tables.

Now, i can only filter by the Company_Code of Table 2, PowerBI is crashing as soon as i try to filter by Company_Code of Table 1.

Using COUNT instead of DISTINCTCOUNT is still working.

Demo without error (COUNT):

Demo with error (DISTINCTCOUNT):

Is there a problem with my m:n relation? I tried relating the two tables directly via Company_Code, but this resulted in the same error.

Thanks in advance for your help!

[v-xulin-mstf]

Hi @Anonymous,

Using many-to-many relationships directly is likely to cause problems, you can use a bridge table to connect those two tables and your life will be easier.

A common problem is circular dependency, you can refer:

https://www.sqlbi.com/articles/understanding-circular-dependencies/

If the issue still exists, you can provide the pbix file after removing sensitive information and I will check it for you.

Best Regards,

Link