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