my dashboard has many pivot tables each with 2 dimensions down the side: one attribute (e.g. age) opening out into an id field (student_number). then a value = simple data count.
The dashboard also has many filters. For data protection reasons, if the user selects from the Disability or Ethnicity filter, I need to hide the id field but show the attribute field and the value.
i've had a couple of goes
a) replace student_number with a calculated field = Student_Number * (1- GetSelectedCount([Disability])) * (1-GetSelectedCount([Ethnicity])). This then shows '0' if any Ethnicity or Disability selection is made, otherwise shows student number. However it made the first 3 charts I modified painfully slow to refresh (I carefully avoided IF statements) and I would have to add it to every chart, aargh. The report's already huge & unwieldy so I'm very reluctant to let performance deteriorate further. Also it doesn't work if multiple selections are made (luckily this is v unlikely for these binary fields)
b) create a variable varVisible = (1- GetSelectedCount([Disability])) * (1-GetSelectedCount([Ethnicity])) - as above - and the calculated field is
Student_Number * varVisible. This looks like just an extra step but it's a bit faster.
... but I'm wondering is there a magic answer which tackles this from a different angle?
Do you need to see the Student ID in any of the tables?
You could create a second field in your data model which is an autonumber or hash of your Student ID field and use this in your tables instead ... you'd still be able to select a student to look at some other information elsewhere in your app, but you wouldn't see the Student ID so wouldn't be able to identify him/her.
Alternatively, you could still create this field, but then have two charts, one with Student ID and one with the second field. You then use a conditional show on the charts to swap them out when an ethnicity or disablility is selected.
This should perform better than a calculated dimension.
Yes it's essential that you can see Student number if you haven't selected the 'dangerous' filters Ethnicity/ disability.
The dashboard already uses contitional show to allow the user to switch between coloured (bar etc.) charts and 'Underlying counts' using a button - my problem outlined above occurs in 'underlying' view. I could do a third set of charts with the conditions you suggest. But there are a lot of them! But maybe that's the best solution for optimisation