Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi @Anonymous
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.
Hi @Anonymous ,
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
Hi @Anonymous ,
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
Hi @Anonymous
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |