Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create relationship detects many to many instead of one to many

Hi community,

 

please help me with the issue I'm facing.

 

I have a table with items (ID, description). There is one item that was created twice by mistake, once normal and once with one space as the last character of ID. Because of that pair of ID's model is creating many to many relationship. It seems like ending space is ignored while checking the type of relashionship. Is this a bug or am I missing something?

I don't want to remove either of the ID's as in real it is not duplicate value.

I would appreciate any help.

 

Item IDDescription

1AItem 1
1A Item 1 with space
2AItem 2
3BItem3

 

DateItem IDQuantity

15.01.20231A10
15.01.20231A 12
15.01.20231A15
15.01.20233B20
16.01.20231A -5
16.01.20231A -3
16.01.20231A6
16.01.20232A5
16.01.20232A4

 

Regards,

1 ACCEPTED SOLUTION
SidTrengove
Advocate III
Advocate III

Hey!

 

Simple but annoying answer to this one. 

 

If the relationship is based on item ID then it will be a many to many relationship

(something we want to avoid at all costs in Power BI 😭)

 

The fix to this will be to create a relationship that is based on a unique item in one list to many in another.

 

For example, you could add a index column into your { ItemID, Description } table and then merge this into your { Date, ItemID, Quantity } table. You would then be able to create a one-to-many realtionship based on this index.

 

 

 


Appreciate your Kudos!

Please accept the solution if it answers your question 🙂

 

Easy Power BI Tutorials

Unleash the full potential of Power BI with Help Xel

Visit HelpXel
2.png


View solution in original post

3 REPLIES 3
Anonymous
Not applicable

So it is a bug? Good to know. And thank you for your post. It solves the issue.

No Problem at all! 

It's not a bug, just the way Power BI operates unfortunately  🙁

 


Appreciate your Kudos!

Please accept the solution if it answers your question 🙂

 

Easy Power BI Tutorials

Unleash the full potential of Power BI with Help Xel

Visit HelpXel
2.png


SidTrengove
Advocate III
Advocate III

Hey!

 

Simple but annoying answer to this one. 

 

If the relationship is based on item ID then it will be a many to many relationship

(something we want to avoid at all costs in Power BI 😭)

 

The fix to this will be to create a relationship that is based on a unique item in one list to many in another.

 

For example, you could add a index column into your { ItemID, Description } table and then merge this into your { Date, ItemID, Quantity } table. You would then be able to create a one-to-many realtionship based on this index.

 

 

 


Appreciate your Kudos!

Please accept the solution if it answers your question 🙂

 

Easy Power BI Tutorials

Unleash the full potential of Power BI with Help Xel

Visit HelpXel
2.png


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.