How to avoid using ISBLANK?

Hi all,
Sorry if this subject has already been up, but I dont know what terms or keywords I should search for. Please redirect me to the right one if it already has.

Im trying to find a method to hide “-100%” values if dates/years that dont have any data. See the two measure examples below.

What I would like to use
Δ %Rev LY = DIVIDE( [Sales] , [Sales LY Until Today] , BLANK() )

With the result looking like this

but ending up like this:

What I use now, but think is a mess and does the trick:
Δ %Rev LY =
IF (
OR ( ISBLANK ( [Sales] ), ISBLANK ( [Sales LY Until Today] ) ),
DIVIDE( [Sales] , [Sales LY Until Today] , BLANK() )

Is there some other way to write the code with less ISBLANK, OR and BLANK() ?

You can use the function
`∆ % AOV Bud MKtg = IF (
[Sales LY Until Today] = 0, Blank(),
[Sales LY Until Today], blank())

This will ignore the elements that have no data in the measure of last year. But in an analytical point of view, I think there’s no wrong with the -100%. Anyway, try this function.


I agree completely that ISBLANK makes things more difficult to read and interpret than this alternative construct, for say your OR/ISBLANK construct above:

X = BLANK() ||

where depending on context, X and Y could be variables, columns, SELECTEDVALUE(), measures.

Here’s a forum post that might be helpful/relevant:

If you want a more specific revision, please post your PBIX.

I hope this is helpful.

  • Brian

Hi Brian,
I rewrote my first post.
I aslo took a look at that post. So you mean that i should use this instead as an example?

SELECTEDVALUE( [Sales LY Until Today] ) = BLANK(),
DIVIDE( [Sales] , [Sales LY Until Today] )

How does that hide the “-100%” rows with no data if I understood you correctly?

The file is over 1gb so I cant provide with an example file but lets say that the matrix looks like this:



Once you’ve got that measure working properly, just click the down arrow next to Year:


and make sure that Show Items with No Data is unchecked:


All your current -100% rows should now disappear.

To get your measure working properly:

  1. get rid of the SELECTEDVALUE function (not needed around a measure, which already returns a scalar)
  2. add " ,0" as the third parameter of your DIVIDE function (not required, but a good practice to trap divide by zero errors)

This should work, but give a shout if you still have issues with it.

  • Brian


I prefer the method described above, but a super simple way to handle this is just to put the following visual filter on your table visual:


  • Brian

Thanks Brian! :slight_smile:
This is absolutely helpful, had forgot that we can use the “Filters on all pages”-function to achieve that!