Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi folks
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] = "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:
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!
Hi @Anonymous
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
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.
Hi
CROSSFILTER is not allowed here, write the next
My Stations =
CALCULATETABLE(
VALUES(Stations[station] )
;FILTER(
VALUES(Stations[station])
;CALCULATE(
COUNTROWS(StationUser)
;StationUser[upn] = [USERPRINCIPALNAME]
)>0
)
)
Hi @Anonymous
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.
Best reagrds
maggie
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
Can you find the solution to this? if yes, share with me also please
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |