Forum Discussion

Frost's avatar
Frost
Frequent Visitor
9 years ago
Solved

New table filter including zero

I have a table of 15 rows with two columns (A+B), where A has values from zero to five (3 rows zero and 7 rows with values=10 rows) but also some blank values (5 rows). I want to create a new table where I filter out only blanks, but including 0-5 using:

 

  1. Table = FILTER (Sheet1, Sheet1[A] <> BLANK ()) - gives me no blanks and no zeros (7 rows)
  2. Table = FILTER (Sheet1, Sheet1[A] <> BLANK () || Sheet1[A]=0) - gives me all values including blanks and zeros (15rows)???

 

I added a column in the original table:

 

CheckNumberOfNoBlanks =
CALCULATE (
    COUNTA ( Sheet1[A] ),
    FILTER (Sheet1, Sheet1[A] <> BLANK () || Sheet1[A] = 0)
)

 

which gives me the correct number of NoBlanks (10 rows) but for some reason this does not work when creating the Table?

 

Any help to get me out of confusing state...

 

 

 

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion

    Frost

     

    use

     

    if(isblank(column), "value if blank", "value if not blank")

    • Frost's avatar
      Frost
      Frequent Visitor

      Thanks, but is there a way to do this without creating a extra column in the original table? I have a number of these to filter out...

      • Frost's avatar
        Frost
        Frequent Visitor

        ....and yes there is:

         

        Table = Filter(Sheet1, if(ISBLANK(Sheet1[A]), FALSE(), TRUE()))

         

        Thanks for your help!