Forum Discussion

datajunkie_29's avatar
datajunkie_29
Helper I
2 years ago
Solved

Dynamic RLS with Hierarchy for Embedded Paginated Reports in Power BI Desktop

Hello Power BI Community,   I hope this message finds you well. I am reaching out to seek your expertise and guidance on a challenge I am currently facing with implementing dynamic Row-Level Securi...
@PowerBI Builder Paginated Report
Dynamic RLS
needhelp
Power BI Desktop
Role level security - multiple levels
  • datajunkie_29's avatar
    datajunkie_29
    2 years ago

    Hello All,

     

    I was finally able to get it resolved and have dynamic RLS implemented in Paginated report. Please find my steps below:

    • Connected my Paginated report to the Power BI Dataset on which dynamic RLS was implemented.
    • Utilized the PATH column created using a Power BI DAX-supported function, to filter the dataset based on the signed-in user's USERID.
    • Add a Calculated column in the dataset, for accurate matching of the user's ID with PATH values, considering both the beginning and end of the path, the filter expression for the calculated column was as follows:
    • LoggedInAs =

    IIf(Fields!ManagerID.Value = User!UserID Or InStr("|" & Fields!ManagerPath.Value, "|" & Parameters!UserUserID.Value & "|") > 0 Or InStr(Fields!ManagerPath.Value & "|", "|" & Parameters!UserUserID.Value) > 0, 1, 0 )

    • Following this, I applied a report-level filter where I set 'LoggedInAs = 1'.

    Please adjust this expression based on your dataset structure and the way path information is stored. Apply the filter based on this calculated field in your report to achieve the desired result. 

     

    Hope this helps!

    Thanks

datajunkie_29's avatar
datajunkie_29
Helper I
2 years ago

Hello All,

 

I was finally able to get it resolved and have dynamic RLS implemented in Paginated report. Please find my steps below:

  • Connected my Paginated report to the Power BI Dataset on which dynamic RLS was implemented.
  • Utilized the PATH column created using a Power BI DAX-supported function, to filter the dataset based on the signed-in user's USERID.
  • Add a Calculated column in the dataset, for accurate matching of the user's ID with PATH values, considering both the beginning and end of the path, the filter expression for the calculated column was as follows:
  • LoggedInAs =

IIf(Fields!ManagerID.Value = User!UserID Or InStr("|" & Fields!ManagerPath.Value, "|" & Parameters!UserUserID.Value & "|") > 0 Or InStr(Fields!ManagerPath.Value & "|", "|" & Parameters!UserUserID.Value) > 0, 1, 0 )

  • Following this, I applied a report-level filter where I set 'LoggedInAs = 1'.

Please adjust this expression based on your dataset structure and the way path information is stored. Apply the filter based on this calculated field in your report to achieve the desired result. 

 

Hope this helps!

Thanks