Forum Discussion

pardeepd84's avatar
pardeepd84
Helper III
5 years ago
Solved

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...
Need Help
  • AllisonKennedy's avatar
    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's avatar
    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's avatar
    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
pardeepd84's avatar
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's avatar
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]) )