Poor performance of calculation groups format string

[f1254lipm]

Hi,

I have a problem with slow performance when using calculation group formats from tabular model in SSAS 2019. I have a pivot table with 3 measures and when I was using just the measures the performance was good.

Then I had the requirement to add the ability to change the format or units of values by selecting the format in a filter so I created a calculation group with one item per format and defined all items as SELECTEDMEASURE() and defined the required format strings into Format string expression. This is the article from sqlbi about this approach but I used much easier expressions just hardcoded formats like "0.00" https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/

Then I added that calculation group into a filter of the pivot table and the calculation group works as expected: when I select for example two decimals in the filter the measures are formatted as two decimals etc. but the format change takes at least a minute sometimes many minutes. This leads to the report being unusable. Are there some ways to improve the performance or different techniques for dynamic formats with the same functionality but better performance? The solution needs to be compatible with Excel.

Thank you for your time,
Filip

[v-shex-msft · Accepted solution]

HI @f1254lipm,

It sounds like you faced the performance issue to process the Dax expressions.

For this scenario, I'd like to suggest you try to use t-SQL query to do apply formal operations, they should not affect getting data and refresh performance. (live connection mode seems not support to use t-SQL statement, perhaps you can try to convert it to direct query mode to enable this on data connector)

Regards,

Xiaoxin Sheng