how to get slicer filter of one table to affect one another table which has a middle table?

[Farnaz_Raj98]

Hi every one,

I have 3 tables as below that have relationships. (Arrows show the columns that are related).

I want to apply dvice filter on 2 cards. one of them in number of etl_id which is calculated from Users table and the other one is number of pxid that is calculated from Users_pxid table. When I filter device for example on mobile it just affects on count of pxid and do not affect on count of etl_id card. any one can help me please?

Tables Structure:
users:

etl_id (Primary Key, FK)

userName

Gender

users_pxid:

actic_etl_userid(FK)

pxid(FK)

pxid:

pxid(PK,FK)

Device

the following query return the right answer in SQL but on power BI the device filter don't:
SELECT COUNT(u.userid)
FROM users u INNER JOIN users_pxid up
ON u.etl_id = up.actic_etl_userid
INNER JOIN pxids p
ON p.pxid = up.pxid
WHERE p.device = 'Mobile'

[v-yanjiang-msft]

Hi @Farnaz_Raj98 ,

According to your description, in my understanding, you want the pxids table also filter the users table. 

If this is the case, try to change the cross filter direction between users_pxid and users to Both.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

For the etl Id card, use

Etl ID = DISTICNTCOUNT('users_pxid'[arctic_etl_userid)

[PaulDBrown]

What measures are you using?

[Farnaz_Raj98]

on count of etl_id card I use "Count(distinct) etl_id" from users table and on count of pxid card I use " Count(distinct) pxid" from pxid table