Custom data details per user and aggregation sharing

[anietorp]

Hi all,

as a large grouop company data analyst I want to create a report which I share to all the group managers aggregate data (sales data for instance) in order to give a group benchmark perspective.

Moreover, I want to give to each manager come drill-through pages with detailed data.

What I want to achieve is to show confidential data (like colleagues' name) to the respective manager and hide such type of information for the others.

Be aware that I don't want to apply a row level security, but change field data that depends on user's account.

As example, I want that my manager see my colleagues' name (John, Paul, Mark...) but I want that other managers see something like (J***, P****, M****).

Is it something I can do dinamically in Power BI?

Thank you very much!

[v-shamiliv]

Hi @anietorp 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-shamiliv]

Hi @anietorp 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-shamiliv]

Hi @anietorp 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-shamiliv]

Hi @anietorp 
Thank you for reaching out microsoft fabric community forum.

You're right: the first DAX-based solution works well in Power BI visuals but not in Excel-connected reports, since Excel pulls raw data. The second approach, with two columns, is better for that.

You can add two columns in your data model: one with full names (EmployeeName) and one with masked names (EmployeeNameHidden, like J***). The masked version can be created in Power Query or DAX using:

Text.Start([EmployeeName],1) & Text.Repeat("*", Text.Length([EmployeeName]) - 1)

Then create a Field Parameter with both columns. Power BI doesn't let you switch this dynamically with DAX, but you can work around it by creating two visuals (one with each column) and use a DAX measure like this to control which one to show:

IsManager = IF(USERPRINCIPALNAME() IN VALUES(Employee[ManagerEmail]), 1, 0)

Based on this, use bookmarks or visual-level filters to show the correct version depending on who is viewing. This approach also works better for Excel, since only the appropriate column is exposed in the model.


If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.

[SamWiseOwl]

Hi  
Two ways I think you might be able to do this.One would be create a measure for that column and say something like:

People names =
IF (
    SELECTEDVALUE ( employee[EmployeeId] ) --check row id
        IN CALCULATETABLE (
            VALUES ( employee[employeeId] ), --create a list of ids
            employee[managerEmail] = USERPRINCIPALNAME () --filter to manager email of report viewer email
        ),
    SELECTEDVALUE ( employee[EmployeeName] ), --show full name
    LEFT ( SELECTEDVALUE ( employee[EmployeeName] ), 1 )
        & REPT ( "*", LEN ( SELECTEDVALUE ( employee[EmployeeName] ) ) - 1 ) --show obscured name
)

So it checks if person viewing the report is the manager and if they are shows the name otherwise first letter plus *.

Or you could have two columns in the data Employee[EmployeeName] and Employee[EmployeeNameHidden] which would probably be better performance wise. You swap which column is showing using a FieldParameter based on the UserEmail.

@anietorp

[anietorp]

Hi, sorry to be late 😞

The first solution is the way I tried, but there is a problem in case I need to create a PowerBi connected excel report.

How can I figure out the second way? Do you have an example?

Thank you very much!