Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PshemekFLK
Helper IV
Helper IV

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

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?

1 ACCEPTED SOLUTION
PshemekFLK
Helper IV
Helper IV

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!

View solution in original post

2 REPLIES 2
PshemekFLK
Helper IV
Helper IV

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
Super User
Super User

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%") )

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors