Circular Dependency

[dmbd1904]

Hi I'm really struggling with this one. Can any one help

Standing Time = VAR Location = COR_DepthRelatedExploratoryInformation[LocationDetails]

VAR Startdate = COR_DepthRelatedExploratoryInformation[DateTimeStart]

VAR Enddate = COR_DepthRelatedExploratoryInformation[DateTimeEnd]

RETURN CALCULATE(SUM(COR_TimeRelatedRemarksLocation[Working Hour Duration]),ALL(COR_TimeRelatedRemarksLocation),COR_ProgressByTime[LocationDetails]=Location,COR_TimeRelatedRemarksLocation[DateTimeStart]>=Startdate,COR_TimeRelatedRemarksLocation[DateTimeEnd]<=Enddate)

Productive Duration = VAR TimeStart = COR_DepthRelatedExploratoryInformation[DateTimeStart]

VAR TimeEnd = COR_DepthRelatedExploratoryInformation[DateTimeEnd]

VAR Workhours =

SUMX(

    CALCULATETABLE(

        'Calendar',

        DATESBETWEEN('Calendar'[Date],TimeStart,TimeEnd),

        'Calendar'[Working Day] = 1

    ),

VAR CalStart = 'Calendar'[Date] + 'Calendar'[Start Time]

VAR CalEnd = 'Calendar'[Date] + 'Calendar'[End Time]

RETURN

MAX(MIN(CalEnd ,TimeEnd) - MAX(CalStart,TimeStart),0) * 24

)

VAR Location = COR_DepthRelatedExploratoryInformation[LocationDetails]

VAR Startdate = COR_DepthRelatedExploratoryInformation[DateTimeStart]

VAR Enddate = COR_DepthRelatedExploratoryInformation[DateTimeEnd]

VAR StandingTime = CALCULATE(SUM(COR_TimeRelatedRemarksLocation[Working Hour Duration]),ALL(COR_TimeRelatedRemarksLocation),COR_ProgressByTime[LocationDetails]=Location,COR_TimeRelatedRemarksLocation[DateTimeStart]>=Startdate,COR_TimeRelatedRemarksLocation[DateTimeEnd]<=Enddate)

VAR ProductiveTime = Workhours-StandingTime

Return ProductiveTime

[dmbd1904]

Hi @v-chenwuz-msft  unfortunately I am still getting the error as below

[v-chenwuz-msft]

Hi @dmbd1904 ,

Please share pbix file without sensitive data. it is hard to fix it without data.

Best Regards

Community Support Team _ chenwu zhu

[dmbd1904]

Hi @v-chenwuz-msft the pbix file can be retrieved from here https://we.tl/t-5GlTum5eII

[dmbd1904]

Hi @v-chenwuz-msft  just wondering if you managed to take a look. I attach another link to the file  https://we.tl/t-fl4FGJmi9G

[v-chenwuz-msft]

Hi @dmbd1904 ,

This is Circular Dependency error in DAX.

You should add allexcept() to CALCULATE() function.

You can try the following code:

Standing Time =
VAR Location = COR_DepthRelatedExploratoryInformation[LocationDetails]
VAR Startdate = COR_DepthRelatedExploratoryInformation[DateTimeStart]
VAR Enddate = COR_DepthRelatedExploratoryInformation[DateTimeEnd]
RETURN
    CALCULATE(
        SUM( COR_TimeRelatedRemarksLocation[Working Hour Duration] ),
        ALLEXCEPT( COR_TimeRelatedRemarksLocation, [primary key column] ),
        COR_ProgressByTime[LocationDetails] = Location,
        COR_TimeRelatedRemarksLocation[DateTimeStart] >= Startdate,
        COR_TimeRelatedRemarksLocation[DateTimeEnd] <= Enddate
    )

For more detail of Circular Dependency error in DAX, please refer this article.

https://www.sqlbi.com/articles/understanding-circular-dependencies/ 

Best Regards

Community Support Team _ chenwu zhu

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

[dmbd1904]

Hi @amitchandak thanks for the reply, I tried ut got the following error

[amitchandak]

@dmbd1904 , Try like. I am assuming you are creating a new column

Standing Time = VAR Location = COR_DepthRelatedExploratoryInformation[LocationDetails]
VAR Startdate = COR_DepthRelatedExploratoryInformation[DateTimeStart]
VAR Enddate = COR_DepthRelatedExploratoryInformation[DateTimeEnd]
RETURN CALCULATE(SUM(COR_TimeRelatedRemarksLocation[Working Hour Duration]),Filter(COR_TimeRelatedRemarksLocation ,COR_ProgressByTime[LocationDetails]=Location && COR_TimeRelatedRemarksLocation[DateTimeStart]>=Startdate && COR_TimeRelatedRemarksLocation[DateTimeEnd]<=Enddate) )