Hybrid Model In PBI

[f2f]

I have a report with fact and dimensions . My fact is direct query and my dimensions are import . I have around 20 slicers from dimension tables and I have created few measures which are only based on fact . All my measures are working fine for upto 1 M records of data , beyond that am getting error.

I just created a countrows measures  to test ,which is COUNT(FACT) ,DISTINCTCOUNT(FACT[PRIMARY KEY]) . For both am getting same error. My native query is enabled for fact and it has some 500M records. I need to get count atleast for fact .

[ajaybabuinturi]

HI @f2f ,

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.

[f2f]

@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.

[v-hjannapu · Accepted solution]

Hello @f2f,

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.

[v-hjannapu]

Hi @f2f,
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.

[v-hjannapu]

Hi @f2f,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.

Regards,
Community Support Team.

[ThomasWeppler]

Hey f2f

It is not a problem with DAX but with PowerQuery. Where you try to load to much data.
The best way to solve it is by limmit the amount of data you try to load into your report set, a way to do it could be to filter on time (for instant 2 years.)
You can also go from directQuery to import. This has the problem that data only will be refreshed when you refresh it and it dowsn't update automaticly.

I hope this helps. 🙂

[f2f]

Sorry , I didn't get you . I don't have historical data . The data will be loaded from source which is last one year of data which has 500M records in fact . we can't go with import mode with that much of data  I believe .
Do we have alternate functions to achieve the count of fact table???