Distinct Calculated Table with Many-to-Many error

[Anonymous]

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.

[Cmcmahan]

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

[Anonymous]

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

[Cmcmahan]

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]

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.