cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Filter using USERPRINCIPALNAME()

Hi folks

 

Another day another challenge Smiley Wink

We have RLS in all our reports but are trying to find ways to open up the datasets whilst still showing the "relevant" data for the user. RLS is set up based on a location table that uses filtering from a User / Location List and the USERPRINCIPALNAME() function.

The ideal scenario would be that a list of locations assigned to the user becomes visible in a slicer. By defaultI would set the value to only show those locations whilst enabling the user to also see other locations e.g. through bookmarks.

Hence, I need to make a calculated table. Here is what I got:

 

 I created a simple model:

 

1.jpgSimple model

 

In the StationUser Table I have:

2.jpgupn contains email addresses, sry for the blank out

 

Now I can create a measure to count how many stations I am assigned to:

Count My Stations =

CALCULATE (
    COUNTROWS ( Stations );
    StationUser[upn] = USERPRINCIPALNAME ();
    CROSSFILTER ( Stations[stationid]; StationUser[stationid]; BOTH )
)

And it will return the correct count. So this works.

I can do the same with the calculated table by stating my UPN explicitly in the formula 

My Stations =
CALCULATETABLE (
    VALUES ( Stations[station] );
    StationUser[upn] = "myemail@email.com";
    CROSSFILTER ( Stations[stationid]; StationUser[stationid]; BOTH )
)


BUT, when I use the Prinicpalname() instead of the "myemail@email.com" in a calculated table, it won't let me:
3.jpg

It seems that it is not possible to create a dynamic table based on the USERPRINCIPLENAME () function?

Does anyone have a solution/alternative for this?

Thanks!

4 REPLIES 4
Community Support Team
Community Support Team

Re: Filter using USERPRINCIPALNAME()

Hi @airfreighter

Yes, it is not possible to create a dynamic table based on the USERPRINCIPLENAME () function, you could use the fuction in a measure instead.

Here is a similar thread.

Implementing RLS

 

Best reagrds

maggie

Re: Filter using USERPRINCIPALNAME()

Hi @v-juanli-msft

Thanks for the link, it looks very similiar and I got a step closer. I now have a measure that can tell me whether the current selection is assigned to me or not:

CurrentUPN = USERPRINCIPALNAME()


----AND----


IsCurrentUPN = IF(HASONEFILTER(Stations[station]); IF ( CONTAINS (StationUser;StationUser[upn];[CurrentUPN]); TRUE(); FALSE() ))


But since it is a measure, I can't use it in a slicer. I need to link it to a table somehow.

 

 

Is there a workaround?

Best regards

Community Support Team
Community Support Team

Re: Filter using USERPRINCIPALNAME()

Hi @airfreighter

How about using this formula

Measure:

USERPRINCIPALNAME = USERPRINCIPALNAME()

My Stations =
CALCULATETABLE (
    VALUES ( Stations[station] );
    StationUser[upn] = [USERPRINCIPALNAME];
    CROSSFILTER ( Stations[stationid]; StationUser[stationid]; BOTH )
)

 

Best Regards

Maggie

Re: Filter using USERPRINCIPALNAME()

Hi @v-juanli-msft
Thanks for the help. I tried something similar. With your formula, it still returns an error saying: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

I guess that's really a challenge and is more complex than I thought it would be.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 439 members 4,279 guests
Please welcome our newest community members: