Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Okay, so this isn't strictly a PowerBI problem, but rather a DAX problem that I'm trying to solve in PowerPivot./Excel, although we will be migrating the workbook to PowerBI in due course.
So here's my problem:
I have some data (90,000 rows) I'm trying to use to calculate a cumulative "fatigue score" for folk working shifts...currently using PowerPivot/Excel 2016.
As per the below screenshot, the dataset is shift data for multiple employees, that has a cumulative count of days worked vs. days off that resets back to 1 whenever they switch from one state to the other, and a 'Score' column that in my production data contains a measure of how fatigued they are.
I would like to cumulatively sum that fatigue score, and halve the cumulative sum for each line that Workday is FALSE. . My desired output is in the 'Cumulative Total' column far right, and I've used grey highlighting to show days worked vs. days off as well as put a bold border around separate Emp_ID blocks to help demonstrate the data.
This is very similar to a question I asked previously at StackOverflow, with the difference that I need the cumulative total to halve every time the Workday value is FALSE. Alejondro came up with a solution to that problem that looked like this:
Cumulative Score =
CALCULATE (
SUM ( 'Shifts'[Score] ),
FILTER (
ALLSELECTED ( Shifts ),
[Helper] = MAX ( [Helper] )
&& [EMP_ID] = MAX ( Shifts[EMP_ID] )
&& [Date] <= MAX ( Shifts[Date] )
)
)
...but I need to amend this to halve the cumulative total each time the Workday value is FALSE.
While making these kinds of adjustments to cumulative totals is very simple in Excel, I'm not sure this is possible in DAX. Currently we are in fact using PowerPivot to serve up the data in Excel, and then using Excel to create the cumulative logic, and then pulling the cumulative totals back into PowerPivot via a linked table, which works but is clunky.
Solved! Go to Solution.
Hi @jeffreyweir
@Phil_Seamark alerted me to this thread.
Yes, your Cumulative Total can be done in a DAX calculated column. (You could also consider Power Query).
Sample pbix here to illustrate the DAX.
A version of the column is:
Cumulative Total (new DAX) = VAR OuterDate = Shifts[Date] RETURN SUMX ( CALCULATETABLE ( Shifts, ALLEXCEPT ( Shifts, Shifts[Emp_ID] ), Shifts[Date] <= OuterDate ), VAR InnerDate = Shifts[Date] RETURN POWER ( 0.5, CALCULATE ( COUNTROWS ( Shifts ), ALLEXCEPT ( Shifts, Shifts[Emp_ID] ), Shifts[Date] <= OuterDate, Shifts[Date] >= InnerDate, NOT ( Shifts[Workday] ) ) ) * Shifts[Score] )
Out of interest, a version that works in "old DAX" without variables is:
Cumulative Total (old DAX) = SUMX ( CALCULATETABLE ( Shifts, ALLEXCEPT ( Shifts, Shifts[Emp_ID] ), Shifts[Date] <= EARLIER ( Shifts[Date] ) ), POWER ( 0.5, CALCULATE ( COUNTROWS ( Shifts ), ALLEXCEPT ( Shifts, Shifts[Emp_ID] ), Shifts[Date] <= EARLIER ( Shifts[Date], 2 ), Shifts[Date] >= EARLIER ( Shifts[Date] ), NOT ( Shifts[Workday] ) ) ) * Shifts[Score] )
The way I've replicated the recursive calculation is to
The reason this works is that if you imagine calculating the Cumulative Total for each row in turn, every time you hit another non-work day, each previous row gets multiplied by another factor of 0.5.
Cheers,
Owen
Yes, that's it!
Have you tested Owen's DAX-solution on that data as well? (Or better not - as it might make us cry 🙂 )
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
No reason for us to cry: all solutions (2xM, 1xDAX) have comparable performance.
Thx for the feedback @MarcelBeug, this comes as a nice surprise to me 🙂
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I performed 10 tests in Excel. Each took about 25 seconds and I caught the finishes on video.
I also performed tests in pbix, but this revealed no differences at all.
To clarify, the tricky part isn't in creating a cumulative total, but rather adjusting that cumulative total downwards by 50% for every non work day. I seriously don't think DAX can do this. But I'd love to be proven wrong.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |