Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Counting NonBlank Cells across multiple columns

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 ce...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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