Auto-creating Format String Expressions in models with DQ to PBI Dataset - serious performance issue

[DamianMJaworski]

Dear Community,

I've recently experienced issues with Power BI models with Direct Query to Power BI Dataset. The issue results in running out of memory, when executing even simple measures. Same measures execute fine, when I'm connecting live. After investigating this problem, I've noticed that the moment I create a local model, every measure gets the Format String Expression added automatically. This can be observed in TE:

This is not an issues on itself, however this expression (interestingly it's nowhere to be found) results in some very poorly optimized queries in some scenarios. Here's an example:

In this example Format String Expression is evaluated for the cartesian product of two large dimension tables (around 1mln rows each). You can imagine that this can underepeform 'a little bit' (the query used for this screenshot was slightly modified, otherwise it would not execute at all - I've limited rows in the two large dimensions). At the same time, the actual measure doesn't return any values for obvious reasons. 

Strange enough, it doesn't affect all models I've built. 

What seems be even stranger is that this issue seem to be linked with another problem observed in my organization. The model which has this problem when used as DQ to PBI dataset, displays similar issues (running out of memory) when used in Analyze in Excel. I was not able to capture a DAX query there, but a workaround that works in Power BI composite model (not applying any formatting to a measure in the original model), works for Excel too. 

Another symptom of this issue can be spotted in PBI Desktop, when local model is created. The formatting fields are greyed out:

There are two workarounds, but none of them is a true fix for this issue:

1. Setting formatting to General

2. Recreating measure in composite model by simply referencing existing measure in a new one (doesn't work for excel obviously)

Has someone experienced something like this? Can you help?

[101Mathew]

I believe this is more of a problem then people believe.

I have an Azure Anaylsis Services Tabular model - which I use a shell PBIX file to directly connect too (this acts as a shell semantic model).  I then connect to PBIX file to the shell semantic model.

I notice and checking the performance some of the measure (not all) have auto generated format measures, this is adding the slow generation of the visuals,  These measures already have been formatted within the Azure Anaylsis Services Tabular model directly so why, and is there a method to disable it?

[DamianMJaworski]

Hi @Anonymous,

Thank you for your effort in trying to help me. I'm afraid your advice is not applicable in my case. The issue arises not from the redundant data, but from the not optimized query execution and (possibly) a bug in assigning the format string expression to mesaures, that shouldn't have it. 

[Anonymous]

Hi @DamianMJaworski ,
When connecting to a data source, we can control the amount of data we need by selecting the parts we need and deleting redundant and unnecessary data with the select statement.

Hybrid model: You can import less frequently updated data and use DirectQuery for real-time data, reducing the overall memory and computational burden.

Semantic model modes in the Power BI service - Power BI | Microsoft Learn
If the above options still don't solve your problem, I've found some documentation on DirectQuery performance optimisation for you, which I hope will help you, and I'll be proud if I help you solve your problem.

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

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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