topic Re: Circular dependency in DAX Commands and Tips

Circular dependency

brankocareer — Mon, 25 Nov 2024 22:52:08 GMT

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

Re: Circular dependency

FreemanZ — Tue, 26 Nov 2024 02:00:55 GMT

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.

Re: Circular dependency

Poojara_D12 — Tue, 26 Nov 2024 04:56:49 GMT

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

Re: Circular dependency

brankocareer — Tue, 26 Nov 2024 16:44:32 GMT

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

Date	Pay Day
2024/04/01 0:00	2
2024/04/02 0:00	3
2024/04/03 0:00	4
2024/04/04 0:00	5
2024/04/05 0:00	6
2024/04/06 0:00	7
2024/04/07 0:00	8
2024/04/08 0:00	9
2024/04/09 0:00	10
2024/04/10 0:00	11
2024/04/11 0:00	12
2024/04/12 0:00	13
2024/04/13 0:00	14
2024/04/14 0:00	1
2024/04/15 0:00	2
2024/04/16 0:00	3
2024/04/17 0:00	4
2024/04/18 0:00	5
2024/04/19 0:00	6
2024/04/20 0:00	7
2024/04/21 0:00	8
2024/04/22 0:00	9
2024/04/23 0:00	10
2024/04/24 0:00	11
2024/04/25 0:00	12
2024/04/26 0:00	13
2024/04/27 0:00	14
2024/04/28 0:00	1
2024/04/29 0:00	2
2024/04/30 0:00	3
2024/05/01 0:00	4
2024/05/02 0:00	5
2024/05/03 0:00	6
2024/05/04 0:00	7
2024/05/05 0:00	8
2024/05/06 0:00	9
2024/05/07 0:00	10
2024/05/08 0:00	11
2024/05/09 0:00	12
2024/05/10 0:00	13
2024/05/11 0:00	14
2024/05/12 0:00	1
2024/05/13 0:00	2
2024/05/14 0:00	3
2024/05/15 0:00	4
2024/05/16 0:00	5
2024/05/17 0:00	6
2024/05/18 0:00	7
2024/05/19 0:00	8
2024/05/20 0:00	9
2024/05/21 0:00	10
2024/05/22 0:00	11
2024/05/23 0:00	12
2024/05/24 0:00	13
2024/05/25 0:00	14
2024/05/26 0:00	1
2024/05/27 0:00	2
2024/05/28 0:00	3
2024/05/29 0:00	4
2024/05/30 0:00	5
2024/05/31 0:00	6

Position ID	End Date	Daily Rate
50022758	2024/04/06 0:00	187.935
50023898	2024/04/13 0:00	185.2246875
50030379	2024/04/15 0:00	177.0290625
50024091	2024/04/15 0:00	168.9454688
50010717	2024/04/15 0:00	168.9454688
50052612	2024/04/15 0:00	168.9454688
50010636	2024/04/15 0:00	168.9454688
50023325	2024/04/15 0:00	168.9454688
50010591	2024/04/15 0:00	168.9454688
50080369	2024/04/16 0:00	168.9454688
50025553	2024/04/22 0:00	168.9454688
50091075	2024/04/25 0:00	166.66875
50091078	2024/04/25 0:00	166.66875
50091081	2024/04/25 0:00	165.22125
50091764	2024/05/07 0:00	165.1664063
50024060	2024/05/14 0:00	161.6592188
50019734	2024/05/17 0:00	161.6592188
50024009	2024/05/23 0:00	161.6592188
50025077	2024/05/30 0:00	161.6592188