DirectQuery - Measures problem?

[slawxsky]

Hello,

Hello, I have the following problem with direct query. I create two measures:

SV_cnt_of_jobs = SELECTEDVALUE(Number_of_jobs_in_flow[CNT_OF_JOBS])

SV_sum_of_done = SELECTEDVALUE(Done_Run_Error_Stats[SUM_OF_DONE])

 They appear normally in my visualization, which is a table, but I have a problem with displaying the following measure:

CombinedText = [SV_sum_of_done] & "/" & [SV_cnt_of_jobs]

It throws an error: "This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.
Please try again later or contact support. If you contact support, please provide these details."

[Anonymous · Accepted solution]

Hi @slawxsky ,

After testing, there is no error reporting when the size of the data volume is relatively small. 

Therefore this problem can be solved by optimizing the report, query or dataset to reduce memory consumption.
1. Simplify queries: First look at your queries and their transformations to see if they can be simplified. Remove unnecessary columns, filters or transformations that may be taking up too much memory. Make sure you are only loading data that the report actually needs.
2. Reduce the amount of data: If the dataset is too large, consider limiting the amount of data loaded into Power BI. You can filter the data to load only a subset of the data, such as a specific date range or related categories.
3. Use data aggregation: Consider aggregating or summarizing data at the data source level, such as in a SQL query or Power Query, before importing the data into Power BI. this can significantly reduce the amount of data that needs to be loaded into memory.

For more details, you can refer to below document:

DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saud968]

Instead of directly using measures in your Combine Text measure, try storing the values in variables first. This can sometimes improve performance:

CombinedText =
VAR sumOfDone = [SV_sum_of_done]
VAR cntOfJobs = [SV_cnt_of_jobs]
RETURN sumOfDone & "/" & cntOfJobs

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[slawxsky]

Unfortunately it didn't solve the problem

[TomasAndersson]

Could be a problem with your measures being numbers and trying to concatenating them with a string ("/"). 

You can try converting the measures to strings before concatenating by using FORMAT():

CombinedText = FORMAT([SV_sum_of_done],"") & "/" & FORMAT([SV_cnt_of_jobs],"")

See if that helps things out.

[slawxsky]

Unfortunately not, still got the same error. I created calculated column and it seems working, but I'm curious why the measure is raising the mentioned error

[TomasAndersson]

Good that you got it to work! Yeah, not sure. SELECTEDVALUE takes a lot of resources and if it is in a filter context where there are many available values instead of just one it can cause problems sometimes. But if you are using that measure in the same table as 

SV_cnt_of_jobs = SELECTEDVALUE(Number_of_jobs_in_flow[CNT_OF_JOBS])

SV_sum_of_done = SELECTEDVALUE(Done_Run_Error_Stats[SUM_OF_DONE])

and those two work I don't know what caused the issue.