Another day another challenge
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 default, I 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:
In the StationUser Table I have:
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] = "email@example.com"; CROSSFILTER ( Stations[stationid]; StationUser[stationid]; BOTH ) )
BUT, when I use the Prinicpalname() instead of the "firstname.lastname@example.org" in a calculated table, it won't let me:
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 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()
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?
How about using this formula
Measure: USERPRINCIPALNAME = USERPRINCIPALNAME() My Stations = CALCULATETABLE ( VALUES ( Stations[station] ); StationUser[upn] = [USERPRINCIPALNAME]; CROSSFILTER ( Stations[stationid]; StationUser[stationid]; BOTH ) )
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.