Excluding rows with multiple blank columns

[Anonymous]

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 in 4 columns which should look about like this:

EXCLUDE(FILTER(table,table[GENDER]= "" && table[OPENTOWORK]= "" && table[AGE]= "" && table[WORKINFIELDTOPIC]="" && table[NATIONALITY]= ""))

where can I do this in PowerBI and what would the function in PowerBI language look like?

Thanks in advance,

Adam

[danno]

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]

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?

@danno 

[v-chuncz-msft · Accepted solution]

@Anonymous 

It seems that you may just Create calculated tables.

[amitchandak]

@Anonymous 

use isblank like isblank(table[GENDER]) && isblank(table[Age])

[Anonymous]

What command do I use to exclude and where do I write it into? Thanks for the prompt answer!