Count Distinct Values by IDs of another table but it creates circular dependency

[Alizee]

Hello,

I have two tables linked by a one-to-many relationship:

Candidates with their ID as primary key and Candidate_Reference that is connected to it through this ID.

The Candidates table regroup all my candidates information and Candidate_Reference regroup infomation about their references if they have any. Therefore, the candidates in Candidates can not appear in Candidate_Reference if they do not have any references however, for those that appear as they have references, I would like to count the number of references they individually have. To do so, I was planning to DistinctCount the ID of the references for each candidate.id as each reference has a unique ID as well.

The candidate_reference table

Originally, I created a Calculated Column in the Candidate Table to compute it as I would then have a count for every unique Candidate ID in the table. I also thought best to have the result there since I would need to include it in my dashboard later with other values from the Candidate Tables.

This was the formula I used:

Reference Count = CALCULATE(DISTINCTCOUNT(candidate_reference[_id]),ALLSELECTED(candidate[id]))

Upon checking the column, I could see that the results were exactly as expected with the right count for every candidate.

The next step for me was to include it in the report after some conditional formating steps but they didn't work out and returned a Circular Dependancy Error.

I tried a few different variations, creating measures instead of calculated columns and so on and using different functions but the result was never right nonetheless.

This was the expected result in my dashboard (after another conditional formating step for the references for the word and the colors).

I am really not sure how I should do to avoid the circular dependancy and still get the right results.

I look forward to hearing from you and thank you!!

[Icey]

Hi @Alizee ,

How do you calculate "New Refs" and "KPI references" column? Could you create a sample .pbix file just with 'candidate' and 'candidate reference' table and related measures and columns for test? And please remove sensitive information.

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey

[amitchandak · Accepted solution]

@Alizee , if you trying to use measure in the column then it will give. Try the next one also as a measure

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

[Alizee]

Thank you very much,

It worked eventually by having them as measures instead of calculated columns.