Forum Discussion
Question about data model and inactive relationship
Hi everyone,
I'm creating a report from Google Analytics, and since the data distorts if the table pulls a lot of data, because the google api starts to sample, I have created multiple tables rather than one large condensed one. I have a problem, I would like to filter based on month of year, and country, so I have created a date table, and a country table with all countries listed.
I have 3 Google Analytics tables, one that shows user counts on a daily basis with country info (connected to date table and country table), another one with monthly metrics that don't collapse too much (connected to date table based on custom month of year column, and country table), and then I have a 3rd that shows the number of users that landed on a particular page on a monthly basis along with country. I connected this 3rd table to the date table, and when I try to connect it to the country table, it makes it inactive. How come? How come this didn't happen for the first 2 tables, that I have connected both to date and country table?
I am trying to make it so that if I filter based on month of year from the date table, all 3 Google Analytics tables are affected, and if I click on a country that is using data from the 2nd table, all 3 google analytics tables are affected as well.
The Google Analytics tables are numbered based on order in the model going down, so top is 1, bottom is 3. You can see the 3rd GA table only has an inactive relationship with Country. When I click on a visual that is using table 2 values and country from country table, nothing happened to a visual using table 3.
Here's my model:
and heres a very dry working report:
Thank you for the help,
Denisse
ruesaint_denis Great question!
This is because of your bi-directional relationships - DON"T USE cross-filter set to BOTH unless you are a PRO and know you need them. 😀 Alberto has a great video about this here: https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
In Power BI, we can only have 1 active path between any two tables.
In your current data model, you have the Green path active already, so can't activate the blue path you're trying to add:
What you need, is single cross filter direction on all relationships. To do this, you need to add a column to your Monthly Metrics tables that give you the start of month (usually you can just convert the Month, YYYY column to Date data type and Power BI/Power Query will automatically do the rest for you). Then relate this to your Date Table Date column as a one to many:
1 Reply
ruesaint_denis Great question!
This is because of your bi-directional relationships - DON"T USE cross-filter set to BOTH unless you are a PRO and know you need them. 😀 Alberto has a great video about this here: https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
In Power BI, we can only have 1 active path between any two tables.
In your current data model, you have the Green path active already, so can't activate the blue path you're trying to add:
What you need, is single cross filter direction on all relationships. To do this, you need to add a column to your Monthly Metrics tables that give you the start of month (usually you can just convert the Month, YYYY column to Date data type and Power BI/Power Query will automatically do the rest for you). Then relate this to your Date Table Date column as a one to many: