Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

how to correctly filter blanks

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...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago
    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.

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
9 years ago
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
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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?

    • Eric_Zhang's avatar
      Eric_Zhang
      Microsoft Employee
      9 years ago
      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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