Need help with DAX - FILTER function

[jinujames12]

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?

@amitchandak 

[bhelou]

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
)

[Anonymous]

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

[ryan_mayu]

could you pls provide the sample data and expected output?

[jinujames12]

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	james@jnj.com
2	Rajan	rajan@jnj.com
3	Anil	anil@jnj.com

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 'james@jnj.com'

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