Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
I have a calculated new column:
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
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
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 |
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |