Forum Discussion
Show error when item/ option is hidden
I am working with survey data. It's a lot more complex, but here is a sample structure to explain.
If I show a bar chart by gender, it would have 2 male and 8 female. I want to hide any response that has fewer than 5 respondents though. So I would show only the female bar and hide the male bar. I have a calculation to do this.
Now I want to show the user a message, which explains that one of the options is not being shown because there are fewer than 5 respondents, so that they don't get confused and think there were no males at all or wonder where that bar has gone. I am struggling with calculating the number of respondents on the response level, when the response itself isn't shown. My expertise is in Tableau (where I would use an INCLUDE LoD) rather than PowerBI and I am struggling to translate my knowledge.
The two approaches I was considering
+ Compare the distint count of responses against the total in the data set and show the warning as soon as it is less than the total- I can get the total, but as the filtering works on the response level I don't know how to apply it in a measure to get the current distinct count of responses
+ Calculate the individual response numbers for each response (e.g. 2 and 8 ) and then flag this as soon as the smallest value is below 5 - same issue, I don't know how to get the number of respondents per response and then get the smallest number from this
Any hints into the right direction will be useful
Hi Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a table.
Table2 = FILTER(SUMMARIZE('Table','Table'[Response],"Count_Type",COUNT('Table'[ID])),[Count_Type]>=5)(3)We can create a measure.
Warning = var _discount1=CALCULATE(DISTINCTCOUNT('Table'[Response])) var _discount2=CALCULATE(DISTINCTCOUNT('Table2'[Response])) return IF(_discount2<_discount1,"Warning",_discount2)(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a table.
Table2 = FILTER(SUMMARIZE('Table','Table'[Response],"Count_Type",COUNT('Table'[ID])),[Count_Type]>=5)(3)We can create a measure.
Warning = var _discount1=CALCULATE(DISTINCTCOUNT('Table'[Response])) var _discount2=CALCULATE(DISTINCTCOUNT('Table2'[Response])) return IF(_discount2<_discount1,"Warning",_discount2)(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for taking the time to respond and build out the solution Neeko.
I should have mentioned that I need these results to be filtered dynamically with a set of slicers. I am not quite sure how to connect this new table to my existing table structure and also if the slicer would act at the right stage of the processing (from what I understand slicer actions don't affect calculations in tables as they are processed at an earlier stage?).
This is the data structure that I am working with.
It feels like your approach of creating a table and then extracting a value from that is the correct one, but the table might need to be created more temporarily/dynamically/in a measure? I will do some research down that route but of course grateful for any further advice.