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.
I'm trying to figure out how to create a new measure that incorporates the user's login ID information without applying a RLS filter.
Here's the situation: I have a table that has these fields:
RecordID
HospID
HospName
HospCategory
... and lots of attribute fields
And another security table that has:
UserID
HospitalID
My users need to be able to see the aggregated values of this raw table at the HospCategory level (e.g. Urban, Rural) but not be able to see the results at the hospital or record level--EXCEPT for their own hospital. What I thought I could do was write a formula like our RLS formula but instead of using it to filter out records that don't match their assigned hospital ID(s), it would replace any hospital names that they don't have permission to see with the hospital category. In a non-technical formula, something like:
[masked name]=if the related [HospID] in the security table of the logged [UserID] does not equal [HospID] in a row in the data table, then [HospCategory] else [HospName] end
Any ideas how to actually write and deploy a formula that can do that in Power BI??
Solved! Go to Solution.
hi, @DataDiva
If you could try this way:
Step1:
Create a dim RLS table with userID and the associated hospital ID(s).
Step2:
Then use Username function to create a Log Measure.
Step3:
Create two measure as below:
Append = CALCULATE(CONCATENATEX(CALCULATETABLE(VALUES(dim[Hospital]),FILTER(dim,dim[UserID]=[Log Measure])),[Hospital],","))
if visibled = CONTAINSSTRINGEXACT([Append],SELECTEDVALUE(raw[Hosp ID]))
Step4:
You could use [is visibled] in visual level filter to filter data.
Result:
When usename is brenda
When usename is Fred
here is pbix file, please try it.
Best Regards,
Lin
hi, @DataDiva
You may refer to this blog to try it with the same logic:
https://docs.microsoft.com/en-us/dax/username-function-dax
https://radacad.com/dynamic-row-level-security-with-manager-level-access-in-power-bi
If not your case, Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Lin
I'm not sure how to attach a data file. But here's some photos of what I thought I could do:
I'm still not sure how to use the Username function to do this because it returns the userID, not the associated hospital ID(s).
hi, @DataDiva
If you could try this way:
Step1:
Create a dim RLS table with userID and the associated hospital ID(s).
Step2:
Then use Username function to create a Log Measure.
Step3:
Create two measure as below:
Append = CALCULATE(CONCATENATEX(CALCULATETABLE(VALUES(dim[Hospital]),FILTER(dim,dim[UserID]=[Log Measure])),[Hospital],","))
if visibled = CONTAINSSTRINGEXACT([Append],SELECTEDVALUE(raw[Hosp ID]))
Step4:
You could use [is visibled] in visual level filter to filter data.
Result:
When usename is brenda
When usename is Fred
here is pbix file, please try it.
Best Regards,
Lin
The idea suggested here is very close. But there's one problem. The formulas as created have a status filter to a user (set as "Fred" in teh example workbook). I need this to be dynamic based on who is signed in, which means I need to use "username()" to get that. But you can't use that command in a column, which my final result needs to be, in order to properly filter. And even if I use it in a measure, and then try to use that measure in a column--without ever including "username()" in the column formula, I still get an error saying "can't use username in a calculated column".
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.