Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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.
In addition, here is a good article about understanding Circular Dependencies in Tabular and PowerPivot for your reference.
Regards
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
Hi @ChrislyBear,
Great to hear you have found a workaround! So could you accept your reply above as solution to close this thread?
Regards
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |