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 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.

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.

4 Replies

  • 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.