Hello Dale,
Thank you for your reply.
To clarify on some of your points:
1. Do you mean "to Azure" is "to Power BI Service"?
I would mean when the report is published from the power bi desktop app to the app.powerbi.com website (using direct query so data would not be published with the report).
2. Do we need to change the visualizations often?
The reports would be updated per user requests for new data not currently in the data model, or for addition of new standardized reports for commonly requested views on data.
3. What steps do you do now?
Currently my process is as follows
- I keep two copies of the report file, a identifiable one and a generic one
- I make any new changes to the identifiable one
- Creating visualizations with people identifiable data, such as addresses etc
- I then copy this file and rename it to generic
- I then go through the generic file and remove any people identifiable information
- By replacing it with generic information (such as an internal ID) in visualizations
- Or hiding the fields in the data model, so end users can't edit their own reports and add these fields
- I then publish the generic file for general internal organization consumption, from the PowerBI website
- Anyone who has access to the very sensitive data has a method to access a staging area with the identifiable file using power bi desktop
4. Could you please post a sample with dummy data?
I do not think a sample will add much, looking at the above description... I am just looking for a way to automate hiding columns in the generic pbix file and changing the columns displayed in visualizations.
It does not look like a very complicated problem, but the complexity involved is introduced with the sensitivity of the data and the number of tables and columns involved, which introduces a high level of probability of mistakes as the reports evolve over time compared to an automated solution.
The current system has around 87 tables with over 500 columns and measures... so is fairly large. So manually making these changes takes time away from me on database/query optimization on the SQL Server backend, with it being directquery.
