Count multiple columns include blanks

[pardeepd84]

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

[AllisonKennedy · Accepted solution]

@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 · Accepted solution]

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

[pardeepd84]

I keep getting multiple replies for my query from syndicate_admin.  Apologies if I have posted on the wrong group.  

[AllisonKennedy]

@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. 

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[pardeepd84]

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 · Accepted solution]

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

[pardeepd84]

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.  

[jdbuchanan71 · Accepted solution]

@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

[pardeepd84]

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]

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

[AllisonKennedy · Accepted solution]

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

[jdbuchanan71]

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