cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
airfreighter Regular Visitor
Regular Visitor

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

airfreighter Regular Visitor
Regular Visitor

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

airfreighter Regular Visitor
Regular Visitor

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.