Forum Discussion

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 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

v-chuncz-msft
6 years ago
Anonymous

It seems that you may just Create calculated tables.

5 Replies

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
  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
  - v-chuncz-msft
    Community Support
    6 years ago
    Anonymous
    
    It seems that you may just Create calculated tables.
amitchandak
Super User
6 years ago
Anonymous

use isblank like isblank(table[GENDER]) && isblank(table[Age])
- Anonymous
  Not applicable
  6 years ago
  What command do I use to exclude and where do I write it into? Thanks for the prompt answer!

Related content