How to avoid using ISBLANK?

[Tibbie]

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] ) ),
BLANK (),
DIVIDE( [Sales] , [Sales LY Until Today] , BLANK() )
)

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

[Hassan]

Hi!
You can use the function
`∆ % AOV Bud MKtg = IF (
[Sales LY Until Today] = 0, Blank(),
DIVIDE([Sales],
[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.

[BrianJ]

@Tibbie,

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() ||
Y = BLANK()

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

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

Removing Blank (empty) field from ranking calculation

Hi I have used the RANKX function to calculate the ranking of data for a column in my data set my issue is i have a large number of fields in the column that have N/A as the data, i used the replace function to replace N/A with an empty field but it is still counting these fields in the calculation. How can i exclude the empty fields so the ranking calculates based on only the fields that has data in them. Any help appreciated - thanks Lifesaving rule = RANKX(ALL(‘Lifesaving Rules’[Lifesavin…

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

I hope this is helpful.

• Brian

[Tibbie]

@BrianJ

SELECTEDVALUE

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?

IF(
SELECTEDVALUE( [Sales LY Until Today] ) = BLANK(),
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:

[BrianJ]

@Tibbie,

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

[BrianJ]

@Tibbie,

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

[Tibbie]

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