Use the User's login ID to dynamically change a measure

[DataDiva]

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?? 

[v-lili6-msft]

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

[DataDiva]

I'm not sure how to attach a data file. But here's some photos of what I thought I could do:

Raw datawhat fred should seeRLS tablewhat brenda should see

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).

[v-lili6-msft · Accepted 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

[DataDiva]

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".