Reliationship Issue A Circular Dependacy

Hi DNA Team,

While working on my data model I run into an issue while building a relationship between data and unique values:
image
From my sales table, I created a unique list of charge codes (it contains also blank), but I can’t create reliationship and I do not understand why.

Distinct.pbix (24.4 KB)

The attached file is just an example.

In here I can’t create one to many relationships, between table and data.

To create “Table” I just use Values(Data"Code")

I thought when I used Distinct it will exclude any blank values but I was wrong.

image

Hello @Matty,

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 -

Circular Dependency - 2

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. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

Circular Dependancy.xlsx (8.0 KB)

Distinct.pbix (28.3 KB)

1 Like

@Harsh

In my data file where I have multiple Code i do have many other columns, when I create a Dim Table using Ditstinct I am only to be able to create reliationship many to many, not one to many as I would like to.

This is what I don’t understand.

Hello @Matty,

The reason it’s allowing you to create “Many-to-Many” and not “One-to-Many” is because there are multiple blanks in your “Data Table” and then when you start creating another table using “DISTINCT()” it’s not able to recognize the relationship between the blanks so it’ll start presuming that “each row of blank from the data table = each row of blank from the DISTINCT table”. So it starts categorizing each blank row as unique value

Now, there’re records against the blank values in your data table then you need to structure your data in such a manner and assign the Codes to them also. Because right now in this scenario this is no other alternative to create “One-to-Many” relationship either by using VALUES() or DISTINCT() function. Or create a table using DISTINCT() which doesn’t contain any blank values in a particular column.

Thanks & Warm Regards,
Harsh

What if you try the following to create your Dimension table:
image

image

Hello @Matty,

I’m providing the link below of a video created by Patrick from Guy in a Cube channel. Although the video is created based on “Blank Value in a Slicer” but it’s very much applicable in our case because it violates the referential integrity and that’s what he has explained and how to fix that issue.

Because if you observe this video carefully, he had blanks in Customer Name column and not in the ID’s column so still it was possible for him to create the relationship but in your case it’s completely vice - versa. You’re having blanks in your ID’s column and against those blanks you’ve individual records.

Hoping this video helps you to understand better about the concepts of “VALUES()” and “DISTINCT()” function.

Thanks & Warm Regards,
Harsh

Thank you @Harsh & @Nick_M for your help.

I actually seen that clip (cuz of it I actually purchase online course for DAX studio as I never knew it about it)

Harsh,

You were right

" each row of blank from the data table = each row of blank from the DISTINCT table ”. So it starts categorizing each blank row as unique value"

I edited my data simply if row = null then Blank otherwise code :slight_smile:

Now when I Create a DIm table using distinct then I am able to create a relationship :slight_smile:

Hello @Matty,

I’m glad that the statement helped you in understanding it better and you’re able to modify your Dimension table accordingly and was able to achieve the desired result.

Thanks & Warm Regards,
Harsh

1 Like