Use UserName in a slicer (without RLS)

[Cookistador]

Hello everyone,

I'm facing an issue with the use of Username() or UserPrincipalName() in a report

I have to build a report to allow my ressources to track the projects on which they are assigned, I cannot used RLS due to the need of this ressources to see more details on other reports using the same dataset

If I use  the following measure:

ProjectRessource =  

IF(SELECTEDVALUE(Ressources[AuthorMail]) = USERPRINCIPALNAME(), 1,0)

It works in the case of a project with a single ressource, but as you know, generally you have more than one ressource in a project...

My datamodel (for this part) is pretty simple 

The ID in the ressources table, is the project ID, so my table ressource contains this kind of information

ID Project	ResourceMail
1	ResourceA@microsoft.com
2	ResourceA@microsoft.com
2	ResourceB@microsoft.com
2	ResourceC@microsoft.com
3	ResourceB@microsoft.com
3	ResourceC@microsoft.com

So with the measure I use, if my ressourceA is connected, he will only be able to see Project with the ID 1

Do you have any idea of how could I fix this issue?

Many thanks in advance for your help 🙂 

[aduguid · Accepted solution]

Try this measure 

ProjectResourceVisibility = 
VAR CurrentUser = USERPRINCIPALNAME()
RETURN
IF(
    CALCULATE(
        COUNTROWS(Ressources),
        FILTER(Ressources, Ressources[ResourceMail] = CurrentUser)
    ) > 0,
    1,
    0
)