Forum Discussion

alexschindler's avatar
alexschindler
Icon for Helper I rankHelper I
1 year ago

User-Defined Aggregations slow even though aggregation is hit; is there still some direct query?

Hi all   I am having troubles getting the user-defined aggregations to work as expected. The aggregations are hit, but the queries are still very slow.   I have a minimal example with - a sales ...
Need Help
alexschindler's avatar
alexschindler
Icon for Helper I rankHelper I
1 year ago

Hi Pavan

 

Thanks so much for taking the time to look into it.

 

As for your points:

1. this should be fine – and you can also see on the DAX studio screenshot that it always finds a match.

2. the 3 relevant dimension tables are all in dual mode.

3. I don't think my DAX measures are very complex. What kind of measure is it that may bypass the aggregation? Generally it's mostly a sum of the net sales.

4. I'm hoping that no direct query should be involved for my query. However, it seems that maybe there is something going on under the hood.

 

So all in all the aggregation is working theoretically; however, it takes a big amount of time such that I can't help wondering if some invisible direct query is going on under the hood. I'm not sure how to avoid this though.

 

Thanks for any further hints.

 

Alex

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago
     
    Thank you for your detailed response. Please follow below steps to solve this error;
     
    1. Make sure relationships between aggregation and dimension tables are optimized, and test switching dimension tables to Import mode to rule out DirectQuery issues.
     
    2. Run Server Timings in DAX Studio to check if DirectQuery calls are still occurring, even for simple measures.
     
    3.Ensure your DAX measures only reference columns present in the aggregation table to prevent unintended DirectQuery triggers.
     
    4Enable Query Reduction Settings;
         In Power BI Desktop, go to File > Options and Settings > Options > Query Reduction and enable options that minimize unnecessary queries, which might help in reducing background DirectQuery operations.
     
    Please continue using Microsoft community forum.
     
    If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.
     
    Regards,
    Pavan.