Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
brankocareer
Helper I
Helper I

Circular dependency

Hi All,

 

I have a calculated new column:

Partial End by Day =
    VAR _EndDate = CALCULATE(MAX('FTE View'[End Date]))
    VAR _PayDaysAfterEnd =
       CALCULATE( COUNT('Date'[Pay Day]),
                'Date'[Date] >= DATE(YEAR(_EndDate),MONTH(_EndDate),1),
                'Date'[Date] <= _EndDate,
            'Date'[Pay Day] <> 1 ,
            'Date'[Pay Day] <> 7 ,
            'Date'[Pay Day] <> 8 ,
            'Date'[Pay Day] <> 14
             )
    VAR _EndSalary =  CALCULATE(SUM('FTE View'[Daily Rate]), 'FTE View'[Position Status]="Filled")
                      * _PayDaysAfterEnd
    RETURN
      _EndSalary


But, if want to add another new column to calaculte the value for ht ewhole month, just one row of DAX is removed, the circular depency error pops up. 
The new column is like:
Total End by Day =
    VAR _EndDate = CALCULATE(MAX('FTE View'[End Date]))
    VAR _PayDaysAfterEnd =
       CALCULATE( COUNT('Date'[Pay Day]),
                'Date'[Date] >= DATE(YEAR(_EndDate),MONTH(_EndDate),1),
            'Date'[Pay Day] <> 1 ,
            'Date'[Pay Day] <> 7 ,
            'Date'[Pay Day] <> 8 ,
            'Date'[Pay Day] <> 14
             )
    VAR _EndSalary =  CALCULATE(SUM('FTE View'[Daily Rate]), 'FTE View'[Position Status]="Filled")
                      * _PayDaysAfterEnd
    RETURN
      _EndSalary
I really don't know how to adjust my DAX to avoid the circular depency issue, please help me out. If you need more information, please let me know.

 
Thank you in advance!!!
Branko
3 REPLIES 3
Poojara_D12
Super User
Super User

Hi @brankocareer 

As per my understanding, Removing a filter or condition changes the dependency chain, causing a loop when creating calculated columns. Convert both Partial End by Day and Total End by Day into measures.

Example of Partial End by Day 

Partial End by Day Measure =
VAR _EndDate = MAX('FTE View'[End Date])
VAR _PayDaysAfterEnd =
    CALCULATE(
        COUNT('Date'[Pay Day]),
        'Date'[Date] >= DATE(YEAR(_EndDate), MONTH(_EndDate), 1),
        'Date'[Date] <= _EndDate,
        'Date'[Pay Day] <> 1,
        'Date'[Pay Day] <> 7,
        'Date'[Pay Day] <> 8,
        'Date'[Pay Day] <> 14
    )
VAR _EndSalary =
    CALCULATE(SUM('FTE View'[Daily Rate]), 'FTE View'[Position Status] = "Filled") * _PayDaysAfterEnd
RETURN
    _EndSalary

 

Example of Total End by Day 

 

 

Total End by Day Measure =
VAR _EndDate = MAX('FTE View'[End Date])
VAR _PayDaysAfterEnd =
    CALCULATE(
        COUNT('Date'[Pay Day]),
        'Date'[Date] >= DATE(YEAR(_EndDate), MONTH(_EndDate), 1),
        'Date'[Pay Day] <> 1,
        'Date'[Pay Day] <> 7,
        'Date'[Pay Day] <> 8,
        'Date'[Pay Day] <> 14
    )
VAR _EndSalary =
    CALCULATE(SUM('FTE View'[Daily Rate]), 'FTE View'[Position Status] = "Filled") * _PayDaysAfterEnd
RETURN
    _EndSalary

 Use these measures in visuals or further calculations.

By switching to measures, dependencies are recalculated dynamically, avoiding circular references.

 

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
FreemanZ
Super User
Super User

Hi @brankocareer ,

 

 

using CALCULATE in calculated column and without proper handling on context transition can be very tricky. 

in your case, Partial End by Day and Total End by Day are depending on each other. 

 

To solve this issue, try:

1) add index column in your 'FTE View' table, rule of thumb!

2) or provide a sample of the 'FTE View' table, with expected result. Expressions like CALCUALTE([], ALLEXCEPT()) may help do the trick. 

 

@FreemanZ  Hi FreemanZ, thank you for your quick reply!  Following I provide 2 tables. Could you please help?
Thank you!
Branko

 

DatePay Day
2024/04/01 0:002
2024/04/02 0:003
2024/04/03 0:004
2024/04/04 0:005
2024/04/05 0:006
2024/04/06 0:007
2024/04/07 0:008
2024/04/08 0:009
2024/04/09 0:0010
2024/04/10 0:0011
2024/04/11 0:0012
2024/04/12 0:0013
2024/04/13 0:0014
2024/04/14 0:001
2024/04/15 0:002
2024/04/16 0:003
2024/04/17 0:004
2024/04/18 0:005
2024/04/19 0:006
2024/04/20 0:007
2024/04/21 0:008
2024/04/22 0:009
2024/04/23 0:0010
2024/04/24 0:0011
2024/04/25 0:0012
2024/04/26 0:0013
2024/04/27 0:0014
2024/04/28 0:001
2024/04/29 0:002
2024/04/30 0:003
2024/05/01 0:004
2024/05/02 0:005
2024/05/03 0:006
2024/05/04 0:007
2024/05/05 0:008
2024/05/06 0:009
2024/05/07 0:0010
2024/05/08 0:0011
2024/05/09 0:0012
2024/05/10 0:0013
2024/05/11 0:0014
2024/05/12 0:001
2024/05/13 0:002
2024/05/14 0:003
2024/05/15 0:004
2024/05/16 0:005
2024/05/17 0:006
2024/05/18 0:007
2024/05/19 0:008
2024/05/20 0:009
2024/05/21 0:0010
2024/05/22 0:0011
2024/05/23 0:0012
2024/05/24 0:0013
2024/05/25 0:0014
2024/05/26 0:001
2024/05/27 0:002
2024/05/28 0:003
2024/05/29 0:004
2024/05/30 0:005
2024/05/31 0:006

 

Position IDEnd DateDaily Rate
500227582024/04/06 0:00187.935
500238982024/04/13 0:00185.2246875
500303792024/04/15 0:00177.0290625
500240912024/04/15 0:00168.9454688
500107172024/04/15 0:00168.9454688
500526122024/04/15 0:00168.9454688
500106362024/04/15 0:00168.9454688
500233252024/04/15 0:00168.9454688
500105912024/04/15 0:00168.9454688
500803692024/04/16 0:00168.9454688
500255532024/04/22 0:00168.9454688
500910752024/04/25 0:00166.66875
500910782024/04/25 0:00166.66875
500910812024/04/25 0:00165.22125
500917642024/05/07 0:00165.1664063
500240602024/05/14 0:00161.6592188
500197342024/05/17 0:00161.6592188
500240092024/05/23 0:00161.6592188
500250772024/05/30 0:00161.6592188

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.