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 these are blank then I would need it to return 0. However if all three columns have data entered in there it will count the number of time it appears in the dataset.

Please see table below, the results column is what I need the measure to return.

ID	Start date	start time	end time	Results
1234	02/02/2021	07:00	07:30	2
1234	02/02/2021	07:00	07:30	2
7890	06/02/2021	11:00	12:00	1
2754	11/02/2021	16:00	16:30	1
5667	04/02/2021	09:00	09:30	3
5667	04/02/2021	09:00	09:30	3
5667	04/02/2021	09:00	09:30	3
	08/02/2021	08:00	08:30	0
4567				0
6789	09/02/2021	09:00	10:00	0

Need Help

AllisonKennedy

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])) )

AllisonKennedy
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]) )

jdbuchanan71

5 years ago

pardeepd84

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

19 Replies

jdbuchanan71
Super User
5 years ago
pardeepd84

You said "However if all three columns have data" but you listed 4 fields in your note.

ID number

start date

start time

end time

Should it check all 4 columns or only 3 of them?

Also, in your sample the last row has a 0 but all 4 fields have data, just an error in the example?

Assuming you mean to check all 4 and the last row was a mistake you can add a column to your table like this.

Row_Count = IF( ISBLANK('Table'[ID])+ISBLANK('Table'[Start date])+ISBLANK('Table'[start time])+ISBLANK('Table'[end time]) = 0, 1, 0)

That will put a 1 on every row where all 4 fields have data. Then we sum that column and put the measure in a visual:

Row Count = SUM ('Table'[Row_Count] )

AllisonKennedy

Community Champion

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])) )

pardeepd84
Helper III
5 years ago
Column 4 is what I would like the DAX formula to return. Apologies for the miscommunication. I would like the results to return a number where the ID, start date and start time are the same, if they are the same it will count the number of times it appears in the dataset and if it appears multiple time it will enter 1,2,3 etc. See example below, the column named results (column 4) is what I would like the measure/DAX formula to return.

ID Start Date Start Time Results
1234 0
11/01/2021 15:30 0
9876 11/01/2021 09:05 1
9876 13/01/2021 13:45 1
3456 01/02/2021 19:30 2
3456 01/02/2021 19:30 2
6789 16/01/2021 19:30 3
6789 16/01/2021 19:30 3
6789 16/01/2021 19:30 3
- v-deddai1-msft
  Community Support
  5 years ago
  Hi pardeepd84 ,
  
  You can use the following measure:
  
  Count Rows = COUNTROWS( FILTER( 'Table', NOT(ISBLANK(MAX('Table'[ID]))) && NOT(ISBLANK(MAX('Table'[Start date]))) && NOT(ISBLANK(MAX('Table'[start time])))))+0
  
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  
  Best Regards,
  
  Dedmon Dai
  Count multiple columns include blanks.pbix40 KB
AllisonKennedy
Community Champion
5 years ago
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.
- pardeepd84
  Helper III
  5 years ago
  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)
  - AllisonKennedy
    Community Champion
    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]) )
- Syndicate_Admin
  Administrator
  5 years ago
  @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)
- Syndicate_Admin
  Administrator
  5 years ago
  @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)
pardeepd84
Helper III
5 years ago
I keep getting multiple replies for my query from syndicate_admin. Apologies if I have posted on the wrong group.