How to make 2 dims tables affect each other through a fact table

[Anonymous]

Hi all
I have the follwoing model : 

I want to be able to clalcute Gender distribution depend on Year and month ..
As you see Dim Employees and Dim Calendar have no direct connection between each other , so I think this should be done through the fact table Fact Departments.
The relation is (one to many) ..
Thanks in advance
Regards

[ThxAlot]

If you wish to put your skill to the next level, read artiles on "Expanded Table in Power BI", such as this one:

Expanded tables in DAX - SQLBI

Keep your 1:* relationshipes in the star schema as it is and apply expanded table knowledge to the calculation to implement filtering from many end to 1 end (fact table filters dim table). 

[Dangar332]

Hi, @Anonymous 

you can change filter direction to both side manually in  model view without changing relationship b/w fact table and dimension table

[Anonymous]

Thank you 
I cant change the relationship direction , this is a very big model and changing relaionship direction will cause many issues 🙂 

[Dangar332]

Hi, @Anonymous 

make *-*(many to many) relationship bw fact department and dim employee

or

other wise  in your dax code use crossfilters function to change direction of filter 

[Anonymous]

Thank you 
I cant change the relationship direction , this is a very big model and changing relaionship direction will cause many issues .. 
Could you help me writing the DAX scripte to get the total number of 'male' when using crossfilters function ?
Thanks in adavnce 
Regards 

[Dangar332 · Accepted solution]

Hi, @Anonymous 

just adjust table and column name

try below code 

result =
   calculate(
      sum('dim employees'[gender]),
      'dim employees'[gender]="male",
       crossfilter('fact departments'[employee_bk],dim employees'[employee_bk],both)
        )