Forum Discussion

id013's avatar
id013
Helper V
6 years ago

Filtering with Multiple Relationships

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 A25Male1500
CITY A25Female1300
CITY B25Male390
CITY B25Female500

 

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