- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Problem with city data and a forced many-to-many relationship
I have two tables and I want to compare values between the two. The two tables both have a COUNTROWS() measure, and a City column (I basically want to measure rows per city) --- N.B The tables also have other columns such as user_id, longitude and latitude etc.
The tables both look like this:
For some reason, when I try and make a relationship between the two, it forces a many-to-many relationship. To circumvent this problem, I have introduced a new custom table with City Names, which only has distinct entries. It looks like this:
In the picture below, I want to make a relationship between the left table[By] and the middle table [Bynavn], AND between the right table[city] and the middle table [Bynavn], in order to get a common denominator.
However, when I make the relations stated above I get a forced many-to-many relationship for both tables, and I am trying to figure out why.
[Both tables are connected to a 'Date' table, if that matters].
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @MichaelBN
You can refer to the similar solved post for help:
https://community.powerbi.com/t5/Desktop/Forced-to-use-many-to-many-relationship/td-p/681147
https://community.powerbi.com/t5/Desktop/Forced-into-many-to-many-relationship/td-p/486504
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @MichaelBN
Is it possible you have multiple Nulls in the bridging table?
if so, filter these out in the query editor and retry creating the relationship 🙂
Thanks,
George
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @judspud
I don't seem to have any null values in either of the three tables. Any other idea about what might be causing this issue?
/Michael
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
08-10-2018 08:27 AM | |||
12-06-2022 01:13 AM | |||
01-18-2024 08:25 AM | |||
11-16-2023 07:55 PM | |||
04-16-2024 06:03 AM |
User | Count |
---|---|
102 | |
74 | |
42 | |
39 | |
30 |
User | Count |
---|---|
161 | |
87 | |
64 | |
46 | |
42 |