Dealing with confidential data in Power BI Desktop (without Publishing)

[RichardJ]

Hi,

An Access Database is being used as a data source.

The Access database has some confidential data in it (lets say its employee salary information).

Power BI Desktop is available. Power BI Pro is not available in this case.

Two Reports are required from the source Access Database :

• For the Owner of the business to include all data (including employee Salaries)
• For the Manager of the business to include non confidential data only. (Employee contact details, Name, Email, etc)

Appreciate this would be simpler to manage if they were to use Power BI Pro and control the access via the publication of the Power BI Desktop file and applying security but that isn’t possible for the moment.

Can anyone suggest any options for meeting this requirement using Power BI Desktop and Access only? The two reports would be very similar except for the confidential information.

I could export the data to two spreadsheets from the Access database (confidential/non confidential data) and use them as data sources but would involve manual exports and I’d rather connect to the source data if possible.

Another option may be to connect the Owners pbix file to Access then export a sub set of data from the owners pbix file manually as a data source for the Managers pbix file.

Am wondering if there are other options that could work which don't involve any manual exports if possible.

Thanks,

Richard

[amitchandak]

@RichardJ , Refer if this can help

https://www.biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-par...

[RichardJ]

Thanks @amitchandak for the information. I really like the concept of Dynamic Data Masking.

I'm not connecting to Access via Get Data > SQL Database but by Get Data > Access Database so am unsure about the ability to run SQL statements to apply the Dynamic Data Mask.

Not as efficient as your suggestion but am investigating an alternative solution where I can use Crystal Reports Scheduler to schedule the export of the data I need from the Access Database.

I'll connect the Owners report directly to the Access Database but will schedule the export the non confidential data to a csv file which will be the data source for the Managers report. This should prevent any confidential information from accidentally being surfaced.