Survey Plain Text Data - Setting Minimum Response Threshold

[Anonymous]

Hello everyone! I've got a puzzler here that I'd love to get some perspective on.

As part of a larger survey results dashboard, I have created a table visualization that shows plain text comments associated with individual identifiers. The visualization is filterable many ways. In some cases, a user would be able to filter down to fewer than 5 individual respondents, which exposes people's identity. This is the challenge I'm trying to fix.

For individual measures, I have a good solution:

Census - Inorganic Growth Employee Count = var result = DISTINCTCOUNT('Census - Inorganic Growth'[Empl ID])

return IF(DISTINCTCOUNT('Census - Inorganic Growth'[Empl ID]) >= 5, result)

The above works well for summary scores and most visualizations. However, I need to implement the same type of strategy to output a table. Here's the code I'm using right now -- it throws an error:

Comments Table (Filtered) = var result = CALCULATETABLE('2020-2021 - Comments')

return IF(DISTINCTCOUNT('2020-2021 - Comments'[EmployeeId]) >= 5, result)

Error received:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

This seems like it might have a relatively simple fix. Any ideas? Thank you so much!

[Anonymous]

Seems a bit odd that this isn't doable in PowerBI. Anyone else have any ideas? This is mission critical for my team!

[amitchandak]

@Anonymous , seen like you are still creating a measure, not a table, that is why this error.

Also, I doubt you need to have some grouping involved at what level it should be >5. and that need a different approach

[Anonymous]

Thank you for helping, @amitchandak ! I changed the DAX expression to be in a "new table," but still am getting the same error.

I can't group the employee ID numbers; they need to be filterable many different ways. Maybe I'm misunderstanding?

[v-yingjl]

Hi @Anonymous ,

This error cause by if statement in Power BI. 

Currently as far as I know, if statement not support to return table or multiple columns as expressions result. If statement should return a specific sclar value instead of table value. In addition, Power bi not support to create dynamic calculated column/table based on filter/slicer.

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.