Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Removing rows with all zeros

Hi,

I need to replicate this table into the power bi matrix. The current matrix that i have right now looks like this.

 

I need to remove the staffs with all zeros within the filtered date. Since Suzy and Andy has 0 for all the dates, i want them removed. The matrix that i want to create should be like this.

 

Thank you!

 

Need Help

4 Replies

  • menglaro78's avatar
    menglaro78
    Icon for Microsoft Employee rankMicrosoft Employee
    3 years ago

    HI,

    what data model are you using?
    All in one table?

     

    If you use a Star Schema data model the native behavior of Power Bi is that of your second image.

    In this case in TableFact you have only Row with qty = 1


    If you have this situation in Table Fact

     

    You can use Filters on this visual Sum of QTY is greater than 0

     

    Marco

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi, for the name and dates, its in two seperated tables. However for the "1" and "0", I've created a measure to get the outcome. The one i shared here is just a simple data and easier to understand. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Anonymous ,

    I created some data:

    Table:

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _table=SUMMARIZE('Table','Table'[Name],"sum",SUM('Table'[Amount]))
return
IF(
    MAX('Name Table'[Name]) in SELECTCOLUMNS(FILTER(_table,[sum]<>0),"1",[Name]),1,0)

    2. Place [Flag]in Filters, set is=1, apply filter.

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Appreciate your help, i didnt tell this earlier but the way i got the values "1" and "0" is by using measures. So your method is not applicable to me. Thanks btw!