Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Circular dependency when creating calculated column from measures

Hi,


I have a case where I'm trying to calculate number of days between different events that should occur for most ID's (Projects).
For instance I'm calculating days between start and end of two different cases, i.e plumbing and drying.

I create three measures for each, start date, end date and a measure for number of days inbetween. 
I then add the last measure to my project dimension table to be able to find averages and etc by region etc. 
This worked fine until I added the second case, plumbing. I tried adding that measure to my dimension table, but got error for circulat dependency.

my measures look very much like this:

plumbing start = CALCULATE(MIN('Table'[originalTimeStamp]), 'Table'[tasknumber] = 50, 'Table'[tasktype] = 31)

plumbing end = 
VAR status60 = CALCULATE(MAX('Table'[originalTimeStamp]), 'Table'[tasknumber] = 60, 'Table'[tasktype] = 31)
VAR status72 = CALCULATE(MAX('Table'[originalTimeStamp]), 'Table'[tasknumber] = 72, 'Table'[tasktype] = 31)
VAR prosjavslut = MIN(Projectslist[finished])

return
    IF([plumbing start] = BLANK(), BLANK(),
    IF(status60 = BLANK() && status72 = BLANK(), prosjavslut,
    IF(status72 = BLANK(), status60,
    IF(status60 = BLANK(), status72,
    IF(status60 > status72, status72, status60)
    ))))

plumbing days = 
    IF(DATEDIFF([plumbing start], [plumbing end], DAY) < 0, BLANK(),
    IF([plumbing start] <> BLANK() && [plumbing end] = BLANK(),
        DATEDIFF([plumbing start], TODAY(), DAY),
            DATEDIFF([plumbing start], [plumbing end], DAY)
    ))

 

I'm just swithing the tasktype for drying instead of plumbing. 
I tried to recreate this problem with dummy data, but it worked then. I have more calculated column in project list, but don't think these matter. 
I've read that it is somethin about calculate and both reference the same table, but not able to figure out how to solve this problem. It seems like the end date measure is what's causing the problem.
Below is a screenshot of how tables look of those I'm using (table in my measure is in my actual file task_created_or_updated):

 

 

Any help or tips would be great! Would really like to get this to work!

2 Replies

  • Are you sure you need measures? Those events are immutable, they do not depend on user filter settings. Calculated columns should be sufficient.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support

    Hi Anonymous ,

     

    The issue may be in the filter conditions.

    Without the sample pbix file, it is difficult to know exactly where is the problem.

    DAX measure are based on row context so any given value from slicers, filters, interactions with other visualizations, variables, relationships and so on can influence your result.

    Could you share the sample pbix via cloud service like onedrive for business?

    Please mask any sensitive data before uploading.

     

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