Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All,
I am coming from Tableau world and started working in Power BI . Need your help for below issue .
My requirement is to display data based on user login in few visuals & in few visuals all the data. So I cannot implement the normal RLS feature .
In one visual need to show the Team's data of User Logged in , I have 2 tables ( Users & Tasks Export ) and are joined based on email id .
I am using below DAX formula to achieve this , its not working
RLS User Team Tasks =
// To get the team name from "Users" table based on user login
var CurrentUserTeam=CALCULATE( MAX('Users'[Team ]),'Users'[UserMail]=USERPRINCIPALNAME())
// To filter the 'Task" table using above calculated variable to show only logged in User's team data
var CurrentUserTeamFilter=FILTER('Tasks','Tasks'[Team]=CurrentUserTeam)
return
SWITCH(
TRUE(),
SELECTEDVALUE('Tasks'[Team])=CurrentUserTeam, CALCULATE( [#Total Task],CurrentUserTeamFilter),
BLANK()
)
Upon debugging above DAX formula second variable is not getting filtered as per expectation , its giving an error "The expression refers to multiple columns . Multiple columns cannot be converted to scalar value " . Can you please let me know if there is any alternative way to achieve this .
If User1 or User4 or User7 logins they need to see below highlighted blue data
Users Table:
Team | User | UserMail |
Team 1 | User1 | User1@cyz.com |
Team 1 | User4 | User4@cyz.com |
Team 1 | User7 | User7@cyz.com |
Team 2 | User2 | User2@cyz.com |
Team 2 | User5 | User5@cyz.com |
Team 2 | User8 | User8@cyz.com |
Team 3 | User3 | User3@cyz.com |
Team 3 | User6 | User6@cyz.com |
Team 3 | User9 | User9@cyz.com |
Tasks Table:
Team | User | User Mail | Task ID |
Team 1 | User1 | User1@cyz.com | Task1 |
Team 1 | User4 | User4@cyz.com | Task2 |
Team 1 | User7 | User7@cyz.com | Task3 |
Team 2 | User2 | User2@cyz.com | Task4 |
Team 2 | User5 | User5@cyz.com | Task5 |
Team 2 | User8 | User8@cyz.com | Task6 |
Team 3 | User3 | User3@cyz.com | Task7 |
Team 3 | User6 | User6@cyz.com | Task8 |
Team 3 | User9 | User9@cyz.com | Task9 |
Thank you,
Kalyan.
Solved! Go to Solution.
Hi @Anonymous ,
Do you want user 1 to be able to see the information of user 1, user 4 and user 7?
If so, please create the following relationship
Then create the following role
Test result
You will find that the box circled in red is not displayed.
If you want to display it, you need to create a new table as follows.
Pay attention to the direction of relationships
You can check more details from the attachment.
Reference: https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear Stephen,
Thank you so much for your time and explanation . I will try to impliment the solution and let you know .
Here my concerns I need to show some visuals on entire data set as per requirement , is it possible ? Please let me know
Thanks,
Kalyan
Hi @Anonymous ,
Yes, it is possibile. As you can see from the content I just replied, the visual of the table is also one of the many visuals.😊
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Do you want user 1 to be able to see the information of user 1, user 4 and user 7?
If so, please create the following relationship
Then create the following role
Test result
You will find that the box circled in red is not displayed.
If you want to display it, you need to create a new table as follows.
Pay attention to the direction of relationships
You can check more details from the attachment.
Reference: https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |