Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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: 

ruesaint_denis_0-1633045599332.png

 

and heres a very dry working report: 

ruesaint_denis_1-1633045797753.png

 

Thank you for the help, 

Denisse

 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@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: 

AllisonKennedy_1-1633049696401.png

 

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:

 

AllisonKennedy_0-1633049609248.png

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

1 REPLY 1
AllisonKennedy
Super User
Super User

@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: 

AllisonKennedy_1-1633049696401.png

 

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:

 

AllisonKennedy_0-1633049609248.png

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.