DAX measure over different tables. Make interaction between tables.

[jkim3743]

DAX measure over different tables. Make interaction between tables.

I need to make interactions between tables.

1. departments table: contains department name.

2. employees table: contains sex. 

I created male/female count. I want to have interaction so when I click each department button, gender distribution changes by each department.

Here's my measure: 

Male Count = CALCULATE(COUNTA(employees[sex]), (employees[sex]) = "M")

Female Count = CALCULATE(COUNTA(employees[sex]), (employees[sex]) = "F")

However, when I click each department button, gender distribution chart does not change even if I turn interaction on.

Here's my relationships and dashboard

I think it has to deal with 'dept_emp' table since there's no direct relationship between 'employees' and 'departments' tables.

Can somebody help?

[Jihwan_Kim]

Hi,

Please try something like below, if you cannot change the filter direction in the data model.

Male Count =
CALCULATE (
    COUNTROWS ( SUMMARIZE ( dept_emp, employees[sex] ) ),
    employees[sex] = "M"
)

Female Count =
CALCULATE (
    COUNTROWS ( SUMMARIZE ( dept_emp, employees[sex] ) ),
    employees[sex] = "F"
)

[Anonymous · Accepted solution]

Hi @jkim3743 ,

I created a sample pbix file(see the attachment), please check if that is what you want. Please change the Cross-filter direction between the table 'dept_emp' and 'employees' as "Both" :

Then the count will be dynamically changed by the department selections:

Best Regards