I’ve got a pivot table with 2 value columns (row_count(sum), percent-of-total(custom))
I’ve got level aware field response_cnt generated by the dataset of a dim_tbl->left join->fact_tbl
On null row joins, this response_cnt = 0.
On my pivot table I can do a level aware max(response_cnt) to ensure each row has the true max (not the zero)
Now for the Value columns of Row Count/% of Total - if my max(response_cnt) is < 10 I need show “Redacted” instead of 5/2% for example. I don’t see any formatting that will help me here.
Another way around my issue might be to cover the row dimension value with ‘redacted’, but QS will not let me code a max( ) function on the dimension side. “CONTEXTUAL_MISMATCHED_AGGREGATION”
I’m stuck either way.
On the dimensions I wrote a custom function looking at the response_cnt on the row, but the null row that value is zero - thus I need the max for the level here too.
I was able to modify my function on the dataset to provide the max value over the context. Using this value I was able set a row-count value to 1, 0 or null based on I had non-null join (1 or 0) and if too few I can set to null. This would blank out the value in the 2 Value columns. Now add a conditional formatting for each of those Value columns if the calculated max (now a data value) was < too few turn the background black. I can’t place ‘retacted’ as the text, but the effect is a blacked out box.
That’s a great solution!
I might have misread this, but can you set you preferred value to Null? If you can then you can change your Null values to a text you would like.
Not sure I followed the full context of your problem but you can return text in a pivot table with a calculated field, you just have to also convert all your returned values to a string as well.
A basic example:
ifelse(value_to_return < threshold, ‘REDACTED’, toString(value_to_return))