Conditionally Redacting Data
Published: 16 May 2017
Last Modified Date: 31 Dec 2019
QuestionHow to conditionally redact data values.
For instance, responses to a survey question may need to be redacted if fewer than a threshold number of participants answered the question.
AnswerThe attached example workbook uses the sample data set Superstore to demonstrate redacting sales data for cities that have had fewer than 5 orders:
- Create a calculated field, name it "Sales with redactions" and enter a formula similar to:
IF COUNTD( [Orders ID] ) > 5
THEN SUM( [Sales] )
ELSE NULL END
- Replace [Sales] in the view with [Sales with redactions].
Additional InformationThe ELSE NULL statement will show blank cells in a crosstab view for redacted data. ELSE NULL can be replaced with ELSE 0 to show zeros in the view. NULL values can also be aliased to show a text value.
Discuss this article...