Three Table Relationship Ambiguity: Cant Create Direct Active Relationship

[wjones]

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.

[bcdobbs · Accepted solution]

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.

[v-luwang-msft · Accepted solution]

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 · Accepted solution]

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.