Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 =
Would anyone be able to assit with this, as this is driving me crazy!
Solved! Go to 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.
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.
Hi @v-jialongy-msft
Thanks very much for taking the time to look into this and respond, that did just the trick !! Thanks 🙂
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |