cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ChrislyBear
Frequent Visitor

Circular Dependency? How? Columns don't depend on each other!

Hi,

 

I have three tables, one with article master data ("DIM Material"), one with measurments of article dimensions, i.e. article volume ("Artikelabmessungen (L&P)") and one with a calculated mean volume per article group ("Vol Warengr (L&P)"). The volume doesn't exist for each article, so we use the mean volume per article group.

 

In the master data table I have the following calculated column:

 

 

CA Vol Mat = IF(COUNTROWS(RELATEDTABLE('Artikelabmessungen (L&P)'))>0; related('Artikelabmessungen (L&P)'[M3/ME (L+P)]);related('Vol Warengr (L&P)'[Durchschn. DM3]))

Pretty straight forward: If it finds a row in the related table, it takes the value from the table "Artikelabmessungen (L&P)", and if not, it will get the mean value per group from table "Vol Warengr (L&P)".

 

Now I want to add a meta data column, to filter the data later on, that tells us which kind of volume data is being used, measured or estimated by the mean. For this I'd like to add another column:

 

CA Vol Mat Type = IF(COUNTROWS(RELATEDTABLE('Artikelabmessungen (L&P)'))>0; "Measured";"Mean estimation")

But adding this second column I get a "Circular Dependency Error" with the circular dependency being "DIM Material[CA Vol Mat Type], DIM Material[CA Vol Mat], DIM Material[CA Vol Mat Type]"

 

 

So, "CA Vol Mat Type" depends on "CA Vol Mat"??!!?! WHAT? How? These two columns are as independet as "Col1 = 'test'" and "Col2 = 1234", are they not?

Yes, they query the same table for related rows, but that's about it. The second calc col even has two static outcomes.

 

What am I missing here?

 

All the best,

Chris

1 ACCEPTED SOLUTION

Accepted Solutions
ChrislyBear
Frequent Visitor

Re: Circular Dependency? How? Columns don't depend on each other!

Hello,

 

Thanks for taking the time to read my issue and replying!

 

I'm afraid I can't post more details because of an NDA. The table structure comes from a partner company and it's very convoluted (each day my dislike for them grows...). So to reproduce the issue in a sample PBIX file would actually be a lot of work.

 

Yesterday evenening i found a workaround, that doesn't trigger a circular dependency error:

 

CA VMT = IF(
            CALCULATE(
                COUNTA('Artikelabmessungen (L&P)'[Some Column without NULLS]),
                FILTER('Artikelabmessungen (L&P)',
                       'Artikelabmessungen (L&P)'[Related Material Number]='DIM Material'[Material])
                )>0,
                "Measured",
                "Mean volume"
            )

It does the same thing I tried with COUNTROWS, just with a CALCULATE and FILTER formula.

 

Maybe there really was a circular dependency, but I don't know where. I think those guys from the partner company just winged it here and there.

 

Chris

View solution in original post

3 REPLIES 3
Microsoft v-ljerr-msft
Microsoft

Re: Circular Dependency? How? Columns don't depend on each other!

Hi @ChrislyBear,

 

Could you post your table structures with some sample data, which can help us to reproduce the issue? So that we can help further investigate on it. It's better that you can also share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. Smiley Happy

 

In addition, here is a good article about understanding Circular Dependencies in Tabular and PowerPivot for your reference. 

 

Regards

ChrislyBear
Frequent Visitor

Re: Circular Dependency? How? Columns don't depend on each other!

Hello,

 

Thanks for taking the time to read my issue and replying!

 

I'm afraid I can't post more details because of an NDA. The table structure comes from a partner company and it's very convoluted (each day my dislike for them grows...). So to reproduce the issue in a sample PBIX file would actually be a lot of work.

 

Yesterday evenening i found a workaround, that doesn't trigger a circular dependency error:

 

CA VMT = IF(
            CALCULATE(
                COUNTA('Artikelabmessungen (L&P)'[Some Column without NULLS]),
                FILTER('Artikelabmessungen (L&P)',
                       'Artikelabmessungen (L&P)'[Related Material Number]='DIM Material'[Material])
                )>0,
                "Measured",
                "Mean volume"
            )

It does the same thing I tried with COUNTROWS, just with a CALCULATE and FILTER formula.

 

Maybe there really was a circular dependency, but I don't know where. I think those guys from the partner company just winged it here and there.

 

Chris

View solution in original post

Microsoft v-ljerr-msft
Microsoft

Re: Circular Dependency? How? Columns don't depend on each other!

Hi @ChrislyBear,

 

Great to hear you have found a workaround! So could you accept your reply above as solution to close this thread? Smiley Happy

 

Regards

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors