Reply
MichaelBN
Frequent Visitor

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:
expt 1.PNG

 

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:

 

expt 2.PNG

 

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. 

 

excpt 3.PNG

 

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]. 

3 REPLIES 3
v-diye-msft
Community Support
Community Support

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 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
judspud
Solution Supplier
Solution Supplier

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

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

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.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)