Forum Discussion
Count multiple columns include blanks
- 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
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
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