Forum Discussion
Count multiple columns include blanks
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])) )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]) )
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
I have tried the above recommendations but they do not return the results I require. What I need is that if either the ID, start date or start time cells are blank then I need it to return 0. I then also need it to count the number of times the ID, start date and start time are all the same, if they are the same it will look at the entire dataset and return the number of times it appears in there for each row. I am trying to replicate an excel formula, see below:
=COUNTIFS($A$2:$A$1500,$A2,$B$2:$B$1500,$B2,$C$2:$C$1500,$C2)
The columns are as follows:
Column A = ID
Column B = Start Date
Column C = Start Time
Column D = Results (excel formula)
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]) )
Thank you Allison, this has almost worked, I need it also to count if any of the rows are blank (empty) if so return 0. I have updated the formula to this:
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))This gives me the following results however I cannot get it to show it 0 instead of blank, I think this is a simple fix but cannot seem to get it to work. Could you suggest a solution.
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