Forum Discussion
pardeepd84
5 years agoHelper III
Count multiple columns include blanks
Hi, I am trying to create a measure that will count multiple columns, if any of the rows are blank then it would return 0. The columns are ID number, start date, start time, end time - if any of...
- 5 years ago
pardeepd84 You can use the and && operator and create a measure:
Count Rows = COUNTROWS( FILTER( 'Table', NOT(ISBLANK('Table'[ID])) && NOT(ISBLANK('Table'[Start date])) && NOT(ISBLANK('Table'[start time])) && NOT(ISBLANK('Table'[end time])) ) - 5 years ago
pardeepd84 You can try doing this as a calculated COLUMN (not measure)
Result =
VAR _currentrowID = Table[ID]
VAR _CRstartDate = Table[Start Date]
VAR _CRstartTime = Table[Start Time]
RETURN
COUNTROWS( FILTER( Table, Table[ID] = _currentrowID && _CRstartDate = Table[Start Date] && _CRstartTime = Table[Start Time]) )
- 5 years ago
If you add a + 0 to the end of your formula that should do it.
Results3 = VAR _currentrowID = 'Table (2)'[ID] VAR _CRstartDate = 'Table (2)'[Start Date] VAR _CRstartTime = 'Table (2)'[Start Time] RETURN COUNTROWS ( FILTER ( 'Table (2)', 'Table (2)'[ID] = _currentrowID && _CRstartDate = 'Table (2)'[Start Date] && _CRstartTime = 'Table (2)'[Start Time] && 'Table (2)'[ID] <> BLANK () && 'Table (2)'[Start Date] <> BLANK () && 'Table (2)'[Start Time] <> BLANK () && 'Table (2)'[ID] = _currentrowID && 'Table (2)'[Start Date] = _CRstartDate && 'Table (2)'[Start Time] = _CRstartTime ) ) + 0
AllisonKennedy
5 years agoCommunity Champion
pardeepd84 You can use the and && operator and create a measure:
Count Rows = COUNTROWS( FILTER( 'Table', NOT(ISBLANK('Table'[ID])) && NOT(ISBLANK('Table'[Start date])) && NOT(ISBLANK('Table'[start time])) && NOT(ISBLANK('Table'[end time])) )