How to apply Minimum Thresholds to visualizations?

[ruecj5]

Hi all,

Can anyone help me figure out how to apply a minimum threshold to a report that prevents data from displaying if there are not enough responses?

My report includes survey data and verbatim responses and allows for fitlering by geo and I would like to set it up so that if there were less than 5 responses from a particular location that you can filter down to, the data will not show in order to keep things anonymous.

Any help is much appreciated,

Chris

[Greg_Deckler]

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.

[ruecj5]

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!

[ruecj5 · Accepted solution]

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.