cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

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

1.JPG

When usename is Fred

2.JPG

 

here is pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

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

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

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

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

 

Raw dataRaw datawhat fred should seewhat fred should seeRLS tableRLS tablewhat brenda should seewhat 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).

Highlighted
Community Support
Community Support

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

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

1.JPG

When usename is Fred

2.JPG

 

here is pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

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

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors