cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wjones
Frequent Visitor

Three Table Relationship Ambiguity: Cant Create Direct Active Relationship

I have three tables. 

1. DB Dates: My date table, which I use to synchronize dates for my slicers and axes and such.

2. GA Pageviews: List of pages and their count of pageviews for each day

3. GA Form Submissions: List of Forms, a URL where the form is hosted, and a count of submissions of these forms for each day

 

As you can see in the attached photo of my model, I have relationships between the date table and both other tables on "Date". 

I want to sync "Page" and "URL" from Pageviews and Form Submissions respectively.

Doing what the warning box asks would ruin the functionality of my report. I dont understand what the issue is here, but I need to find a solution that lets me associate pageview counts with the page where a form is hosted while still being able to use the same date slicer on the report.

 

 

wjones_1-1642021179905.png

 

2 ACCEPTED SOLUTIONS
bcdobbs
Super User
Super User

Hi @wjones 

 

You need a page dimension which is a distinct list of all the pages.

You can use PowerQuery or do it in DAX with a calculated table:

Page = 
DISTINCT (
	UNION (
		DISTINCT ( 'GA Pageviews'[Page] ),
		DISTINCT ('GA Form Submissions'[URL] )
		)
	)


From there you can create one to many relationships to each table.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

v-luwang-msft
Community Support
Community Support

Hi @wjones ,

Bi directional relationships are one way of solving a specific problem where you need to filter a lookup table from the contents in a data table. Not every lookup table will need this and there are other ways to solve the problem.  I suggest you

 

1. turn off bidirectional cross filtering on all your tables.

2. Hide the foreign key in every data table. This way users won't accidentally use is instead of the primary key in the lookup table. 

3. When (and only when) you need a bi directional relationship for what you are doing, turn it on for that single table. 

4. If you ever need 2 that conflict, then use the many to many pattern. I explain it here https://www.powerpivotpro.com/2014/08/filters-can-flow-up-hill-via-formulas-that-is/ and also here http://exceleratorbi.com.au/many-many-relationships-dax-explained/

 

And similar question refer:

https://community.powerbi.com/t5/Desktop/Ambiguity-relationship/m-p/560891 

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @wjones ,

Bi directional relationships are one way of solving a specific problem where you need to filter a lookup table from the contents in a data table. Not every lookup table will need this and there are other ways to solve the problem.  I suggest you

 

1. turn off bidirectional cross filtering on all your tables.

2. Hide the foreign key in every data table. This way users won't accidentally use is instead of the primary key in the lookup table. 

3. When (and only when) you need a bi directional relationship for what you are doing, turn it on for that single table. 

4. If you ever need 2 that conflict, then use the many to many pattern. I explain it here https://www.powerpivotpro.com/2014/08/filters-can-flow-up-hill-via-formulas-that-is/ and also here http://exceleratorbi.com.au/many-many-relationships-dax-explained/

 

And similar question refer:

https://community.powerbi.com/t5/Desktop/Ambiguity-relationship/m-p/560891 

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

bcdobbs
Super User
Super User

Hi @wjones 

 

You need a page dimension which is a distinct list of all the pages.

You can use PowerQuery or do it in DAX with a calculated table:

Page = 
DISTINCT (
	UNION (
		DISTINCT ( 'GA Pageviews'[Page] ),
		DISTINCT ('GA Form Submissions'[URL] )
		)
	)


From there you can create one to many relationships to each table.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!