DAX expression in creating role

[navin_Learning]

Dear All,

So, i have two table

Policy table

Policy	RO Code	SO code	Sum insured
123	2	10	2000
124	3	20	3000
125	6	30	4000

User table

User	RO Code	SO code
abc@loggedin	2	10
def@loggedin	3	20
ghi@loggedin	6	30

Now i am still learning, so please bear with me.

Question: What i want is to create a dax in manage role, which will fetch who is logged in power bi service and will filter the data/report based on his/her RO code & SO code.

Output: As in a sample data above,

if abc@loggedin is logged in power bi server,

then he should only see data/report related to RO code - 2 & SO code - 10

Thank you!

Regards,

Navin

[RajaCSN · Accepted solution]

Hi @navin_Learning,

I have created a simple solution that will take care of your requirement using CALCULATETABLE function, which unfortunately will not work with Direct Query models.

As long as you are going to use the import option (from any source), this method will work fine.

As per sample data provide by you, the unfiltered table will look as below:

As per your condition, user PQR@XYZ.COM can see only one record (ROCD=100, SOCD=20) and that happens.

Another sample for user MNO is also shown below.

You can see the same results for MNT also since his combination is also 101-30.

I hope this is what you have described as your requirement and you can see it happening. 

You can download the Excel and PBIX files from this link, if you want:

RLS2-06-06-2023

Please consider accepting this as a SOLUTION so that others can make a note of this and check when needed.  Your Kudos are also welcome.

Best Regards,

C.S.N. Raja

[navin_Learning]

Dear Raja,

This worked perfectly fine, however just one more requirement has been added and that is out of the user list some should have full access.

So how to flag them?

Thank you for your valuable time

[RajaCSN · Accepted solution]

1. In the source data you can type ALL for either ROCD or SOCD columns or for both the columns as the case may be.

2. Scenario-1:  ROCD ALL BUT SOCD IS LIMITED TO 75

3. Scenario-2: ROCD IS LIMITED TO 100 BUT SOCD IS ALL

4. Scenario-3: ROCD = ALL & SOCD = ALL - Now all the records will be displayed

5. Modified DAX code:

6. Please note - both the tables are not connected. The ROCD & SOCD columns are converted into text, since I am using the "ALL" string in them.

Hope this helps.

Regards,

C.S.N. Raja, MCT

Power BI Trainer & Consultant

[navin_Learning]

Thank you so much for your showing interest in my requirement.

Here is the attached sample data in .XLS format, again thank you for your time & attention

Policy data.

Policy	ROCD	SOCD	Sum
1234	100	10	2000
5678	100	10	2000
91011	100	20	3000
121314	101	30	4000
151617	101	30	5000
151618	133	75	600
151617	133	75	800
151615	144	52	9000
151617	144	52	1222

User data

User	ROCD	SOCD
abc@xyz.com	100	10
def@xyz.com	100	10
pqr@xyz.com	100	20
mno@xyz.com	101	30
mnt@xyz.com	101	30

Sorry i could find how to attached the xls file or i guess its restricated from my company

[RajaCSN · Accepted solution]

Hi @navin_Learning,

I have created a simple solution that will take care of your requirement using CALCULATETABLE function, which unfortunately will not work with Direct Query models.

As long as you are going to use the import option (from any source), this method will work fine.

As per sample data provide by you, the unfiltered table will look as below:

As per your condition, user PQR@XYZ.COM can see only one record (ROCD=100, SOCD=20) and that happens.

Another sample for user MNO is also shown below.

You can see the same results for MNT also since his combination is also 101-30.

I hope this is what you have described as your requirement and you can see it happening. 

You can download the Excel and PBIX files from this link, if you want:

RLS2-06-06-2023

Please consider accepting this as a SOLUTION so that others can make a note of this and check when needed.  Your Kudos are also welcome.

Best Regards,

C.S.N. Raja

[RajaCSN]

Hi,

You need to use the DAX function USERPRINCIPALNAME() to achieve this ROW LEVEL SECURITY (RLS). 

RLS should be created in PowerBI Desktop and should also be implemented in PowerBI Services.
I have simulated your data and here is sample image. When abc@xyz.com is logged-in, the user can see only relevant data.

The actual table will show all the data (for the creator) as in this image:

If you want to see the file in which I have simulated this, please download it from here:

RLS SAMPLE.pbix

If this explanation is helpful, please consider accepting this a solution. Your kudos are welcome too.

Best, 

C.S.N. Raja

[navin_Learning]

Thank you dear Raja for your response, much appreciated!

I see that you have created relationship betweek ROCD and created role to filter the user.

Sorry not to mention it earlier, this is more complex

RO Code has multiple SO code, hence user should see data combining the RO & SO code.

Multiple user should see the same RO & SO code data

RO - regional code

SO - state code

So one state will have multiple people looking after the same data.

I  hope am able to explain my requirement, thank you for your help!

[RajaCSN]

Creating RLS in complex scenarios is really interesting. Your requirement is pretty much possible to be implemented. 

Assume that I have a report similar to this image:

And a set of dynamic roles similar to this image:

I can limit the results as per combination of various fields (columns) for a given user. An example is shown below:  (All states, all categories but only consumer segment)

Sorry, unable to post my Excel, PBIX files here since they need to be santized before posting in a public domain. If you provide with me a skeleton dataset with your requirements, I shall create the roles and then share it with you.

Best Regards,

C.S.N. Raja