Count Non Blank Rows w/ Filters

[Anonymous]

I am trying to calculate my NPS scores but it is counting blanks in the distractors and total rows.

Calculated Participant NPS (ICU United) =

VAR _participantAdvocatesICU = (CALCULATE(COUNTROWS('Participant Feedback - ICU United'), 'Participant Feedback - ICU United'[How likely are you to recommend simulation learning to your colleagues?] >= 9))

VAR _participantDetractorsICU = (CALCULATE(COUNTROWS('Participant Feedback - ICU United'), 'Participant Feedback - ICU United'[How likely are you to recommend simulation learning to your colleagues?] <= 6))

VAR _totalParticipantResponsesICU = (CALCULATE(COUNTROWS('Participant Feedback - ICU United')))

RETURN DIVIDE(_participantAdvocatesICU - _participantDetractorsICU, _totalParticipantResponsesICU)

[Jimmy801]

Hello @Anonymous 

instead of using COUNTROWS (that counts the rows of table) you can use COUNTA, where you have to reference to a column. This function counts all values that are not empty.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[StefanoGrimaldi]

hey, on the calculate segment filter for this dax add a filter as follow: 

calculate(expresion, "filter" nps score column <>BLANK())

that <>blank() filter will make it only calculate for the no blank cells in that column you spacify

if this helped please mark as solution, if you liked it give some kudos. 🙂

[Anonymous]

I tried the following but did not seem to work. 

Calculated NPS =

VAR _participantAdvocates = CALCULATE(COUNTROWS('Primary'), 'Primary'[NPS Score] >= 9 <>BLANK())

VAR _participantDetractors = CALCULATE(COUNTROWS('Primary'), 'Primary'[NPS Score] <= 6 <>BLANK())

VAR _totalParticipantResponses = CALCULATE(COUNTROWS('Primary') <>BLANK())

RETURN DIVIDE(_participantAdvocates - _participantDetractors, _totalParticipantResponses)

[v-yingjl · Accepted solution]

Hi @Anonymous ,

If you want to use <> blank in your formula, the measure should be like this:

Calculated NPS =
VAR _participantAdvocates =
    CALCULATE (
        COUNTROWS ( 'Primary' ),
        FILTER (
            ALL ( 'Primary' ),
            'Primary'[NPS Score] >= 9
                && 'Primary'[NPS Score] <> BLANK ()
        )
    )
VAR _participantDetractors =
    CALCULATE (
        COUNTROWS ( 'Primary' ),
        FILTER (
            ALL ( 'Primary' ),
            'Primary'[NPS Score] <= 6
                && 'Primary'[NPS Score] <> BLANK ()
        )
    )
VAR _totalParticipantResponses =
    CALCULATE ( COUNTROWS ( 'Primary' ), 'Primary'[NPS Score] <> BLANK () )
RETURN
    DIVIDE (
        _participantAdvocates - _participantDetractors,
        _totalParticipantResponses
    )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.