Forum Discussion

gvg's avatar
gvg
Post Prodigy
8 years ago
Solved

How to remove blank rows in a calculated table?

Hi,

 

I have a calculated table made with SELECTCOLUMNS like this:

 

mySales = DISTINCT(SELECTCOLUMNS( Sales, 
                                  "ProductID", Sales[ProductID], 
                                  "Amount", Sales[Amount])
                  )

but it produces some blank values in Amount. How do I modify this table definition so that I get only rows that are non-blank in Amount column? I can not do this in M as this is a table derived from another table in Power BI.

 

 

Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    gvg

     

    May be this one

     

    mySales =
FILTER (
    DISTINCT (
        SELECTCOLUMNS ( Sales, "ProductID", Sales[ProductID], "Amount", Sales[Amount] )
    ),
    NOT ( ISBLANK ( [Amount] ) )
)

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    gvg

     

    May be this one

     

    mySales =
FILTER (
    DISTINCT (
        SELECTCOLUMNS ( Sales, "ProductID", Sales[ProductID], "Amount", Sales[Amount] )
    ),
    NOT ( ISBLANK ( [Amount] ) )
)
    • gvg's avatar
      gvg
      Post Prodigy
      8 years ago

      Yep! Exactly. Clever solution.

       

      Thanks!

    • AlakarHarijan1's avatar
      AlakarHarijan1
      Frequent Visitor
      7 years ago

      Hi Bro, 

       

      Your reply was very usefull for me, I've made my table. Just a little more to add.

       

      After making the custom table i just want to summarize one of the column, can you please help me on this.

       

      Thank you so much.