Forum Discussion

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
9 years ago
Solved

Limitation on text length when creating relationship?

I have a data model where the dimension table has a key value that is text (I have verified in Excel that there are no duplicates).  However, when I try to create a relationship to the fact table usi...
Need Help
  • dedelman_clng's avatar
    dedelman_clng
    9 years ago

    Thanks for the reply.  Given that there is no documented limitation, I took a stab at the data model again, removing any leading special characters from the columns (all of the large text values started with a / ).  Once I removed the leading special character, the data model actually self-detected the correct relationship.

     

    So moral of the story - don't have your relationship index fields start with a special character (or at least a /)

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

 Hi dedelman_clng,

48 of the 218 records in my dimension table have a text key length over 100 characters.  Is there a limit as to how long a text field can be in order to be part of a relationship?

I have verified that I can create relationships between tables that have a text key length over 200 characters on my side. In addition, I haven't been aware of any official documentations/articles which mention that there is a limit as to how long a text field can be in order to be part of a relationship. So I don't think there is.:smileyhappy:

 

In this scenario, could you share a sample pbix file which can reproduce the issue? So that we can help to do some investigation on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

Regards

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Thanks for the reply.  Given that there is no documented limitation, I took a stab at the data model again, removing any leading special characters from the columns (all of the large text values started with a / ).  Once I removed the leading special character, the data model actually self-detected the correct relationship.

     

    So moral of the story - don't have your relationship index fields start with a special character (or at least a /)

    • cnpdx's avatar
      cnpdx
      Icon for Helper II rankHelper II
      8 years ago

      What is the technique to remove all non-alphanumeric characters? In my case it could be many possibilities, so I can't do a simple trim that isolates 1 or 2 characters specifically. Thanks for your help!

      • dedelman_clng's avatar
        dedelman_clng
        Icon for Community Champion rankCommunity Champion
        8 years ago

        "What is the technique to remove all non-alphanumeric characters? In my case it could be many possibilities, so I can't do a simple trim that isolates 1 or 2 characters specifically. Thanks for your help!"

         

         

        cnpdx that is generally best done in PowerQuery/M.  You should probably create a new post with that specific question and the users most familiar with advanced PowerQuery should be able to help.