topic Re: Counting NonBlank Cells across multiple columns in DAX Commands and Tips

Counting NonBlank Cells across multiple columns

Anonymous — Sun, 27 Sep 2020 13:41:01 GMT

Hello House,

I have a dataset where I used "SplitColumn by Delimeter" to split a particular column into several columns. Now, I will like to have a calculated column that will count the number of cells with non-blank for each row.

Thank you.

amitchandak

Re: Counting NonBlank Cells across multiple columns

Greg_Deckler — Sun, 27 Sep 2020 14:10:34 GMT

Anonymous I would recommend unpivoting those columns in Power Query. However, if for some reason you need them in multiple columns, you could use MC Aggregations:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-p/391698#M129

MC Count Blank = VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Column1]) VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Column2]) VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Column3]) VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Column4]) VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4) VAR tmpValue = COUNTROWS(FILTER(tmpTable,ISBLANK([Column])) RETURN tmpValue

Re: Counting NonBlank Cells across multiple columns

Anonymous — Sun, 27 Sep 2020 14:33:30 GMT

Thank you very much for your quick response. This is appreciated.

I actually do need the splitting in the multiple columns hence this request.

When I copied and edited the syntax you suggested, it gave me some error. Please see the screenshot below

I think the error is from second to last step of the formula (ISBLANK([Column]))):

MC Count Blank =

VAR tmpCol1 = SELECTCOLUMNS(MAD,"Column",[Please mention all the food your child ate between yesterday and now, Day&Nigh.1])

VAR tmpCol2 = SELECTCOLUMNS(MAD,"Column",[Please mention all the food your child ate between yesterday and now, Day&Nigh.2])

VAR tmpCol3 = SELECTCOLUMNS(MAD,"Column",[Please mention all the food your child ate between yesterday and now, Day&Nigh.3])

VAR tmpCol4 = SELECTCOLUMNS(MAD,"Column",[Please mention all the food your child ate between yesterday and now, Day&Night.])

VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4)

VAR tmpValue = COUNTROWS(FILTER(tmpTable,ISBLANK([Column]))

RETURN tmpValue

Greg_Deckler

Re: Counting NonBlank Cells across multiple columns

Greg_Deckler — Sun, 27 Sep 2020 16:04:03 GMT

Anonymous I'm on my phone so hard to see error message. But may try

COUNTROWS(FILTER(tmpTable,[Column]=BLANK()))

Re: Counting NonBlank Cells across multiple columns

Greg_Deckler — Sun, 27 Sep 2020 19:18:09 GMT

Anonymous There is just a missing ) in the line before RETURN:

Re: Counting NonBlank Cells across multiple columns

Anonymous — Mon, 28 Sep 2020 09:11:25 GMT

Thank you Greg_Deckler . The syntax returned aggregate value for all rows. This is not what I want. Please see screenshot below:

For this screenshot, you would notice that only the first column has values while the rest has are blanks. The figures here should 1. Please can you help out.

Re: Counting NonBlank Cells across multiple columns

Greg_Deckler — Mon, 28 Sep 2020 12:31:50 GMT

Anonymous Maybe:

MC Count Blank = VAR __Table = SUMMARIZE('Table',[Column],"__Column1",MAX([Column1]),"__Column2",MAX(Column2]),"__Column3",MAX([Column3]),"__Column4",MAX([Column4])) VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[__Column1]) VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[__Column2]) VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[__Column3]) VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[__Column4]) VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4) VAR tmpValue = COUNTROWS(FILTER(tmpTable,ISBLANK([Column]))) RETURN tmpValue