Forum Discussion

dmbd1904's avatar
dmbd1904
Helper III
4 years ago

Circular Dependency

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
Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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) )

     

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.