Dashboard of survey responses, don't display results where there have been fewer than X respondants.

[ST2022]

In Power Bi I have a table set up with the following columns (the data is survey data from MS forms):

ID = the ID of all respondents who answered a survey - there are no duplicates.
Statement = the name of the question in the survey - same value for every row
Response = the answer given to the question, with the values being: 1,2,3,4,5 and an "NA" option
Response as number = this converts all the scores in the "Response" column as a number, e.g. 1=1, 2=2 and the "NA" option = 6.

I have plotted this data into a chart in power BI. However, I have a number of slicers in my dashboard and when slicers are set, it sometimes results in fewer than 3 people having answered the question. When this is the case, I do not want the chart to display any data at all. But if more than three people have answered the question, then I want all the results of these people to be displayed in the chart.

I spent hours googling solutions and nothing seems to work. 

I have managed to get this working in another chart with the exact same logic used, the only difference being that the answer options to my other question were simply 1-5 (no "NA option). For this, I applied the following measure as a filter to my graph and set it as "show items when the value is greater than or equal to 3" and it works perfectly:

Q9. ResponseCount =

CALCULATE(

    DISTINCTCOUNT('Survey responses'[ID]),

    ALL('Survey responses'["this references the column which is the called the name of the survey question" ])

)

Would anyone be able to assit with this, as this is driving me crazy!

[v-jialongy-msft · Accepted solution]

Hi @ST2022 

The measure you described seems almost right, but we need to adjust it to correctly exclude "NA" responses from the count. Here’s how you could modify your measure:

Q9. ResponseCount =
CALCULATE(
    DISTINCTCOUNT('Survey responses'[ID]),
    'Survey responses'[Response] <> "NA",
    ALL('Survey responses'[survey question])
)

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ST2022]

Hi @v-jialongy-msft 

Thanks very much for taking the time to look into this and respond, that did just the trick !! Thanks 🙂