Table visual anomaly and circular dependency error

[andrea_chiappo]

This is a replica to a previous question which was unsolved

I want to calculate (correctly) for every user in my model the number of:   

ideas proposed, projects being involved in and challenges involved in  

The tables I'm using are the following:  

DimUsers[userID] <--> FactIdeas[userID]

DimUsers[userID] <--> FactProjects[userID]

DimUsers[userID] <--> FactChallenges[userID]  

(inactive relationships)

I thought of adding in DimUsers a column for each quantity I want to calculate .  

Problem (of course) is that circular dependecy prevents this - despite following 

the indications given in 

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

For instance, the first calculated with  

UsersIdeasCol = CALCULATE(

    IF(

        DISTINCTCOUNT(FactIdeas[ideaID])=0,

        0,

        DISTINCTCOUNT(FactIdeas[ideaID])

    ),

    FILTER(ALLNOBLANKROW(FactIdeas[factIdeasTypeID]),

           FactIdeas[factIdeasTypeID]=2),

    USERELATIONSHIP(DimUsers[userID], FactIdeas[userID])

)

correctly calculates the number of ideas created by each user,  

given by the filter condition on factIdeasTypeID=2  

(Note the use of ALLNOBLANKROW as suggested in the sqlbi website!)  

Trying to calculate the number of projects, using the slightly different formula  

UsersProjectsCol = CALCULATE(

    IF(

        DISTINCTCOUNT(FactProjects[projectID])=0,

        0,

        DISTINCTCOUNT(FactProjects[projectID])

    ),

    FILTER(ALLNOBLANKROW(FactProjects[factProjectsTypeID]), 

           FactProjects[factProjectsTypeID]=2),

    USERELATIONSHIP(DimUsers[userID], FactProjects[userID])

)

gives me the error:  

A circular dependency was detected: DimUsers[UsersProjectsCol], DimUsers[UsersIdeasCol], DimUsers[UsersProjectsCol].  

I tried two different workarounds  

1) following the suggestion given in many similar answers,  

I thought of calculating Measures instead of a columns.  

However, here's what happens when I display the two different  

objects in the Table visual showing DimUsers[name] and DimUsers[role],  

(having a slicer on the customer, which controls the users to display in the table)  

using the data from the calculated column  

  

(9 users and 9 distinct ideas) 

and when I display also the data from the measure  

  

Dispite showing the correct numbers of ideas, the latter clearly does not mimic 

the former but instead displays all user in DimUsers, so even the ones from 

other customers.  In other words, the effect of the slicer is lost.  

Why does this happen and how can I remedy this?  

2) I thought of "fooling" DAX by duplicating the DimUsers table and adding the  

additional calculated column in its replicas. This felt like overkill but I need a solution.  

I duplicated DimUsers with  

DimUsers2 = ADDCOLUMNS(DimUsers, "Dummy", BLANK())  

and added a column to calculate the number of projects for user, with  

UsersProjectsCol = ... (last formula above) 

This time no circular dependecy error but adding the object to the Table visual above,  

it displays for every user all distinct projects present in my FactProjects table.  

Can anybody help me solving this by either helping me understand the anomalous behaviour  

of the table visual or help me remove the cicular dependecy error? Many thanks

[v-alq-msft]

Hi, @andrea_chiappo 

Could you share some sample data and expected results with OneDrive for Business? Do mask sensitive data before uploading,

By using 'Calculate', we are asking to transform the current row content into a filter context and this modifies the dependency list. For further information, you may refer the document .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @andrea_chiappo ,

Just believe that the relationship between the table is one to many correct?

How are you transposing the column to the measure are you making the exact formula?

Be aware that measures are based on context so depending on the parameters, filters, columns, and so on that you add to your measure an to your visualization the result may be incorrect within the context, believe that's what is happening on the second image where you have the total correct but the lines don't have any values.

Looking at whar you are posting don't believe it's necessary yo have complex calculations on the information to return the values you need.

Can you share some sample data or and PBIX mockup with expected result? If informantion is sensitive can you share it trough private message?

[andrea_chiappo]

Thanks for your answer Felix, which was also very teling. Howerver the hint to the SQBLI page was eye-opening.  

Specifically, using ALLEXCEPT as a first filter in CALCULATE did the magic