Thank You for posting your query onto the Forum.
This issue has occurred because in both of your tables “Data” and “Code” because both the tables has only single column in it and therefore, it’s not able to distinguish how to detect the relationship.
Secondly, the DISTINCT() function only ignores the blank values occurring from the “Dimension Table” and not from the “Fact Table”.
Now, since your “Data Table” is itself considered as a “Fact Table” in this scenario it’s not able to eliminate the blank values from it.
To solve your “Circular Dependency” issue and create a relationship as well as to ignore the blank values in your “Calculated - Dimension Table”. Here’s are steps I followed to achieve the result -
Step 1: I created the added the “Custom Column” in the using the “Conditional Column” under the QueryEditor.
Step 2: I filtered the data by ignoring the blank values.
Below is the screenshot provided for the reference -
After this load the table into your model and move towards the “Modelling View” and now you shall be able to create the relationship and blank values are also ignored.
Below are the screenshots attached for thee reference -
I’m also attaching the Excel as well as the PBIX file of my working for the reference. Also attaching the article from the SQLBI which will help you to understand the difference between the “VALUES()” and “DISTINCT()” function and also how “DISTINCT()” function performs in certain scenario.
Hoping you find this useful and meets your requirements.
Thanks & Warm Regards,
Circular Dependancy.xlsx (8.0 KB)
Distinct.pbix (28.3 KB)