Suppressing data

[ScottA1423]

I am building a dashboard that needs to suppress information when it may violate patient confidentiality. The dashboard includes the city of patients who present to the emergency department for accidental drug overdoses. A table in the dashboard displays the number of overdoses for each city/town for the filtered date range. We should not be displaying cities with less than 10 so as to protect patient confidentiality. Instead, I want the city/town name to display but with an "S" in the Values field (to indicate data suppression).

Can this be achieved using a new calculated column, measure, or should I create a whole new table? I've tried to create a bad IF formula to display "S" when the sum of Syndrome (overdoses) for the filtered Date Range is less than 10:

Here is something silly I've tried:

Column = IF(TOTALYTD('ED Visits for Overdose (ESSS)'[Syndrome])<10),'ED Visits for Overdose (ESSS)'[Date].[Date],'ED Visits for Overdose (ESSS)'[City],TODAY()),"S",SUM('ED Visits for Overdose (ESSS)'[Syndrome]))

Thank you!

[v-juanli-msft]

Hi @ScottA1423

From your formula, the number of overdoses for each city/town for the filtered date range is not exist directly within your table, it needs to calculate based on the city and date range, right?

Since I don’t know the data model, I make a test with a simple data below using the formula to create a calculated column

city with suppression =
IF ( [ number of overdoses] < 10, "S", [city name] )

 

Could you offer more information as this article suggested so we could get an effective solution?

Best Regards

Maggie

[ScottA1423]

Thank you I apologize for not providing enough detail! I did some searching to better explain my question, but first here is a sample of the applicable data. You'll see the date column is by date of an overdose occurence.

Date	Drug Overdoses	City
Saturday, January 14, 2017	1	Microsoftville
Friday, January 27, 2017	1	Google Hollow
Monday, January 30, 2017	1	Microsoftville
Monday, January 30, 2017	1	Apple Orchard
Thursday, February 2, 2017	1	Apple Orchard
Thursday, February 23, 2017	1	Apple Orchard
Thursday, February 23, 2017	1	Microsoftville
Saturday, March 4, 2017	1	Google Hollow
Saturday, March 18, 2017	1	Apple Orchard
Tuesday, April 4, 2017	1	Apple Orchard

So what happens in my model is based on the date range the user filters for, a table visual populates with the City name and the total overdoses for that period. I am trying to set up a table that shows S instead of the number for fewer than 10 cases. See visuals below:

[v-juanli-msft · Accepted solution]

Hi @ScottA1423

As i use your example, i replace "fewer than 10 " to "1", however it doesn't affect the result you want

Try this measure 

Measure =
IF (
    CALCULATE (
        SUM ( Table1[Drug Overdoses] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[City] ),
            Table1[Date] <= MAX ( 'Table'[Date] )
                && Table1[Date] >= MIN ( 'Table'[Date] )
        )
    )
        <= 1,
    "S",
    CALCULATE (
        SUM ( Table1[Drug Overdoses] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[City] ),
            Table1[Date] <= MAX ( 'Table'[Date] )
                && Table1[Date] >= MIN ( 'Table'[Date] )
        )
    )
)

Best Regards

Maggie

[jm-data]

Hi, great response, wondering if you can help with the following:

I need to suppress so that if less than a certain amount of participants have answered a certain question (when drilled down by slicers) it is not shown, but on top of that I need to suppress each graph to show that if less than 10 individuals answered a specific answer (out of a multiple choice of 7) then each individual bar should be suppressed and not shown.

[jywong]

Is it possible to have this be reflected in a stacked bar chart?
So if some value is fewer than 10, have the chart label in a stacked bar chart display the character "S"? Thank you.