Forum Discussion
Helper IIINeed help with troubleshooting "Resourced Exceeded" error
I have a table visual that currently contains 900,000 rows and 20 columns but when the error occurred, it is only displaying 25 rows. This report page also includes three slicers and a "Clear All Slicers" button.
When I added the following measure, I received a 'Query Resources Exceeded' message:
test_measure = IF(ISBLANK(SELECTEDVALUE(my_table[user_id])), "N/A", SELECTEDVALUE(my_table[user_id]))
My questions are:
How can I identify which element on this visual is using the most resources so I can try to see what I can do?
What I find odd is that this report contained many measures that worked fine in the past. However, I have now unchecked all of them, so none are being used when I received the error. Do unused measures still consume resources?
I’ve seen reports done by others with 20+ visuals on a single page. Compared to those, my report seems quite simple. What other factors could be causing this issue?
This is just one year's data and it will grow as the years pass. if a mere 900,000 rows could cause resource issues, how are people using this with tens of million rows of data?
PS: After posting original message, I have actually deleted all Dax measures as well as one of the slicers keeping only date range slicer and one smimple slicer to limit the data displayed. I still received the same error.
Thanks
AR
Hi arunbyc ,
The issue is likely happening because of how Power BI handles relationships and blank values in your data.When you use IF(1=0,0,1), it always gives 1, which might be forcing a filter on the data.
When you use IF(1=1,0,1), it always gives 0, which seems to make Power BI optimize the query differently and avoid unnecessary calculations.
If a column has blanks and it's used to connect fact and dimension tables, Power BI naturally ignores those rows while making relationships.
But if you replace blanks with something like 'Unknown' or 0, these values start matching across tables in ways they shouldn’t, which can create duplicate records and massively increase data size (like a cartesian product).
The difference in behavior between your CloudPC and home computer could be because of different Power BI versions, system settings, or available resources like memory and processing power. It's a good idea to check if both systems are using the same versions and settings.
If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
If you continue to face issues, feel free to reach out to us for further assistance!
4 Replies
- suparnababu8
Super User
Hello arunbyc
- If you want to identify which element on this visual is using the most resources, you can use the Performance Analyzer in PBIDesktop. This will helps you see how each visual and DAX formula performs when you/users interact with the visuals.
- Unused measures do not consume resources during report execution. My suggestion is, whatever the unwanted measures, you can remove those from your report.
- Use the aggregated measures and if you have large data try to implement incremental refresh
If you need additional info pls go through below documentation. It might helps you
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer
https://pivotalstats.com/how-to-manage-and-optimize-power-bi-reports-for-very-large-datasets/
Thank you!!
- Deku
test_measure = IF(ISBLANK(SELECTEDVALUE(my_table[user_id])), "N/A", SELECTEDVALUE(my_table[user_id]))
Replacing blank with any value can be a very expensive process, and remove the engines ability to performance calculation reduction. I assume you also have dimensions in addition to my_table. If this is the case this measure will produce the cross join ( Cartesian product) of the dimension and my_table. Using a huge amount of resources.
arunbycWill you please explain a little more about why replacing the blank with anything else can trigger a cartesian product? Yes. I do have about six dimension tables. And I 've noticed that none of modificatons like rebuilding the report from scratch, reducing number of rows pulled in, rows displayed, reducing columns or eliminating all columns with calculations helped. .
I noticed something really intriguing.
if (1=0, 0,1) produced an Resourced exceeded error
but if (1=1,0,1) worked fime.
It worked always when the condition tested is always true, no matter what the resulting values are. but if the condition is always or sometimes false, it seems to be giving the error.
Also this is happening on my CloudPC, but I am unable to repreduce the error on my home computer.
What would you suggest I do now?
AR
v-aatheequeCommunity Support
Hi arunbyc ,
The issue is likely happening because of how Power BI handles relationships and blank values in your data.When you use IF(1=0,0,1), it always gives 1, which might be forcing a filter on the data.
When you use IF(1=1,0,1), it always gives 0, which seems to make Power BI optimize the query differently and avoid unnecessary calculations.
If a column has blanks and it's used to connect fact and dimension tables, Power BI naturally ignores those rows while making relationships.
But if you replace blanks with something like 'Unknown' or 0, these values start matching across tables in ways they shouldn’t, which can create duplicate records and massively increase data size (like a cartesian product).
The difference in behavior between your CloudPC and home computer could be because of different Power BI versions, system settings, or available resources like memory and processing power. It's a good idea to check if both systems are using the same versions and settings.
If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
If you continue to face issues, feel free to reach out to us for further assistance!
