Evaluate filter expression for all columns (count rows where any column is empty)

[Anonymous]

Hi, 

I'm trying to count all rows where at least one column/value is empty. 

My attempt: COUNTROWS(FILTER(tablename; (true when all values are NOT empty)))

Now my problem: How do I write this boolean expression without checking all columns?

Is there some way to iterate over all columns without having to hard-code them?

Thanks, 

Ruben

[v-yuta-msft · Accepted solution]

@Anonymous ,

Suppose the table is like below:

 

Click query editor-> transform-> click on columns [Value1], [Value2] and [Value3]-> Unpivot, then the table will be transformed like below:

 

After apply&close, create a calculate column using dax below:

Flag = IF(CALCULATE(COUNT('Table'[Key]), ALLEXCEPT('Table', 'Table'[Key])) = 3, 1, 0)

Then create a measure to achieve the rows number:

Result = SUM('Table'[Flag]) / 3

 

Community Support Team _ Jimmy Tao

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

[kentyler]

Are you asking this because the number of columns varies ? or are you just looking for a short cut in writing the code ?

[Anonymous]

@kentyler 

the number and names of the columns may vary, yes.