Filtering with Multiple Relationships

[id013]

Hi,

I have a sales and marketing dataset that I want to connect with some population data. The Marketing dataset has a single row for each time a person was contacted, and if that contact resulted in a sale. It also has some demographic information, city, age & gender specifically. 

The population dataset has population data broken down by city, age and gender. For Example:

CITY A	25	Male	1500
CITY A	25	Female	1300
CITY B	25	Male	390
CITY B	25	Female	500

I want to do a calculation of contact rate with regards to population. I would take the count of contacts and divide it by the population of city A. So if 80 people of age 25 were contacted in City A I would divide 80/2800 to give me the contact rate.

So both the marketing dataset and the population dataset don't have unique values for age, gender & city. So to facilitate the relationship I've created 3 dimension tables, one just with unique city names between the 2 tables, one with unique ages between the 2 tables and 1 with the unique genders between 2 tables connected like this:

All relationships have a cross filter direction of single. 

So I want to be able to filter by Age & Gender. What I'm find though is I can only filter by one. If I set the cross filter direction between Marketing and dim_age then I can filter population by age and the same for gender. But Power BI won't let me set both relationships to have a cross filter direction of both. Is there a way to do this? I saw some posts that said you had to concatenate them but I tried it and the population numbers don't seem right.

Thanks

[MFelix]

Hi @id103,

You can replicate the filter both ways using the crossfilter function on your measure.

https://docs.microsoft.com/en-us/dax/crossfilter-function

https://m.youtube.com/watch?v=nRTOHmqyhoo

May help you achieve what you need.