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

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's avatar
jdbuchanan71
Super User
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