Forum Discussion
DAX expression in creating role
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
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 [email protected] 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:
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
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
7 Replies
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 [email protected] 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:
If this explanation is helpful, please consider accepting this a solution. Your kudos are welcome too.
Best,
C.S.N. Raja
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!
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
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 [email protected] 100 10 [email protected] 100 10 [email protected] 100 20 [email protected] 101 30 [email protected] 101 30 Sorry i could find how to attached the xls file or i guess its restricated from my company
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 [email protected] 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:
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
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
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
