topic Re: Dynamic Row Level Security-with Distinct Location ID in Developer

Dynamic Row Level Security-with Distinct Location ID

reddy6400 — Fri, 31 Jul 2020 05:00:32 GMT

Hi All,

Please suggest how to implement Dynamic Row Level Security for my below scenario.

I have 2 tables.One is main report data table(sales by Location) and 2nd table is Location table.

My 2nd table has multiple Location Id's.I have to select Distinct location for the Email user and join that Location Id to the main data table to show the data in a repor With out duplicate records.

Below is the screen shot.

Thank in Advance.

Raj

Re: Dynamic Row Level Security-with Distinct Location ID

nandukrishnavs — Fri, 31 Jul 2020 05:46:06 GMT

@reddy6400

You have to create a relationship between two tables using the Location ID column.

Table2[Location Id] many to one Sales_by_Location[Location Id] (Both direction)

Then create a Role in Table2.

[Email] = userprincipalname()

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Here is my latest blog

https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/1097786

Re: Dynamic Row Level Security-with Distinct Location ID

reddy6400 — Fri, 31 Jul 2020 19:46:38 GMT

@nandukrishnavs

Sorry,Data in SALES_BY_LOCATION has same multiple Location ID's.
I have mutile Location ID's in both the tables(many to many).

I have to get distinct Location ID from Table 2 and join to table1.

Re: Dynamic Row Level Security-with Distinct Location ID

reddy6400 — Fri, 31 Jul 2020 20:32:30 GMT

@nandukrishnavs ..Getting below error when i join both the tables.

Re: Dynamic Row Level Security-with Distinct Location ID

nandukrishnavs — Sat, 01 Aug 2020 02:23:16 GMT

@reddy6400

What is the error message?

if your location id and sales combination is repeating, you can select both column and remove duplicate. If the sales values are different, then you can group by location id. (Edit Query)

Re: Dynamic Row Level Security-with Distinct Location ID

reddy6400 — Sat, 01 Aug 2020 02:37:27 GMT

@nandukrishnavs

When i drag Email ID(Table-2) to the report.It is giving me below error.

Re: Dynamic Row Level Security-with Distinct Location ID

nandukrishnavs — Sat, 01 Aug 2020 03:02:07 GMT

@reddy6400

Click on see details, and see what is the error message.