Forum Discussion
Need help with DAX - FILTER function
Hey,
I'm trying to get a new filtered table of 'team projects' table wherein the logged in user is a member of the project. The login ID of the users are stored in 'work_email' in 'team users' table. The ID of 'team users' is connected to 'project members' table, where the list of team members of the project is stored.
To summarise:
'project members'[project_ID] *:1 'team projects'[ID]
'project members'[member_ID] *:1 'team users'[ID]
Can someone help me with the DAX to get the filtered table?
4 Replies
could you pls provide the sample data and expected output?
Projects Table:
Project ID Name Due Date 1 Project Name 1 03-02-2023 2 Project Name 2 05-02-2023 3 Project Name 3 07-02-2023 4 Project Name 4 09-02-2023 Users Table
User ID Name Email 1 James [email protected] 2 Rajan [email protected] 3 Anil [email protected] Team Members Table
ID Project ID User ID 1 1 1 2 1 2 3 1 3 4 2 1 5 2 3
Expected Output
1. if Power BI service logged in username is '[email protected]'
2. James is the member of 1st and 2nd project as can be seen from team members table.
Project ID Name Due Date 1 Project Name 1 03-02-2023 2 Project Name 2 05-02-2023
based on requirement u need to implement Dynamic RLS on the Power BI Report. U can follow the link to implement Dynamic RLS. If it helps you please accept the solution.
https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple
Try this maybe it works :
Filtered Table =
FILTER(
'team projects',
COUNTROWS(
FILTER(
'project members',
'project members'[project_ID] = 'team projects'[ID] &&
'project members'[member_ID] IN VALUES('team users'[work_email])
)
) > 0
)
