Forum Discussion
Hybrid Model In PBI
Hello Anonymous,
Thank you for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank ajaybabuinturi , ThomasWeppler for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
Right now your fact table is DirectQuery and all your dimension tables are Import. Because of this mix, Power BI is trying to pull a huge amount of fact data locally to match it with the imported dimensions. When the data is very large like your 500M fact rows, this step crosses the allowed limit and you get that error, even for a simple COUNT.
If you can, try keeping your dimension tables also in DirectQuery. Then all the filtering and joining will happen directly in the database, and Power BI will only receive the final number. In that case, your COUNTROWS on the fact should work fine.
Hope this helps if you have any queries we are happy to assist you further.
Regards,
Community Suppport Team.
HI Anonymous ,
I would like to let you know that, your fact table is DirectQuery (500M rows) and your dimension tables are Import, so Power BI must join them locally in-memory. To perform that join, Power BI must bring the relevant rows from the fact table (often exceeding the 10M-row cap).
This import–DirectQuery hybrid causes the mashup engine to do data shaping locally, which breaks native query folding and leads to massive intermediate results.
I would suggest you that if possible, make all your dimension tables DirectQuery. That allows joins and filters to happen in the database, not in Power BI. Eventually your COUNTROWS(FACT) will execute fully inside SQL, returning a single row then there is NO 10M row issue.
Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.
ajaybabuinturi
thanks for the reply , I can say it was working fine when I use all fact and dimensions as direct . But I would need to check the possiblity of hybrid model as well .
Also , for my fact table native query is enabled ,means there's no problem of query folding. I request ,Is there any possibility that my COUNTROW(FACT) measure will work with hybrid model itself.
Thank you in advance.Hello Anonymous,
Thank you for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank ajaybabuinturi , ThomasWeppler for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
Right now your fact table is DirectQuery and all your dimension tables are Import. Because of this mix, Power BI is trying to pull a huge amount of fact data locally to match it with the imported dimensions. When the data is very large like your 500M fact rows, this step crosses the allowed limit and you get that error, even for a simple COUNT.
If you can, try keeping your dimension tables also in DirectQuery. Then all the filtering and joining will happen directly in the database, and Power BI will only receive the final number. In that case, your COUNTROWS on the fact should work fine.
Hope this helps if you have any queries we are happy to assist you further.
Regards,
Community Suppport Team.Hi Anonymous,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Regards,
Community Support Team.