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
pardeepd84 Are you still needing help on this? Please advise what you have tried and what isn't working if so, as there have been numerous similar solutions suggested and all should lead you in the right direction to get your desired result. If they do, please mark as solution(s) so others can find, if they don't, please let us know why not so we can help more.
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.
@AllisonKennedy, I'vetried the recommendations above, but they don't return the results I require. What I need is that if the ID, start date, or start time cells are blank, then I need it to return 0. Next, I also need you to count the number of times the ID, start date, and start time are all the same, if they are the same, it will examine the entire dataset and return the number of times it appears there for each row. I'm trying to replicate an Excel formula, see below:
=COUNTIFS($A$2:$A$1500,$A 2,$B$2:$B$1500,$B 2,$C$2:$C$1500,$C 2)
The columns are as follows:
Column A - ID
Column B - Start Date
Column C - Start Time
Column D - Results (Excel Formula)
@AllisonKennedy, I'vetried the recommendations above, but they don't return the results I need. What I need is that if the ID, start date, or start time cells are blank, then I need it to return 0. Next, I also need you to count the number of times the ID, start date, and start time are all the same, if they are the same, it will examine the entire dataset and return the number of times it appears there for each row. I'm trying to replicate an Excel formula, see below:
=COUNTIFS($A$2:$A$1500,$A 2,$B$2:$B$1500,$B 2,$C$2:$C$1500,$C 2)
The columns are as follows:
Column A - ID
Column B - Start Date
Column C - Start time
Column D - Results (Excel Formula)
@AllisonKennedy, I'vetried the recommendations above, but they don't return the results I need. What I need is that if the ID, start date, or start time cells are blank, then I need it to return 0. Next, I also need you to count the number of times the ID, start date, and start time are all the same, if they are the same, it will examine the entire dataset and return the number of times it appears there for each row. I'm trying to replicate an Excel formula, see below:
=COUNTIFS($A$2:$A$1500,$A 2,$B$2:$B$1500,$B 2,$C$2:$C$1500,$C 2)
The columns are as follows:
Column A - ID
Column B - Start Date
Column C - Start time
Column D - Results (Excel Formula)
@AllisonKennedy, I'vetried the recommendations above, but they don't return the results I need. What I need is that if the ID, start date, or start time cells are blank, then I need it to return 0. Next, I also need you to count the number of times the ID, start date, and start time are all the same, if they are the same, it will examine the entire dataset and return the number of times it appears there for each row. I'm trying to replicate an Excel formula, see below:
=COUNTIFS($A$2:$A$1500,$A 2,$B$2:$B$1500,$B 2,$C$2:$C$1500,$C 2)
The columns are as follows:
Column A - ID
Column B - Start Date
Column C - Start time
Column D - Results (Excel Formula)
@AllisonKennedy, I'vetried the recommendations above, but they don't return the results I need. What I need is that if the ID, start date, or start time cells are blank, then I need it to return 0. Next, I also need you to count the number of times the ID, start date, and start time are all the same, if they are the same, it will examine the entire dataset and return the number of times it appears there for each row. I'm trying to replicate an Excel formula, see below:
=COUNTIFS($A$2:$A$1500,$A 2,$B$2:$B$1500,$B 2,$C$2:$C$1500,$C 2)
The columns are as follows:
Column A - ID
Column B - Start Date
Column C - Start time
Column D - Results (Excel Formula)
@AllisonKennedy, I'vetried the recommendations above, but they don't return the results I need. What I need is that if the ID, start date, or start time cells are blank, then I need it to return 0. Next, I also need you to count the number of times the ID, start date, and start time are all the same, if they are the same, it will examine the entire dataset and return the number of times it appears there for each row. I'm trying to replicate an Excel formula, see below:
=COUNTIFS($A$2:$A$1500,$A 2,$B$2:$B$1500,$B 2,$C$2:$C$1500,$C 2)
The columns are as follows:
Column A - ID
Column B - Start Date
Column C - Start time
Column D - Results (Excel Formula)
@AllisonKennedy, I'vetried the recommendations above, but they don't return the results I need. What I need is that if the ID, start date, or start time cells are blank, then I need it to return 0. Next, I also need you to count the number of times the ID, start date, and start time are all the same, if they are the same, it will examine the entire dataset and return the number of times it appears there for each row. I'm trying to replicate an Excel formula, see below:
=COUNTIFS($A$2:$A$1500,$A 2,$B$2:$B$1500,$B 2,$C$2:$C$1500,$C 2)
The columns are as follows:
Column A - ID
Column B - Start Date
Column C - Start time
Column D - Results (Excel Formula)
@AllisonKennedy, I'vetried the recommendations above, but they don't return the results I need. What I need is that if the ID, start date, or start time cells are blank, then I need it to return 0. Next, I also need you to count the number of times the ID, start date, and start time are all the same, if they are the same, it will examine the entire dataset and return the number of times it appears there for each row. I'm trying to replicate an Excel formula, see below:
=COUNTIFS($A$2:$A$1500,$A 2,$B$2:$B$1500,$B 2,$C$2:$C$1500,$C 2)
The columns are as follows:
Column A - ID
Column B - Start Date
Column C - Start time
Column D - Results (Excel Formula)
@AllisonKennedy, I'vetried the recommendations above, but they don't return the results I need. What I need is that if the ID, start date, or start time cells are blank, then I need it to return 0. Next, I also need you to count the number of times the ID, start date, and start time are all the same, if they are the same, it will examine the entire dataset and return the number of times it appears there for each row. I'm trying to replicate an Excel formula, see below:
=COUNTIFS($A$2:$A$1500,$A 2,$B$2:$B$1500,$B 2,$C$2:$C$1500,$C 2)
The columns are as follows:
Column A - ID
Column B - Start Date
Column C - Start time
Column D - Results (Excel Formula)
