IF(ISBLANK) in SWITCH too heavy for a query to export

[PshemekFLK]

I have a simple SWITCH formula to switch between different measures in the matrix. One of the measures need to show in % format so I'm using

FORMAT([Measure], "0.0%")

but this measure is adding blank rows in the matrix (as explained here FORMAT function (DAX) - DAX | Microsoft Learn "If value is BLANK() the function returns an empty string")

I found the solution in this thread: Solved: Re: Visual showing blank rows using SELECTEDVALUE ... - Microsoft Power BI Community

where it is suggested to use:

IF ( ISBLANK ( [Measure] ), BLANK (), FORMAT ( [Measure], "0.0%" ) )

and after adding this the blank rows disappear which is great however it looks like the measure is now too heavy and users can't export the summarized data from the matrix. Even a very small export is failing due to query being too heavy. As soon as I get rid of IF(ISBLANK),BLANK() the export works again. Any suggestions for alternative that would allow me to get rid of blank rows and not affect the export possibility?

[PshemekFLK · Accepted solution]

This didn't work unfortunately. The moment I add IF(NOT(ISBLANK) (whether it's in the VAR or not) the query becomes too heavy to export.

I found another solution myself. Basically instead of using a SWITCH formula I tried creating a field parameter which does not require FORMAT formula to show correct format and therefore does not require IF (NOT(ISBLANK)

Thanks anyway!

[johnt75]

Store the measure result in a variable so that it is only calculated once

VAR MeasureResult = [Measure]
RETURN IF( NOT( ISBLANK( MeasureResult ) ), FORMAT( MeasureResult, "0.0%") )