Slicer does not work if USERPRINCIPALNAME() is added to report

[johnt-softcloud]

We have two tables project and project_member. The tables are related by project_member.project_id = project.id.  Our report has a slicer that displays project.name.  The report also has a table visual that displays columns from project_member.  The slicer works fine. We then add a measure field, UserName = USERPRINCIPALNAME(), to the table visual. The slicer no longer works.  Why would adding UserName to the table visual break the slicer's functionality. There's still a relationship between project and project_member tables.

[techies]

Hi @johnt-softcloud please try this

Username =

VAR CurrentUser = USERPRINCIPALNAME()

RETURN

    IF(

        CurrentUser IN VALUES(Project_Member[member_name]),

        CurrentUser

    )

[johnt-softcloud]

I watched the video on row level security at https://www.youtube.com/watch?v=jphj40tBPD8
Unfortunately, that is not the functionality I need. I need a way to send the USERPRINCIPALNAME() to the visual. The user should be able to see every record in the table. I created a very simple report pbix file to demonstrate the problem. I uploaded the report to my OneDrive at https://1drv.ms/u/c/535eb13b4d205cd8/Edk6mriJc6FAlyR0fwzdm2gB8fhcoj4OUjAp9CoFTrW--A?e=0xVzCv 

People are having issues with the OneDrive link so I copied the file to my Google Drive link at https://drive.google.com/file/d/1Cab-Z4U--e4JLyhhiD7fDMlykiO0zCYU/view

[MFelix · Accepted solution]

Hi @johnt-softcloud ,

If you allow me to interact, the problem you are having is concerning the context, since the USERPRINCIPALNAME is a calculation that has not relation with the tables you are using it will return the same value for every row in your visual, what is happening is the same has if you had a column with a count or a sum with an unrelated table you get the same value for all rows.

So the filter context provided by the slicer is getting overwritten by the "constant value" that you have in USERPRINCIPALNAME

You can try and  use something similar to this for your measure:

UserName = if(COUNTROWS(project_member) > 0,USERPRINCIPALNAME())

Just adding the row count of the project member table gives you the context you need for the calculation to work properly.

See PBIX file attach.

[johnt-softcloud]

Thank you, this seems to be working. Let me do more testing.

[FBergamaschi]

The link does not work at the moment

Anyway, when you say "I need to send the USERPRINCIPALNAME to the visual", what do you mean? The measure will return the username, what do you want to do with that? You will have to filter some column I assume, otherwise I am still not sure I get the point

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[johnt-softcloud]

I've updated the message above with another link.  Posting it here as well.

https://drive.google.com/file/d/1Cab-Z4U--e4JLyhhiD7fDMlykiO0zCYU/view

[FBergamaschi]

Can you please show the error? What do you mean that the slicer no longer works? USERPRINCIPALNAME() returns the email of the connected user but in the desktop it works a bit wierdly. Though I need to undretsand the issue to help you.

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[johnt-softcloud]

 The table visual on the left is not being filtered. The table on the right is being filtered. The table on the right does not have a measure added.

[FBergamaschi]

The measure using USERPRINCIPALNAME has alway the same value (the connected user to power bi Desktop - you, I assume - and nothing to do with the data you have in the tables), that is the reason why the slicer does not apper to work.

If you add the Project Name to the matrix including the PrincipalUserName measure, you should only see the filtered project name, but other combinations will show up

Why are you adding that measure?

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[johnt-softcloud]

We have a custom visual that has the same issue. Our custom visual actually does a write back to SQL Server if user modifies a row. We need to capture who did the modification.

I don't understand why adding a measure will break the relationship,  project_member.project_id = project.id, to the table visual on the left side.

[FBergamaschi]

So when your custom visual write back into SQL, does it also writes bake the user doing the modification, storing that user in a SQL table column (along, I imagine, with a colum saying "modified Yes/No"?)

If that is the case, then you can use a dinamic row level security to show, to each user connected, only its modifications

the role qould be a single role and the filter would be

TableModifications[User that did the modification] = USERPRINCIPALNAME ()

I hope I am getting write what you are trying to do

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[johnt-softcloud]

<<TableModifications[User that did the modification] = USERPRINCIPALNAME () >>
Let me look into row level security as you suggested.