locked
Ignoring blank results (without using filter) RRS feed

  • Question

  • I have row categories and then there are blanks. How can I create a sum that ignores values for which there is a blank category in that row? Thanks.
    Thursday, January 19, 2017 7:59 PM

Answers

  • It's not a good idea to filter out blanks. These blanks indicate that you have records on the "many" side of a relationship that have no related record on the "one" side of the relationships.

    The best way to deal with these is to either add the missing rows to the table on the "one" side of the relationship OR to not load these records into the table on the "many" side if you are not interested in them.

    If this just a quick and dirty model you can write a calculation that will filter out blanks using the following pattern, but as a general rule it's better to fix the data than use a calculation like this.

    =CALCULATE( Sum( <amount column here> ),  NOT( ISBLANK( <category column here> ) ))


    http://darren.gosbell.com - please mark correct answers

    Friday, January 20, 2017 6:15 AM

All replies

  • Hi Albo44,

    If we add the categories as row level, we are not able to ignore them. We only can figure them. You can click the angle button(highlighted in yellow background), deselect the blank category or click Label Filter->Does Not Equal->null. 

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 20, 2017 2:21 AM
  • It's not a good idea to filter out blanks. These blanks indicate that you have records on the "many" side of a relationship that have no related record on the "one" side of the relationships.

    The best way to deal with these is to either add the missing rows to the table on the "one" side of the relationship OR to not load these records into the table on the "many" side if you are not interested in them.

    If this just a quick and dirty model you can write a calculation that will filter out blanks using the following pattern, but as a general rule it's better to fix the data than use a calculation like this.

    =CALCULATE( Sum( <amount column here> ),  NOT( ISBLANK( <category column here> ) ))


    http://darren.gosbell.com - please mark correct answers

    Friday, January 20, 2017 6:15 AM