Forum Discussion
How to apply Minimum Thresholds to visualizations?
Quick update on the resolution to this issue in case someone has a similar issue with a similar data format type:
Thanks to smoupre for the help offered, the solution I settled on came from the ideas that you shared, just with some tinkering to make it work for my data set.
As described in more detail in my previous post, I have data sets stored in 2 different ways:
1. Survey data in 1 row per response per person format (I have 2 data sets like this, a closed ended version and a verbatim version)
2. Demographic data in 1 row per person format
For my charts utilizing the closed ended survey information I already had a Measure that was used to calculate scores for the various charts. To activate the Minimum Threshold ability, I added an if( statement to the beginning of the calculation that only allowed the Measure to be performed if the Distinctcount of unique respondents was above my threshold of 5. It looked something like this:
Original:
Chart Measure = COUNTA('Closed'[B])/COUNTA('Closed'[C])Minimum Threshold:
Chart Measure = if(DISTINCTCOUNT('Closed'[UniqueID]) > 4, COUNTA('Closed'[B])/COUNTA('Closed'[C]), BLANK())For my tables that utilized verbatim responses, I did not have a measure created to populate the tables, I was just pulling in responses directly from the Response column of my data. To apply a Minimum Threshold, I created a new measure that was simply just a Distinctcount of RespondentID from my related Demographic Information table that had a 1:many relationship based on RespondentID. The new measure looked something like this:
Threshold Measure = DISTINCTCOUNT(DemoInfo[Respondentid])
From there, I applied the measure as a visual level filter on each verbatim chart and filtered it based on the rule that Threshold Measure > 4.
Hope this helps anyone in a similar situation.
Without your data model, difficult to answer exactly but I will give it a go.
If you have data like:
Response,Location,Date
Then, you could either create a measure like:
MyMeasureCount = COUNTA([Response])
That would be if Response is a text column let's say.
Then, if you create a visual with location in it, that should filter MyMeasureCount to each individual location and then you would set a filter for "MyMeasureCount" > 5 for example.
Alternatively, you could create a "Locations" table with a column like:
Location
Relate your fact table to this and create a column like:
MyColumnCount = COUNTX(RELATEDTABLE([FactTable]),[Response])
Same basic concept.
This could all be worthless because I have no idea about your data model.
Thanks for the reply, I will share some more information on my data set and report to see if that helps.
My survey data is stored in columns like below:
SurveyWave RespondentID QuestionID Response
Each respondent has a unique RespondentID that is found in column 2, and there is a row of data for each person for every question in the survey (ie. their unique RespontID appears many times in this data set).
I have the location and other demographic information stored in another data set with columns like below:
SurveyWave RespondentID Location Profession Etc... Etc...
In this table, each unique RespondentID only appears one time in its own row in the data set and each column contains their demographic information whether that is location, profession, or something else. I have a relationship between these two tables based on RespondentID.
I have measures based off of responses in the first data set that are used to populate charts in the report and they are filterable by information in the second data set. I also have verbatim tables that allow for the same filtering to be applied.
I would like to set it up so that if there are less than 5 responses based on the filtering of data, the charts/tables will not display the data.
Thanks again!
Quick update on the resolution to this issue in case someone has a similar issue with a similar data format type:
Thanks to smoupre for the help offered, the solution I settled on came from the ideas that you shared, just with some tinkering to make it work for my data set.
As described in more detail in my previous post, I have data sets stored in 2 different ways:
1. Survey data in 1 row per response per person format (I have 2 data sets like this, a closed ended version and a verbatim version)
2. Demographic data in 1 row per person format
For my charts utilizing the closed ended survey information I already had a Measure that was used to calculate scores for the various charts. To activate the Minimum Threshold ability, I added an if( statement to the beginning of the calculation that only allowed the Measure to be performed if the Distinctcount of unique respondents was above my threshold of 5. It looked something like this:
Original:
Chart Measure = COUNTA('Closed'[B])/COUNTA('Closed'[C])Minimum Threshold:
Chart Measure = if(DISTINCTCOUNT('Closed'[UniqueID]) > 4, COUNTA('Closed'[B])/COUNTA('Closed'[C]), BLANK())For my tables that utilized verbatim responses, I did not have a measure created to populate the tables, I was just pulling in responses directly from the Response column of my data. To apply a Minimum Threshold, I created a new measure that was simply just a Distinctcount of RespondentID from my related Demographic Information table that had a 1:many relationship based on RespondentID. The new measure looked something like this:
Threshold Measure = DISTINCTCOUNT(DemoInfo[Respondentid])
From there, I applied the measure as a visual level filter on each verbatim chart and filtered it based on the rule that Threshold Measure > 4.
Hope this helps anyone in a similar situation.