Forum Discussion

ScottA1423's avatar
ScottA1423
New Member
8 years ago
Solved

Suppressing data

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!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

     

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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's avatar
      ScottA1423
      New Member
      8 years ago

      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.

       

      DateDrug OverdosesCity
      Saturday, January 14, 20171Microsoftville
      Friday, January 27, 20171Google Hollow
      Monday, January 30, 20171Microsoftville
      Monday, January 30, 20171Apple Orchard
      Thursday, February 2, 20171Apple Orchard
      Thursday, February 23, 20171Apple Orchard
      Thursday, February 23, 20171Microsoftville
      Saturday, March 4, 20171Google Hollow
      Saturday, March 18, 20171Apple Orchard
      Tuesday, April 4, 20171Apple 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:

      Date filter

      Table

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        8 years ago

        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