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
AllisonKennedy's avatar
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. 

Syndicate_Admin's avatar
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)

Count Rows.JPG