Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Excluding rows with multiple blank columns

Hi there,   I'm coming from SPSS where one has the possibility to exclude (I don't want to remove) cases in the data table based on logical operators. In my case I want to exclude cases with blanks...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi, if you are trying to do a calculation over rows in a table, such as a sum or countrows, then you would use the filter expressions within a measure that uses the CALCULATE statement.  NOT can be used to negate a condition

So add a new measure to your table, and type in the measure statement such as
Total Rows =

CALCULATE(

      COUNTROWS('table'), // or other function that you need

       FILTER('table', 

             NOT ISBLANK(table[GENDER]) && NOT ISBLANK(table[OPENTOWORK]) && NOT ISBLANK(table[AGE])

             && NOT ISBLANK(table[WORKINFIELDTOPIC]) && NOT ISBLANK(table[NATIONALITY])

        )

)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Great answer, thank you! Yet what I'm trying to do is to create a sort of custom filter so that my charts aren't calculated with the rows/cases that lack all essential data. Is there a other function than CALCULATE that could do that?

     

    Anonymous