Forum Discussion
Helper IDAX query with SUMMARIZECOLUMNS - Optimization
Hi Mr_Robot0092 ,
try loading data using dataflows gen 2 and load it in semantic model and use it for paginated report.
below thread may help:
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
Hi Mr_Robot0092,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to amitchandak, for his inputs on this thread.
Thanks for confirming and for testing that. Just to clarify the syntax issue you are seeing with FILTER() inside SUMMARIZECOLUMNS is expected. The function doesn’t allow filters in that position, which is why it errors out.
However, even if the syntax was adjusted, it unfortunately wouldn’t resolve the original memory issue. The error you are facing is caused by the size of the intermediate tables this query generates, combined with the full outer join in DAX. That is what triggers the Resource Governance limit.
The best approach here is still to move the heavy transformations (joins, merging, grouping, filtering) out of DAX and into Power Query / SQL / Warehouse and only return the required dataset to the report. That is what will avoid the memory limitation completely.Refer this link: https://learn.microsoft.com/en-us/dax/summarizecolumns-function-dax
Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
Community SupportHi Mr_Robot0092,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to amitchandak, for his inputs on this thread.
Thanks for confirming and for testing that. Just to clarify the syntax issue you are seeing with FILTER() inside SUMMARIZECOLUMNS is expected. The function doesn’t allow filters in that position, which is why it errors out.
However, even if the syntax was adjusted, it unfortunately wouldn’t resolve the original memory issue. The error you are facing is caused by the size of the intermediate tables this query generates, combined with the full outer join in DAX. That is what triggers the Resource Governance limit.
The best approach here is still to move the heavy transformations (joins, merging, grouping, filtering) out of DAX and into Power Query / SQL / Warehouse and only return the required dataset to the report. That is what will avoid the memory limitation completely.
Refer this link: https://learn.microsoft.com/en-us/dax/summarizecolumns-function-dax
Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
- v-kpoloju-msft
Hi Mr_Robot0092,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you.
- v-kpoloju-msft
Hi Mr_Robot0092,
Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.Thank you.