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

Distinct Calculated Table with Many-to-Many error

I created a calculated table using DISTINCT(Dealer Group ID) from another table.  When I try and connect Dealer Group ID from the Distinct Table to the other Dealer Group ID in the original queried table, I keep getting a many-to-many error.  This shouldn't be many-to-many.  They are all distinct values.  Normally I fix this by removing blanks from the queried table but I cannot do this this time.

Screenshot_1.jpg

4 REPLIES 4
Cmcmahan
Resident Rockstar
Resident Rockstar

In the Manage Relationships wizard, try changing the relationship in question from many-to-many to a one-to-many relationship.

Anonymous
Not applicable

It gives me the usualy error that "The cardinality you selected isn't valid for this relationship".  

 

Additionally, when I created a rule on the original Dealer ID field saying if the Dealer ID is blank to return "None" otherwise return dealer ID. If I create the distinct table off this new column, it then works.  Seems like the null value is throwing the error which makes no sense since a "null" is a "null" is a "null".

Ahh, I see the issue.

 

The problem is that the language interpreter just sees that sometimes (it's not sure under what conditions) you use DISTINCT, and sometimes you use "None".

 

You should be able to fix this by removing that condition in your original table creation query.  Just let blanks be blanks, and DISTINCT should handle them like any other ID.

Anonymous
Not applicable

No I think you're misunderstanding.  I have an original SQL table which has dealer ID in one column called "Dealer Group".  There are ID's and then 'null' for those rows without an ID.  When I create a table withe the equation "DISTINCT('Account - Mapping & Franchise'[Dealer Group])" it creates a table with each distinct Dealer ID and then a single null value.  If I try to connect the original SQL table to the new distinct table using the two dealer ID fields, it says I can only have a many-to-many relationship.

 

I can fix the problem though if I create a new column in the original SQL table that replaces 'null' values with text "None" and then create the distinct table off of this new column with "None" instead of null. I then end up with a distinct table that has each unique ID and then a single "None" value.

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.