Cross filtering between two facts and common dimension tables

[don_writer]

My sincerest apologies for not being able to share any files,

I have three fact tables:

srcActive

srcTerminations

srcBenchmarks

srcActive and srcTerminations have the following common fields:

ID

Year

age_bracket

headcount

srcBenchmarks has:

Year

age_bracket

Turnover%

I have a Date Dimension table.

I have a measure that pulls from srcActive and srcTerminations:

TurnoverAnnual = DIVIDE(sum(srcTerminations[headcount]),sum(srcActive[headcount]))

(its slightly more comlicated than that but the details shouldn't change the outcome)

I have two bridge tables for age_bracket and year

I want to display terminations by age_bracket in a clustered column chart

And add the srcBenchmarks Turnover% as a Line (obviously a Line and Clustered Column Chart)

My model looks something like this: (ignore the column names... the relationships should be obvious between age_bracket and year)

The issue is that I can't have a bi-directional relationship on multiple fact (src) tables. Whichever fact table I make the bi-directional one the other table's measures flatten out (predicatably).  I suspect my measure needs to include a CALCULATE and a filter of some sort but I am uncertain how I should go about this and what functions...ALL? CROSSFILTER? 

Your input is greatly appreciated!

Best regards,

~Don

[v-yuta-msft]

@don_writer ,

Maybe you can try function like below:

TurnoverAnnual =
CALCULATE (
    DIVIDE ( SUM ( srcTerminations[headcount] ), SUM ( srcActive[headcount] ) ),
    CROSSFILTER ( srcTerminations[headcount], srcActive[headcount], BOTH )
)

Community Support Team _ Jimmy Tao

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

[don_writer]

Thank you Jimmy. 

Question: why am I creating the crossfilter on the measurable values? Wouldn't I crossfilter on the shared axis variable?
Assuming I make the srcActive[age_bracket] the bi-directional relationship? Or am I completely misunderstanding crossfiltering?

TurnoverAnnual =
CALCULATE (
    DIVIDE ( SUM ( srcTerminations[headcount] ), SUM ( srcActive[headcount] ) ),
    CROSSFILTER ( srcTerminations[age_bracket], refMasterAgeBracket[age_bracket], BOTH )
)

 Much appreciated!

~Don