topic how to correctly filter blanks in Developer

how to correctly filter blanks

Anonymous — Mon, 12 Jun 2017 20:44:10 GMT

inside the CALCULATE formula, I can use filters.

usually I filter blanks using:

data=BLANK()

But another developer here uses this:

LEN(data)=0

Both filters return the same final value, but which filter is the correct one?

in my example, I am filtering off blank dates from my dataset.

How can I test this and be 100% sure of the correct filter?

Is NULL and BLANK the exactly same thing for Power BI? All related material I find shows only blanks, never shows null values.

Re: how to correctly filter blanks

Eric_Zhang — Wed, 14 Jun 2017 02:08:39 GMT

@Anonymous wrote:

inside the CALCULATE formula, I can use filters.

usually I filter blanks using:

data=BLANK()

But another developer here uses this:

LEN(data)=0

Both filters return the same final value, but which filter is the correct one?

in my example, I am filtering off blank dates from my dataset.

How can I test this and be 100% sure of the correct filter?

Is NULL and BLANK the exactly same thing for Power BI? All related material I find shows only blanks, never shows null values.

@Anonymous

Power Query treats the empty("") and null as different, however, DAX seems to treat them as the same.

So the three in DAX shall work the same way.

Column = [a]=BLANK()

Column = [a]=""  //equal to LEN([a])=0

Column = LEN([a])=0

Re: how to correctly filter blanks

Anonymous — Wed, 14 Jun 2017 11:53:38 GMT

thanks for your answer, but I asked DAX, and you showed M language inside Query Editor.

DAX is used only in the Report view for measures and calculates columns or tables. Inside Query Editor it is M language.

Anyway, if they both commands work the same way, when should I use one or another?

Re: how to correctly filter blanks

Eric_Zhang — Thu, 15 Jun 2017 02:06:25 GMT

@Anonymous wrote:

thanks for your answer, but I asked DAX, and you showed M language inside Query Editor.

DAX is used only in the Report view for measures and calculates columns or tables. Inside Query Editor it is M language.

Anyway, if they both commands work the same way, when should I use one or another?

@Anonymous

I showed the null and empty("") difference in data model in M(Query Editor) and Data tab in report designer window, if you look into my snapshot carefully.

Though I think they're equal, I'd use BLANK() when I comparing/counting blank values and use LEN to compare text type(Returns the number of characters in a text string) to make my DAX more comprehensible.

Re: how to correctly filter blanks

Anonymous — Thu, 14 Oct 2021 09:17:35 GMT

I had a problem, that if I used BLANK() statement with int column, it filtered BLANK() and 0s as well.

instead: table[column] = BLANK()

use: LEN(table[column]) <> 0

SOLVED my problem